In one of our previous posts, we provided instructions on how to use SSIS to export data to flat files. Often times, our business users would like to have a running history of exported data. For example, if we had an export that output all orders for the prior day our users wouldn’t want to see a file named “Orders Placed Yesterday”. Data consumers would prefer to see a file named “Orders Placed 6-30-2015”. To append the date to flat file exports in SSIS is a relatively common and simple task.
Setting Up the File Name Variable
To start, open up the SSIS solution named “Credit Union SSIS Solution”. Right click on the “Export to Flat File” package in the “Solution Explorer” and select “Copy”. Then right-click on the “SSIS Packages” folder and click “Paste” to create a copy of the package. Rename this package “Export with Date Append”.
Make sure that you have a “Variables” panel on the left side of your screen. If you cannot find this panel, click on the “View” option from the main menu, hover over “Other Windows” and choose “Variables”.
Our plan is to use variables to store the fully qualified file name with the appropriate date value. Then, we will use this variable value as the connection string for our flat file connection.
To start, click the “Add Variable” button in the top left corner of the “Variables” panel.
A new row will appear in this “Variables” panel. Name the variable “FilePath” and then set the “Data type” column value to “String”.
With the variable’s row highlighted, navigate to the “Properties” panel which should be on the right side of your screen. Find the property named “EvaluateAsExpression”; set this value to “True”. Directly beneath this property is another property named “Expression”. If you click in the text box to the right of the “Expression” property, an ellipsis button will appear to the right; click this button.
The “Expression Builder” window will appear on your screen. Here is where we will define how this variable will be evaluated. I won’t dive too deep into how to create different SSIS expressions, but if you click here you will be redirected to SSIS expression documentation from the Microsoft Developer Network that provides a wealth of knowledge on the subject.
In the “Expression” text box, type in the following expression (including all quotation marks as they appear and of course modifying for your specific location and folder names):
“\\\\localhost\\c$\\Users\\Brewster\\Desktop\\AccountTypes as of ” + (DT_WSTR, 2)MONTH(GETDATE()) + “-” + (DT_WSTR, 2)DAY(GETDATE()) + “-“+ (DT_WSTR, 4)YEAR(GETDATE()) + “.csv”
Once you have typed or copied the above expression, click the “Evaluate Expression” button below the text box. The “Evaluated value” read-only text box will display how the expression evaluates. In this case, it contains the fully qualified file path containing the current date appended to the file name and the appropriate “.csv” extension.
Press “OK” once this has been completed.
Adjusting the Connecting String
Next, we need to adjust the connection setting for the flat file export. To do this, highlight the “AccountTypes.csv” connection from the “Connection Managers” panel towards the bottom of your screen:
Navigate to the “Properties” panel, and find the “Expressions” property. Click the ellipsis button to the right of the property. The “Property Expressions Editor” window will appear. In the first row, choose “ConnectionString” from the “Property” column dropdown. In the “Expression” column either type “@[User::FilePath]” or click the ellipsis and choose the variable name from the “Variables” folder in the resulting window that appears. This specifies that we want to send our data to the location specified by the “FilePath” variable.
Press “OK” once this has been completed.
Guess what? That’s it! That is all it takes to append the date to the file name for a flat file export using SSIS. To test the package, click the green triangle (play button) on the toolbar underneath the main menu. Your package should like the screen below if everything executed successfully:
With the package successfully completed, navigate to the location of the export and check to be sure that the file was exported properly. You can see in the image below that I have a file with the date appended to the location I expected to see the file:
What might some like an otherwise challenging task can be completed rather simply using SSIS. Understanding what types of expressions can be created and how they can be employed throughout your SSIS packages is a valuable skill. This simple example hopefully provides you with some insight on other ways you can utilize SSIS in your organization!
