Search code examples
sql-serverssissql-server-2016flat-file

How can I delete in SSIS a FlatFile in ControlFlow when it has dynamic name?

I use SQL Server 2016 and I have a SSIS package in which I have created a FlatFile with a dynamic Name in one DataFlow in Loop.

For creating the name I have used this pathern : "SomeText" + DateTime + "SomeText"

enter image description here

I have created a varibale like below Image :

enter image description here

This is Exprission in my variable:

enter image description here

Then I have assigned this variable to connectionString properties of my FlatFile Destination like this :

enter image description here

In some case in DataFlow I don't have the data and I create an empty file but I need to delete this file when it is empty. Because of that I have created a Variable and I have used RowCount Component for populate this variable.

enter image description here

After that in Control Flow, I have put a File System Task for deleting the file if my variable is 0. (My loop is based on a object)

enter image description here

But I have an issue, In DataFlow, When I want to delete the file, the name of FlatFile will be changed and I can't find the same file that has been created in DataFlow.

The problem is that value of variable will be change in second time when I want use the variable.

How can I delete a Flat File in Control Flow which has been created dynamically in DataFlow?

Solution

  • You need to use a script task to set your variable value, not an expression builder.

    Since you are using a date/time in the expression builder, it is constantly changing. Using a script task will allow you to capture/store the date/time value as the loop iterates, and store that value persistently throughout the loop.

    This script task would typically be at the top (first) of your loop, and it would set/change the filename on every iteration.

    Here are some examples of how to do this: https://microsoft-ssis.blogspot.ca/2010/12/how-to-use-variables-in-script-task.html

    If you are getting values within the dataflow which are used in the filename, then you may need to use a script component (transformation) to update the filename within the dataflow.

    Here are some examples of how to do this: https://microsoft-ssis.blogspot.ca/2011/01/how-to-use-variables-in-script.html