Dynamically Name Text Files in SQL Server Integration Services

By: Rick Dobson   |   Comments (2)   |   Related: More > Integration Services Development

Problem

Exporting data as text files is a standard way to make SQL Server data available to other applications. SSIS is a rich tool for supporting this process. However, do you also need to empower C# developers in your organization to be able to modify a standard SSIS solution for their custom text file requirements or do you just want to explore the interoperability of C# with SSIS? This tip presents a solution that can get you started.

Solution

MSSQLTips has many great tips on how to put SSIS to use. A good place to get started is with the MSSQLTips SSIS tutorial that includes a menu of tips covering a wide array of SSIS functionality. However, no prior MSSQLTips article illustrates how to jointly use SSIS and C#. This tip addresses this specific issue.

The tip starts with a very brief overview of a solution on how to use SSIS and C# for dynamically naming a text file with data defined by a T-SQL query. C# is specifically used for time stamping the text file name. The overview is presented merely for providing a context for getting you started on how to use of C# with SSIS. Please refer to the MSSQLTips SSIS tutorial for more general coverage on creating solutions with SSIS. Also, take a look at the tip titled Dynamic Flat File Connections in SQL Server Integration Services for another approach on how to time stamp text file names without C#.

Overview of C# Text File Naming Solution

Theses are the steps for the solution.

• Start an SSIS project with BIDS.
• Add a Data Flow Task item to the Control Flow tab; double-click the Data Flow Task.
• On the Data Flow tab, add an OLE DB Source. The test solution for this tip used the SQL Server 2008 R2 edition of the AdventureWorks database, a SQL Command Data Access mode, and a T-SQL query statement to define the data exported to the text file.
• Add a variable at the package level; this variable will eventually be written to by C# with the time stamped text file name.
• Add a Flat File Connection Manager.
• Configure the Flat File Connection Manager ConnectionString property to point at the package level variable.
• Add a Flat File Destination to the Data Flow tab and connect the OLE DB Source to it.
• Add a Script Task item to the Control Flow tab. Configure the package level variable for the Script Task as ReadWriteVariables.
• Click the Edit Script button to open an automatically generated C# template. Add your custom C# code to the template.
• Update the C# template with a .NET namespace reference omitted by the code generator that creates the template.
• Close the C# template, close the Script Task, and execute the package to verify that the time stamped text file is generated.

C# Code for Time Stamping a File Name

After you initially click the Edit Script button in the preceding solution overview, the bottom of the C# template will appear as in the following screen shot. You can insert your custom code between the comment line (it begins with //) and the line that returns an integer value indicating the success of the script.

The next screen shot presents a generalized version of the C# code for generating a time stamped file name and returning the name to SSIS as the value for a package variable. You'll need to update your computer with the file path in the C# script or change the script so that it points to a path on your computer. You may also care to revise the string value assigned to MyFileName. Each C# statement ends with a semicolon (;).

The first two statements define the path name and the file name for the text file. The "string" keyword declares the reference type, namely a string. After the type instance name and an equal sign, is a string value or string expression designating a value for the type instance. Notice that the MyFilePath string instance uses a plus operator to concatenate two string values.

The "DateTime" keyword after the file name declaration and assignment assigns the current date and time to a datetime instance named Now_dt. The subsequent five statements after the datetime assignment extract the month, year, day, hour, and minute from the current date and time value and assign a string representation to a corresponding string reference instance.

• The ToString method transforms other reference type instances to a string instance, and the "MMM" argument represents a month name as a three character string value. The ToString method does not have such conveniently available arguments for representing the other date and time parts of a datetime value.
• The code in the screen shot extracts the year value with a combination of the Year datetime method and the ToString method for the Convert class.
• The Day method for a datetime instance returns either 1 or 2 digits representing the day of the month. The expression for the string value of a day number (Now_dd_st) prefixes the day number with a leading zero if the day number is less than 10, but otherwise the expression does not assign a leading zero. The C# conditional operator (?) takes three arguments. One for a Boolean condition, a second for when the Boolean condition is true, and a third for when the Boolean condition is false.
• The expressions for extracting the hours and minutes from the current date and time follow the same approach used for the day number value. The Hour method extracts the hour number as 1 or 2 digits, and the Minute method returns the number of minutes past an hour as 1 or 2 digits.

After the assigning values to the string instances for the file path and name as well as the month, day, year, hour, and minute parts of the current date and time, the string value for the DynamicFilePathName instance is assigned. The expression for assigning the DynamicFilePathName value is merely a concatenation of the individual string values computed with a succession of plus operators. For example, if the current date and time were 3:09 PM on October 21, 2011, the value for DynamicFilePathName would be "C:\RootPathName\Child1PathName\Child2PathName\BaseFileName_Oct212011_1509".

The final line in the script assigns DynamicFilePathName to the PackageVariableName member of the Variables property of the Dts class. The SSIS Script Task uses the Variables property of the DTS class to read and write to Variable objects in a SSIS package. SSIS will use the variable to write a text file with a an explicit FILE extension. Other applications can read the file with any text file reader. For example, you can examine the file with Notepad.

A Fix for an Omitted .NET Namespace

When SSIS initially creates the C# template, the code generator for the template adds references to four .NET namespaces. The "using" keyword followed by the name of a namespace establishes the reference. The following screen shot shows the original four namespace references plus one additional namespace reference that you need to add manually.

The omitted namespace is the Microsoft.SqlServer.Dts.Tasks.ScriptTask namespace. This namespace contains classes that support custom scripting, such as the C# code in the preceding screen shot. If you do not manually add the namespace reference, the solution will appear to run to a normal conclusion, but the C# code will not update the value of the package level variable. Therefore, the solution will run and save the file with whatever static value you assign to the package level variable when adding it to the SSIS package. Since the whole purpose of the C# code is to assign a value dynamically for the text file, failing to add the omitted namespace essentially causes the solution to fail although it does not return an error.

Next Steps

• This tip introduces you to the basics of using C# code with SSIS for one specific task, and it covers an omitted namespace reference for the automatically generated C# template created when you initially click the Edit Script button for a SSIS Script Task item. However, the Microsoft.SqlServer.Dts.Tasks.ScriptTask namespace enables more functionality than covered in the sample solution described in this tip. In addition, the Microsoft.SqlServer.Dts.Runtime offers numerous classes for interacting with SSIS solutions from a C# code template.
• Download the solution I used for this tip.
• If you are just getting started with SSIS or want to refresh your SSIS skills, do not forget to read some of the excellent MSSQLTips articles on SSIS.
• Here are some additional resources to review:
  • MSSQLTips SSIS tutorial.
  • MSSQLTips article on dynamic flat file connections without C#.
  • The link to the MSDN site on the Microsoft.SqlServer.Dts.Tasks.ScriptTask namespace.
  • The link to the MSDN site on the Microsoft.SqlServer.Dts.Runtime namespace.

About the author

Rick Dobson is an author and an individual trader. He is also a SQL Server professional with decades of T-SQL experience that includes authoring books, running a national seminar practice, working for businesses on finance and healthcare development projects, and serving as a regular contributor to MSSQLTips.com. He has been growing his Python skills for more than the past half decade -- especially for data visualization and ETL tasks with JSON and CSV files. His most recent professional passions include financial time series data and analyses, AI models, and statistics. He believes the proper application of these skills can help traders and investors to make more profitable decisions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips