Andy Answers: Format a Flat File Name

My Twitter DMs are open @AndyLeonard. Ask me a question about SSIS, ADF, or Biml. I may answer it with a blog post, a video, or both.

Today’s Andy Answer is an answer to an SSIS question received via @AndyLeonard direct message:

Using SSDT, I have 3 variables: one for the file path, one for date string, and one for file name. I’m trying to create the flat file destination with a variable so the date will be correct each day the file is generated. I have the right expressions for each variable, however, when the file is created it only shows the file name variable value. I configured the flat file connection manager’s ConnectionString property with an expression that concatenates these three variables. Can you help?

[edited]

Yes. Yes I can help. I Am Here To Help.™

An Example to Demonstrate

Create a demo project:

Rename the SSIS package, if you’d like:

Add new SSIS Variables to the SSIS Package. 

I added four variables to start:

• FlatFilePath [String] – defaulted to a path on my server.
• FlatFileName [String] – a hard-coded base name for my file.
• FlatFileExtension [String] – to hold my file extension (csv in this case).
• FlatFileDateString [String] – the date formatted as I desire:

Formatting the FlatFileDateString SSIS variable is tricksy. I used the following SSIS Expression:

(DT_WSTR,4)YEAR(@[System::StartTime]) + (DT_WSTR,2)((MONTH(@[System::StartTime]) < 10) ? “0” + (DT_WSTR,2)MONTH(@[System::StartTime]) : (DT_WSTR,2)MONTH(@[System::StartTime])) + (DT_WSTR,2)((DAY(@[System::StartTime]) < 10) ? “0” + (DT_WSTR,2)DAY(@[System::StartTime]) : (DT_WSTR,2)DAY(@[System::StartTime]))

Add a new SSIS Variable to contain the full path to the flat file. My variable is named FlatFileFullPath and the SSIS Expression used to build it is show here:

My SSIS Variables, configured:

Note: the initial value for the FlatFileFullPath Expression is:

@[User::FlatFilePath] + “\” + @[User::FlatFileName] + “_” + @[User::FlatFileDateString] + “.” + @[User::FlatFileExtension]

Add a Data Flow Task. In the Data Flow Task, add an OLE DB Source adapter. Configure the source adapter to read some data from some sample database:

Create a test sample file in the directory you specified in the FlatFilePath SSIS Variable Value:

Populate the sample file with column names and one sample data row. Enclose the column names and data values in double-quotes:

Create a Flat File Connection Manager. Configure the Text Qualifier as double-quotes and make sure the “Column names in the first data row” checkbox is checked:

Verify Flat File Connection Manager configuration by viewing the Columns page:

Add a Flat File Destination adapter to the Data Flow Task and configure it to use the Flat File Connection Manager. Ensure the “Overwrite data in the file” checkbox is checked to make the SSIS package re-executable (within the same day, at least):

Select the Flat File Connection Manager and view its Properties. Click the ellipsis for the Expressions property to open the Property Expressions Editor for the Flat File Connection Manager. From the Property dropdown, select ConnectionString:

Drag the FlatFileFullPath variable to the Expression textbox. Click the Evaluate Expression button to examine and verify the value of the FlatFileFullPath SSIS variable: 

When you close the Expression Builder, the Property Expressions Editor should appear similar to:

Once the ConnectionString Expression override is configured, set the DelayValidation property for the Flat File Connection Manager to True. This setting configures the connection manager to skip validation, which is helpful if the file does not (yet) exist:

A test execution in the SSIS debugger proves we’ve accomplished out goal:

One Change

I recommend (at least) one change to this package: Move the FlatFilePath SSIS Variable to a Package Parameter. Why? One may desire to change the target directory of the output file at some point and a parameter value is easy to override at runtime:

After converting the SSIS variable to a package parameter, update the Expression for the FlatFileFullPath SSIS variable to reflect the change:

The updated Expression is:

@[$Package::FlatFilePath] + “\” + @[User::FlatFileName] + “_” + @[User::FlatFileDateString] + “.” + @[User::FlatFileExtension]

A second test run lets me know we’re all good.

Conclusion

This is one answer for this question posed to me on Twitter.

Do you have an SSIS question? Ask! Andy Answers.

:{>