ssis flat file creation date

Question

I have a very simple ssis task, currently it is just a flat file connection and a bulk insert task to update a table. I cannot figure out how to ensure the flat file was created today before continuing with the bulk insert.

Try this solution https://stackoverflow.com/questions/27161004/can-ssis-pick-up-the-file-with-the-latest-date-creation — gofr1, Nov 04 '20 at 10:51

EJoshuaS - Stand with Ukraine · Accepted Answer · 2020-11-05T13:37:48.850

0

In your Data Flow block, add a Script component, like this:

Add two variables, one for your file name and one for the result of the script:

Next, configure your script as follows:

Now, you can add the following expression to the arrow between the script component and your data flow block:

Your code in the script itself would be:

public void Main()
    {
        string fileName = Dts.Variables["FileName"].Value.ToString();
        FileInfo fi = new FileInfo(fileName);

        Dts.Variables["FileWasCreatedToday"].Value = fi.CreationTime >= DateTime.Now.Date;

        // Another option would be to set this to fail if it's not created today. This will fail the whole package.
        Dts.TaskResult = (int)ScriptResults.Success;
    }

Edit: If you want it to send you an email in the case of a failure, just create a second link from the Script Task to your Send Mail Task with the following expression: !@[User::FileWasCreatedToday]. Like below:

edited Nov 05 '20 at 13:37

answered Nov 04 '20 at 16:22

EJoshuaS - Stand with Ukraine

11,977
56
49
78

I have tried this solution but I notice if I set the task to fail it does not move on to the next step, which is a send email task to warn me of the failure. – user2369812 Nov 05 '20 at 07:29
@user2369812 You don't need to set it to fail - just conditionally either move to the data flow task or the task to email you based on the `FileWasCreatedToday` variable. (See my update). – EJoshuaS - Stand with Ukraine Nov 05 '20 at 13:33
@user2369812 Glad I could help. – EJoshuaS - Stand with Ukraine Nov 05 '20 at 15:23

ssis flat file creation date

1 Answers1