Unique Files in SSIS

A common issue I’ve had work with in ssis is to get a file name that represents distinct name on when it was run. For example suppose you have a flat file that has data dumped to it every hour, if it’s static then it will get overwritten each time it’s run. If there was an issue three hours ago you can’t review since it’s been overwritten. Using a time stamp allows you to make this distiquishment. For example your flat file might look something like this. C:\testing\out10-20-200790000am.txt To produce this follow these steps.


Step 1 create a variable called FlatDir. Seed the FlatDir with a directory location. Later make this variable a configuration variable.

Step 2 Make the flat file variable value set by expression. What’s going on here is that it’s set using the start time of the ssis package, which I’ll explain below.

Notice when it’s evaluated it now has a datestamp associated with it. Above I mentioned using the starttime. The reason you do this over the current time is that the current time gets evaluated when the connection is needed. For example if you need to dump data then pick it up on another task it will be evaluated 2x. Using the current time would be this expression would be different for each task. For example in the above example the first variable would be c:\testing\FlatFile-10-15-2008112322AM.txt but might evaluate to c:\testing\FlatFile-10-15-2008112323AM.txt on the next task requiring the variable.

Another point you might be wondering is why put this in a variable expression why not the connection expression. This is for flexibility. Lets say you export the data and then need to validate the size of the file, you can easily work off the expression, such as in a scripting task you could do something like this.

Public Sub Main()
If Dts.Variables("FlatFile").Value.ToString().Length > 0 Then
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failur
End If
End Sub