How To Create Csv File Dynamically In Ssis

Scenario : Download Script

You are working as SQL Server Integration Services developer. You need to create an SSIS Package that should execute Stored Procedure and write the results to flat file. Every time the SSIS Package will run, it should create new flat file with date-time.

This is simple requirement. You might think to use OLE DB Source and then use Flat file destination. But there is small problem. The definition of Stored Procedure can change anytime, that means the number of columns returned by Stored Procedure can change.

You need to develop an SSIS Package in such a way that you don't have to reopen and remap the columns every-time the definition of Stored Procedure change.

Solution:

We are going to use the Script Task in SSIS Package to create flat file dynamically form Stored Procedure result-set.

Step 1: Create new SSIS Package and create Variables

Open your SSDT ( SQL Server Data Tools) and create new SSIS Package. Once done, create below variables as shown.

FlatFileNamePart:Provide the file name that you would like to create. Date-time part will be added to it.

DestinationFolder:Provide the destination folder path where you would like to create your files.

StoredProcedureName:Provide the Stored Procedure name with schema from which you like to get data

LogFolder:Provide the Log folder path where you would like to create log file in case of error in Script Task.

FileDelimiter :Provide the file delimiter you would like to use, you can provide comma, pipe etc.

FileExtension: Provide the extension for your file such as txt or csv etc.

Create variable in SSIS Package to generate flat file dynamically from Stored Procedure

Step 2: Create ADO.NET Connection in SSIS Package to use in Script Task

Create ADO.NET Connection Manager so we can use in Script Task to get Stored Procedure data to export to flat file. This ADO.Net connection should be pointing to Database which contains the Stored Procedure from which we want to export data to flat file.

Create ADO.NET Connection in SSIS Package to use in Script Task to export Stored Procedure results to flat file dynamically

Step3: Add Variables to Script Task to use from SSIS Package

Bring the Script Task on Control Flow Pane in SSIS Package and open by double clicking Check-box in front of variable to add to Script Task.

Add variables to Script Task to generate flat file dynamically from Stored Procedure results in SSIS Package

Step 4: Add Script to Script task Editor in SSIS Package to Export Data from SQL Server Table or View to flat file dynamically.

Click Edit Button and it will open Script Task Editor.

Under #region Namespaces, I have added below code

                    using                    System.IO;                    using                    System.Data.SqlClient;

Under public void Main() {

I have added below code.

                    string                    datetime = DateTime.Now.ToString("yyyyMMddHHmmss");                    try                    {                    //Declare Variables                    string                    FileNamePart = Dts.Variables["User::FlatFileNamePart"].Value.ToString();                    string                    DestinationFolder = Dts.Variables["User::DestinationFolder"].Value.ToString();                    string                    StoredProcedureName = Dts.Variables["User::StoredProcedureName"].Value.ToString();                    string                    FileDelimiter = Dts.Variables["User::FileDelimiter"].Value.ToString();                    string                    FileExtension = Dts.Variables["User::FileExtension"].Value.ToString();                    //USE ADO.NET Connection from SSIS Package to get data from table                    SqlConnection myADONETConnection =                    new                    SqlConnection();                 myADONETConnection = (SqlConnection)(Dts.Connections["DBConn"].AcquireConnection(Dts.Transaction)                    as                    SqlConnection);                    //Execute Stored Procedure and save results in data table                    string                    query =                    "EXEC "+StoredProcedureName;                 SqlCommand cmd =                    new                    SqlCommand(query, myADONETConnection);                 DataTable d_table =                    new                    DataTable();                 d_table.Load(cmd.ExecuteReader());                 myADONETConnection.Close();                    string                    FileFullPath = DestinationFolder +"\\"+ FileNamePart +"_" + datetime + FileExtension;                          StreamWriter sw = null;                         sw = new StreamWriter(FileFullPath, false);                          // Write the Header Row to File                         int ColumnCount = d_table.Columns.Count;                         for (int ic = 0; ic < ColumnCount; ic++)                         {                             sw.Write(d_table.Columns[ic]);                             if (ic < ColumnCount - 1)                             {                                 sw.Write(FileDelimiter);                             }                         }                         sw.Write(sw.NewLine);                          // Write All Rows to the File                         foreach (DataRow dr in d_table.Rows)                         {                             for (int ir = 0; ir < ColumnCount; ir++)                             {                                 if (!Convert.IsDBNull(dr[ir]))                                 {                                     sw.Write(dr[ir].ToString());                                 }                                 if (ir < ColumnCount - 1)                                 {                                     sw.Write(FileDelimiter);                                 }                             }                             sw.Write(sw.NewLine);                                                      }                                                  sw.Close();                                                                    Dts.TaskResult = (int)ScriptResults.Success;                 }                            catch (Exception exception)             {                  // Create Log File for Errors                 using (StreamWriter sw = File.CreateText(Dts.Variables["User::LogFolder"].Value.ToString() + "\\" +                     "ErrorLog_" + datetime + ".log"))                 {                     sw.WriteLine(exception.ToString());                     Dts.TaskResult = (int)ScriptResults.Failure;                   }             }

TSQL Scripts used in the video

                          --Create Procedure without Paramter              Create              procedure              dbo.prc_TotalSale              AS              Begin              Select              *              from              dbo.TotalSale              End              --Alter Stored Procedure to add parameter              Alter              procedure              dbo.prc_TotalSale @Region              VARCHAr(50)              AS              Begin              Select              [SalePersonFName]       ,[SalePersonLName]       ,[ProductName]       ,[ItemsSold]       ,[SoldPrice]        ,Region              from              dbo.TotalSale              where              Region=@Region              End              --Execute Stored Procedure              EXEC              dbo.prc_TotalSale              'ASia'              --Get Distinct Regions              Select              distinct              REgion              from              dbo.TotalSale

Step 5: Save the script and run the Package

Save the script in Script Task editor and then run your SSIS Package. It should create the flat file with date-time every time you will execute package. You have freedom to change the extension of file such as txt or csv. Also you can change delimiter by changing value of variable.

I execute the package and then dropped the column in the stored procedure definition and it was able to create flat file with date-time without any problem.