Under #region Namespaces, I have added below code using System.IO; using System.Data.OleDb; using System.Data.SqlClient; using System.Runtime.InteropServices; Under public void Main() { I have added the below code. string datetime = DateTime.Now.ToString(“yyyyMMddHHmmss”); try { //Declare Variables string FolderPath = Dts.Variables[“User::New”].Value.ToString(); //USE ADO.NET Connection from SSIS Package to get data from table SqlConnection myADONETConnection = new SqlConnection(); myADONETConnection = (SqlConnection)(Dts.Connections[“DB_Connection”].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(); string ExcelFileName = “”; ExcelFileName = SchemaName + “_” + TableName + “_” + 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;\””; //Load Data into DataTable from SQL ServerTable 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::New”].Value.ToString() + “\\” + “ErrorLog_” + datetime + “.log”)) { sw.WriteLine(exception.ToString()); Dts.TaskResult = (int)ScriptResults.Failure; } } }
Start with MSBI video as mentioned below to learn topic practically in-depth: How do I export data from SSIS to Excel?On the SSIS menu, select New connection. In the Add SSIS Connection Manager dialog box, select EXCEL and then Add. Create the connection manager at the same time that you configure the Excel Source or the Excel Destination on the Connection manager page of the Excel Source Editor or of the Excel Destination Editor.
How do I export SSIS to CSV?Double-click on the Flat File Destination icon to open the Flat File Destination Editor as in the window below. Click on the New button to open the Flat File Format window, select format of the destination file and click on OK button. See the different Flat File formats that can be used: Delimited.
How do I export SSIS execution results?How to export SSIS Package execution log. Open SSMS (SQL Server Management Studio).. Navigate to following node (Your Server> Integration Services Catalog > SSISDB > YourFolder > YourProject > YourPackage).. Right click on Package > Reports > Standard Reports > All executions like below.. How do I export SQL Server data to multiple Excel spreadsheets using SSIS?Solution:. Step 1: Create new SSIS Package and create Variables.. ColumnNameForGrouping : Provide the column you would like to use for creating sheet data.. ExcelFileName : Provide the excel file name you would like to create.. FolderPath: Provide the Path where you would like to create excel files.. |