How to create Text or CSV File Dynamically from a Stored Procedure in SSIS Package - SSIS Tutorial

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.
How to create flat file dynamically from Stored Procedure results in SSIS Package-SSIS Tutorial



Video Demo: How to create flat file dynamically from Stored Procedure results in SSIS Package







 Related Posts / Videos on Dynamic Text / CSV files by Script Task