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.
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
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
- How to Load Data from Excel Files when Number of Columns can decrease or order is changed in Excel Sheet
- How to Load Only Matching Column Data to SQL Server Table from Multiple Excel Files (Single Sheet per file) Dynamically in SSIS Package
- How to Load Excel File Names with Sheet Names ,Row Count,Last Modified Date, File Size in SQL Server Table
- How to Load Multiple Excel Files with Multiple Sheets to Single SQL Server Table by using SSIS Package
- How to Load Matching Sheets from Excel to Table and Log Not Matching Sheets Information in SQL Server Table
- How to create Table for each sheet in Excel Files and load data to it dynamically in SSIS Package
- How to Create Table per Excel File and Load all Sheets Data Dynamically in SSIS Package by using Script Task
- How to create CSV file per Excel File and Load All Sheets from Excel File to it in SSIS Package
- How to Create CSV File for Each Excel Sheet from Excel Files in SSIS Package
- How to Load Excel File Name and Sheet Name with Data to SQL Server in SSIS Package
- How to Import data from Multiple Excel Sheets with a pattern of sheet names from Multiple Excel File in SSIS Package
- How to import Data from Excel Files for specific Sheet Name to SQL Server Table in SSIS Package
- Load Data To Tables according to Excel Sheet Names from Excel Files dynamically in SSIS Package
- How to Load Excel Files with Single/ Multiple Sheets to SQL Server Tables according to Excel File Name Dynamically
- How to Read Excel Sheet Data after Skipping Rows in SSIS Package by using Script Task
- How to read data from Excel Sheet and Load to Multiple Tables by using Script Task in SSIS Package
- How to create Excel File Dynamically from SQL server Table/View by using Script Task in SSIS Package
- How to create Excel File Dynamically for Stored Procedure Results in SSIS Package by using Script Task
- How to Export SQL Server Tables from Database to Excel File Dynamically in SSIS Package by using Script Task
- How to Convert CSV/Text Files to Excel Files in SSIS Package by using Script Task
- How to Load All CSV Files to Excel Sheets ( Sheet Per CSV) in single Excel File in SSIS Package
- How to Load All CSV Files to Single Excel Sheet with File Names in an Excel File Dynamically in SSIS Package
- How to Create Sample Excel file with Sheet from each table with Top 1000 Rows per sheet in SSIS Package
- How to Export Data to Multiple Excel Sheets from Single SQL Server Table in SSIS Package
ReplyDeleteThanks , this works well. But i do have some values which has quotes in between the text, like Australian Int'l School. The code fails with the following error for such records. What change can be done to correct this?
Syntax error (missing operator) in query expression ''Australian Int'l School
Ace4sure is the website that deals in preparation material for the exam for many years. According to my exposure and research, this is the right platform where you can get exact HPE6-A42 Dumps Questions.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteHi I'm also having the issue where the package is throwing the syntax error Syntax error (missing operator) in query expression where there are single quotes in the text. Is there a solution for this? Many thanks
ReplyDeleteHi,
ReplyDeleteIs it possible to have numeric values displayed as numbers, not as strings in the excel?
facing the below error
ReplyDeleteSystem.Data.OleDb.OleDbException (0x80004005): IErrorInfo.GetDescription failed with E_FAIL(0x80004005)
The structure will permit you to make an Excel article and control a portion of the essential functionalities, for example, getting worksheet data and extricating information from the worksheet given a reach.
ReplyDeleteExcel spreadsheet consultant
Thanks for this. Helped me out a lot!
ReplyDeleteHi! I'm try start your code, but i have read-wrute error. Can you help me with next error:
ReplyDeleteSystem.Data.OleDb.OleDbException (0x80004005): cannot update. database or object is read-only
I have a problem with data in a sql table that contains the ' character in its name.
ReplyDeleteWhat to correct in the code so that this error is not there?
Not easy to fix with table names containing quotes in their name, as strings are used throughout this code. Can you create a view that selects from the table and run the code against the view?
Deletethe modification below worked for me.
Deletecolumnvalues += "'" + table.Rows[index].ItemArray[i].ToString().Replace("'", "''") + "',";
Good luck!
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].ToString().Replace("'", "''") + "',";
}
columnvalues = columnvalues.TrimEnd(',');
var command = sqlCommandInsert + columnvalues + ")";
Excel_OLE_Cmd.CommandText = command;
Excel_OLE_Cmd.ExecuteNonQuery();
}
I Need write table in existing excel template with out creating new excel sheet . Also table also come with dynamic fileds
ReplyDeleteExcellent article. However I am facing the below error:
ReplyDelete"The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine."
Pretty sure the Microsoft.ACE.OLEDB.12.0 is registered and works for other SSIS packages. I inserted the $ in sheetname too. Still couldnt resolve the above error
Worked great, thanks for posting!
ReplyDeleteThis is very informative and interesting for me. Thank you for such a wonderful article and for sharing. God bless you. We also provide service for Travel Portal System With Dynamic Packaging for more info visit our website.
ReplyDeleteThis Article is Awesome. It’s helped me a lot. Please keep up your good work. We are always with you and Waiting for your new interesting articles. Dynamic Packaging System Software.
ReplyDeleteMaster Excel 2016 Essential Skills is very much organized with simple to follow meetings and examples. One thing you will see straight away is that the book is bigger than numerous other Excel books. placement cells database
ReplyDelete