Scenario: Download Script
You are working as ETL Developer / SSIS Developer or maybe Dot Net Developer. You got this requirement where you have to Export all the Tables from SQL Server Database to Excel File. Each Table should be export as SchemaName_TableName as Sheet to single Excel File.
So let's say you have 10 user tables in a Database and you would like to export all of them. The SSIS Package should be able to create an Excel File( You can provide File Name in variable_DateTime) and this excel file should have 10 sheets, one for each table and load the data.
If you will return the SSIS Package, it will create new Excel file with Sheets equal to user tables in our database.
In excel, I am creating all the fields as Text Data type. If you would like to chose few tables, you can change the query inside script task that returns list of tables from database.
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.
Solution:
We are going to use Script Task in our SSIS Package to create an Excel file and export all tables data to it from SQL server Database.
Step 1:Create new SSIS Package and Variables
Create an SSIS Package by using SQL Server Data Tools. Once SSIS Package is created, create variables as shown below.
FolderPath: Where you would like to create an Excel File
ExcelFileName: The name of Excel file you would like to create
How to Export all tables from SQL server Database to Excel File in SSIS Package 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 get tables data to export to Excel. This ADO.Net connection should be pointing to Database that we want to export to an Excel File.
Create ADO.NET Connection in SSIS Package to use in Script Task to Export All Tables from Database to Excel File
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.
Use SSIS Package variables in Script Task to generate Excel File for all the Database Tables
Step 4: Add Script to Script task Editor in SSIS Package to Export All Tables from SQL Server Database to Excel File
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(); 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); //Read list of Tables with Schema from Database string query = "SELECT Schema_name(schema_id) AS SchemaName,name AS TableName FROM sys.tables WHERE is_ms_shipped = 0"; //MessageBox.Show(query.ToString()); SqlCommand cmd = new SqlCommand(query, myADONETConnection); //myADONETConnection.Open(); DataTable dt = new DataTable(); dt.Load(cmd.ExecuteReader()); myADONETConnection.Close(); //Loop through datatable(dt) that has schema and table names foreach (DataRow dt_row in dt.Rows) { string SchemaName = ""; string TableName = ""; object[] array = dt_row.ItemArray; SchemaName = array[0].ToString(); TableName = array[1].ToString(); //Load Data into DataTable from SQL ServerTable // Assumes that connection is a valid SqlConnection object. string queryString = "SELECT * from " + SchemaName + "." + 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 [" + SchemaName+"_"+TableName + "] (" + 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 [" + SchemaName+"_"+TableName + "] (" + sqlCommandValue +") 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 your SSIS Pakcage and Execute to Export All Tables form Database to Excel File
I will suggest you to test this package in dev and other environments to calculate time. As it is going to export each table from database and sometime database are really big, it can take long time. I had small database on my laptop and it worked fine.
How to Export All tables form SQL server Database to Excel File in SSIS Package by using Script Task
Check out our other posts/videos for Dynamic Excel Source and Destination
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
What do i need to do to change from using ado connection to oledb connection for sqlserver in script task?
ReplyDeleteone of my field has single quote in it and its failing...can you help me fix it (example: john's)
ReplyDeletenvm i figure it out
ReplyDeleteHi do you have a solution for the single quote issue? Many thanks
ReplyDeleteHello Chuck,
ReplyDeleteWhat is your solution please ? I have the same issue...
Mylene
What do I have to do if I want to load stored procedure(For example, I have 3 tables and get its result by sp) result into multiple excel spreadsheets dynamically? Please help me.
ReplyDeletehow can i add a where clause to the initial select?
ReplyDelete1. I have column names as special characters like columns ([EMP NO.] , [EMP A/C NAME] ) so this kind of columns are not allowing into excel file using above script.
ReplyDelete2. Also i have data in sql table with special characters like (AB'CDFF). The special data also not allowing.
Can you please suggest on above scenarios.
If I need to create connection to ado.net I use salesforce net connector
ReplyDelete