Welcome To TechBrothersIT: How to create Excel File Dynamically from SQL server Table/View by using Script Task in SSIS Package

Scenario: Download Script

You are working as ETL Developer / SSIS Developer in one of the Auto Insurance Company. You got this requirement " Create an SSIS Package that should read the data from SQL server Table and create an Excel file with Datetime and load data from SQL server Table". On each execution the new Excel file should be created. Once problem with requirement is that the SSIS Package should be able to handle new columns dynamically or if columns dropped from Table, it should not fail and simply start creating file with available columns.

Solution:

Long time back, I wrote a post

Create Excel File Dynamically In SSIS

that uses Microsoft.Office.Interop.Excel.dll and we used in script task to create excel file dynamically form SQL server Table.

In this post we are going to use Microsoft.ACE.OLEDB.12.0 provider.

Log File Information : In case your SSIS Package fail. Log file will be created in the same folder where your Excel File will be created. It will have the same datetime like your Excel File.

Step 1: Create Variables to make your SSIS Package Dynamic

Create your SSIS Package in SSDT ( SQL Server Data Tools). Once SSIS Package is created. Create below variables.

ExcelFileName : Provide the name of your Excel File that you would like to create

FolderPath: This will save where you would like to create Excel File

SheetName: Provide the Sheet Name you like to have in your Excel File

TableName : Provide the Table Name or View Name with Schema from which you would like to extract data for your excel sheet in Excel file

Create Variable in SSIS Package to generate Excel File Dynamically from SQL server Table or View by using Script Task

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 Load data from Excel Sheets to SQL Server Table.

Create ADO.NET Connection in SSIS Package to use in Script Task to get data from SQL server Table/View to Load into Excel File Dynamically in SSIS Package

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 Excel File Dynamically from SQL Server Table/ View in SSIS Package

Step 4: Add Script to Script task Editor in SSIS To create Excel File with Datetime Dynamically from SQL Server Table or View

Click Edit Button and it will open Script Task Editor.

Under #region Namespaces, I have added below code

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

Under public void Main() {

I have added below code.

            string datetime=DateTime.Now.ToString("yyyyMMddHHmmss");
            try
            {
                //Declare Variables
                string ExcelFileName = Dts.Variables["User::ExcelFileName"].Value.ToString();
                string FolderPath = Dts.Variables["User::FolderPath"].Value.ToString();
                string TableName = Dts.Variables["User::TableName"].Value.ToString();
                string SheetName = Dts.Variables["User::SheetName"].Value.ToString();
                ExcelFileName = ExcelFileName + "_" + datetime;

                OleDbConnection Excel_OLE_Con = new OleDbConnection();
                OleDbCommand Excel_OLE_Cmd = new OleDbCommand();

                //Construct ConnectionString for Excel
                string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + FolderPath + ExcelFileName
                    + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";

                //drop Excel file if exists
                File.Delete(FolderPath + "\\" + ExcelFileName + ".xlsx");

                //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);

                //Load Data into DataTable from SQL ServerTable
                // Assumes that connection is a valid SqlConnection object.
                string queryString =
                  "SELECT * from " + TableName;
                SqlDataAdapter adapter = new SqlDataAdapter(queryString, myADONETConnection);
                DataSet ds = new DataSet();
                adapter.Fill(ds);


                //Get Header Columns
                string TableColumns = "";

                // Get the Column List from Data Table so can create Excel Sheet with Header
                foreach (DataTable table in ds.Tables)
                {
                    foreach (DataColumn column in table.Columns)
                    {
                        TableColumns += column + "],[";
                    }
                }

                // Replace most right comma from Columnlist
                TableColumns = ("[" + TableColumns.Replace(",", " Text,").TrimEnd(','));
                TableColumns = TableColumns.Remove(TableColumns.Length - 2);
                //MessageBox.Show(TableColumns);


                //Use OLE DB Connection and Create Excel Sheet
                Excel_OLE_Con.ConnectionString = connstring;
                Excel_OLE_Con.Open();
                Excel_OLE_Cmd.Connection = Excel_OLE_Con;
                Excel_OLE_Cmd.CommandText = "Create table " + SheetName + " (" + TableColumns + ")";
                Excel_OLE_Cmd.ExecuteNonQuery();


                //Write Data to Excel Sheet from DataTable dynamically
                foreach (DataTable table in ds.Tables)
                {
                    String sqlCommandInsert = "";
                    String sqlCommandValue = "";
                    foreach (DataColumn dataColumn in table.Columns)
                    {
                        sqlCommandValue += dataColumn + "],[";
                    }

                    sqlCommandValue = "[" + sqlCommandValue.TrimEnd(',');
                    sqlCommandValue = sqlCommandValue.Remove(sqlCommandValue.Length - 2);
                    sqlCommandInsert = "INSERT into " + SheetName + "(" + sqlCommandValue.TrimEnd(',') + ") VALUES(";

                    int columnCount = table.Columns.Count;
                    foreach (DataRow row in table.Rows)
                    {
                        string columnvalues = "";
                        for (int i = 0; i < columnCount; i++)
                        {
                            int index = table.Rows.IndexOf(row);
                            columnvalues += "'" + table.Rows[index].ItemArray[i] + "',";

                        }
                        columnvalues = columnvalues.TrimEnd(',');
                        var command = sqlCommandInsert + columnvalues + ")";
                        Excel_OLE_Cmd.CommandText = command;
                        Excel_OLE_Cmd.ExecuteNonQuery();
                    }

                }
                Excel_OLE_Con.Close();
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch (Exception exception)
            {

                // Create Log File for Errors
                using (StreamWriter sw = File.CreateText(Dts.Variables["User::FolderPath"].Value.ToString() + "\\" +
                    Dts.Variables["User::ExcelFileName"].Value.ToString() + datetime + ".log"))
                {
                    sw.WriteLine(exception.ToString());
                    Dts.TaskResult = (int)ScriptResults.Failure;

                }
            }

Step 5:

Save the script in Script Task Editor and close the window. Run your SSIS Package. It should read the data from table or view you have provided as variable value and it should generate an excel file with sheet name.

I ran the package for my table and here is the file it generate. You can use this SSIS Package as template. By providing different values to variables, you can create excel file for any table or view.

Create New Excel File Dynamically to Export Data from SQL Server Table or View by using Script task in SSIS Package

Check out our other posts/videos for Dynamic Excel Source and Destination

Welcome To TechBrothersIT

Label

How to create Excel File Dynamically from SQL server Table/View by using Script Task in SSIS Package - SSIS Tutorial

Scenario: Download Script

Solution:

Create Excel File Dynamically In SSIS

No comments:

Post a Comment