Dynamic name of text file generated by Script Task in SSIS 2008

Today, I came up with one of the most useful script used by ETL developer. I used to see many questions about this issue so many times in different forums too.

We are going to generate text file by calling one stored procedure.  I have already written an article which generate text file as an output of Stored Procedure.  But It was using Flat File Destination task to generate text file and it was having static file name.

Here in this article we are going to use power of variables in SSIS. We will use the query and dynamic file name stored in variables. As a pre-requisite, let us create one table and stored procedure.

CREATE TABLE StudDetail

(

StudentID INT,

PassYear VARCHAR(10),

Grades int,

Increase INT,

Decrease INT

)

GO

INSERT INTO StudDetail

SELECT 1, '08-09', 3333,0,0 union all

SELECT 1, '09-10', 4252,25,0 union all

SELECT 2, '08-09', 2100,0,0 union all

SELECT 2, '09-10', 2002,0,-10

GO

create proc usp_StudDetail 

as 

begin 

 select StudentID,PassYear,Grades from StudDetail 

end 

GO

After creating this table and SP, let us create one new project in BIDS for SSIS.

1.)    Add one variable in variable window named “ExecuteSP”, datatype “String”

2.)    Add another variable named “DynamicFileName”, datatype “String”. We want dynamic value for this variable so in property window, set “True” in “EvaluateAsExpression” property

3.)    Click on ellipse button in “Expression” property to set the expression which make dynamic file name. Set following string in “Expression” which will generate dynamic file name

"DynamicFileName_" + (DT_WSTR,2) Day(GETDATE()) +(DT_WSTR,2) Month(GETDATE()) +(DT_WSTR,4) Year(GETDATE()) +(DT_WSTR,4) DatePart("mi",GETDATE())+(DT_WSTR,4) DatePart("second",GETDATE())+".txt"

To get clear idea about all these settings, look at the screen shot below.

 

Once you set up variables, now this is turn to set ado.net database connection.

In the connection manager at the bottom of the screen, right click and insert “New ado.net connection” and rename it to “AdoNetConn” from the property window. Look at the below screen shot.

Now, you are going to do real programming like you used to do in C# application, which is my favorite programming language. I am glad that Microsoft has given facility to write script in C# language in Microsoft Integration services 2008.

Anyway, add one script manager in your design area and double click on it to configure.  Select both variables which we have created in “ReadOnlyVariables” property and click on “Edit Script” button.  See below image for more information.

 

Now, write down following script in your MAIN method.

//storing dynamic file name in strFileName varialbe

            string strFileName = Dts.Variables["DynamicFileName"].Value.ToString();

            //storing our TSQL in strSQL variable

            string strSQL = Dts.Variables["ExecuteSP"].Value.ToString();

            //storing the directory in strDir where our dynamic text file will be generated.

            string strDir = "D:\\SSIS\\";

            //getting connection string in strConn from the ado.net connection manager we have added in design time

            string strConn = this.Dts.Connections["AdoNetConn"].ConnectionString.ToString();

           

            string strLine = "";

            System.Data.SqlClient.SqlConnection sqlConn = new System.Data.SqlClient.SqlConnection(strConn);

            sqlConn.Open();

            System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand(strSQL, sqlConn);

            System.Data.SqlClient.SqlDataReader dr;

            dr = sqlCmd.ExecuteReader();

            //getting column header and writing it to file

            for (int i = 0; i < dr.FieldCount; i++)

            {

                strLine = strLine + dr.GetName(i).ToString() + "|";

            }

            strLine = strLine.Substring(0, strLine.Length - 1);

            System.IO.StreamWriter sw = new System.IO.StreamWriter(strDir + strFileName, true);

            sw.WriteLine(strLine);

            sw.Close();

            sw = null;

       

            //writing record set to file

            while (dr.Read())

            {

                strLine = "";

                for (int i = 0; i < dr.FieldCount; i++)

                {

                    strLine = strLine + dr.GetValue(i).ToString() + "|";

                }

                strLine = strLine.Substring(0, strLine.Length - 1);

                System.IO.StreamWriter sw1 = new System.IO.StreamWriter(strDir + strFileName, true);

                sw1.WriteLine(strLine);

                sw1.Close();

                sw1 = null;

            }

            // TODO: Add your code here

            Dts.TaskResult = (int)ScriptResults.Success;

This is just a sample script to demonstrate; you can make it more generic, divide it into smaller function and call that in MAIN method.

Anyway, you are now ready to run package by hitting F5 after writing script, close script edition and close “Script task editor”.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com