tag:blogger.com,1999:blog-2379842053067321961.post6242374274617272065..comments2024-03-28T05:21:44.049-07:00Comments on Welcome To TechBrothersIT: How to Export Data to Multiple Excel Sheets from Single SQL Server Table in SSIS Package - SSIS TutorialAamir Shahzadhttp://www.blogger.com/profile/16777994869678463807noreply@blogger.comBlogger14125tag:blogger.com,1999:blog-2379842053067321961.post-37238818493035586352022-03-14T02:27:42.808-07:002022-03-14T02:27:42.808-07:00facing same issue. using same code. no changes at ...facing same issue. using same code. no changes at all. only thing is. my column is of INT datatype based on which grouping is happening. thanks in advance ! rbkhttps://www.blogger.com/profile/01249497497955833208noreply@blogger.comtag:blogger.com,1999:blog-2379842053067321961.post-15981981214809707962022-03-14T02:24:41.727-07:002022-03-14T02:24:41.727-07:00you can use below code : for decimal/int = use num...you can use below code : for decimal/int = use number or varchar/nvarchar = use text :<br /> int columnIndex;<br /> foreach (DataTable table in ds.Tables)<br /> {<br /> columnIndex = 0;<br /> foreach (DataColumn column in table.Columns)<br /> {<br /> //TableColumns += column + "],[";<br /><br /> switch(columnIndex)<br /> {<br /> case 0:<br /> TableColumns += column + "] text,["; <br /> break;<br /> <br /> default:<br /> TableColumns += column + "] number,["; <br /> break;<br /> }<br /><br /> columnIndex++;<br /> }<br /> }rbkhttps://www.blogger.com/profile/01249497497955833208noreply@blogger.comtag:blogger.com,1999:blog-2379842053067321961.post-16470117427935365752022-03-14T02:22:36.703-07:002022-03-14T02:22:36.703-07:00This comment has been removed by the author.rbkhttps://www.blogger.com/profile/01249497497955833208noreply@blogger.comtag:blogger.com,1999:blog-2379842053067321961.post-23148769545272055202022-03-14T02:21:18.178-07:002022-03-14T02:21:18.178-07:00you code works perfectly fine. but i'm getting...you code works perfectly fine. but i'm getting underscore in front of every sheet name. my grouping column is INT datatype. three digit number based on which I am creating multiple sheets. can you help me with removal of underscore? using same code as it is. no change ! rbkhttps://www.blogger.com/profile/01249497497955833208noreply@blogger.comtag:blogger.com,1999:blog-2379842053067321961.post-23737892466127656592020-06-12T14:12:11.822-07:002020-06-12T14:12:11.822-07:00I got it to the end up until running the script ru...I got it to the end up until running the script run succesfully, but the file is nowhere to be found on the FolderPath. I tried changing file path or giving all the permissions to the folder to check if that was the issue.Pakmanhttps://www.blogger.com/profile/06647115502947278291noreply@blogger.comtag:blogger.com,1999:blog-2379842053067321961.post-87490630756327741592020-03-24T04:31:26.332-07:002020-03-24T04:31:26.332-07:00How did you add template to this process? Can you ...How did you add template to this process? Can you please write steps?bradohttps://www.blogger.com/profile/13571723039508251065noreply@blogger.comtag:blogger.com,1999:blog-2379842053067321961.post-76059450403322271852020-01-07T11:04:16.777-08:002020-01-07T11:04:16.777-08:00The code works great. But how can you keep the dat...The code works great. But how can you keep the data type from SQL when exporting into excel? my date and int column outputs are showing as text.Anonymoushttps://www.blogger.com/profile/15677402898707000982noreply@blogger.comtag:blogger.com,1999:blog-2379842053067321961.post-50038060095246013502020-01-07T10:39:54.910-08:002020-01-07T10:39:54.910-08:00The code works great. But how can you keep the dat...The code works great. But how can you keep the data type from sql when exporting into excel? my date and int column outputs are showing as text. Anonymoushttps://www.blogger.com/profile/15677402898707000982noreply@blogger.comtag:blogger.com,1999:blog-2379842053067321961.post-50180731450644053072019-07-15T02:39:58.034-07:002019-07-15T02:39:58.034-07:00Hi, I added 2 parts of the code (I couldn't pa...Hi, I added 2 parts of the code (I couldn't paste the whole code into 1 comment) in order to create several excel files instead of worksheets. You need to remove the parameter ExcelFileName and use this field in the code to assign the dynamic name for your new excels. I used country name as ExcelFileName. Good luck!Karinahttps://www.blogger.com/profile/03219986550746430916noreply@blogger.comtag:blogger.com,1999:blog-2379842053067321961.post-4660792778038899762019-07-15T02:36:20.176-07:002019-07-15T02:36:20.176-07:00//Write Data to Excel Sheet from DataTable dynamic... //Write Data to Excel Sheet from DataTable dynamically<br /> foreach (DataTable table in ds.Tables)<br /> {<br /> //Use OLE DB Connection and Create Excel Sheet<br /> Excel_OLE_Con.ConnectionString = connstring;<br /> Excel_OLE_Con.Open();<br /> Excel_OLE_Cmd.Connection = Excel_OLE_Con;<br /> Excel_OLE_Cmd.CommandText = "Create table [" + CountryValue + "] (" + TableColumns + ")";<br /> Excel_OLE_Cmd.ExecuteNonQuery();<br /><br /> String sqlCommandInsert = "";<br /> String sqlCommandValue = "";<br /> foreach (DataColumn dataColumn in table.Columns)<br /> {<br /> sqlCommandValue += dataColumn + "],[";<br /> }<br /><br /> sqlCommandValue = "[" + sqlCommandValue.TrimEnd(',');<br /> sqlCommandValue = sqlCommandValue.Remove(sqlCommandValue.Length - 2);<br /> sqlCommandInsert = "INSERT into [" + CountryValue + "] (" + sqlCommandValue + ") VALUES(";<br /><br /> int columnCount = table.Columns.Count;<br /> foreach (DataRow row in table.Rows)<br /> {<br /> string CountryValues = "";<br /> for (int i = 0; i < columnCount; i++)<br /> {<br /> int index = table.Rows.IndexOf(row);<br /> CountryValues += "'" + table.Rows[index].ItemArray[i] + "',";<br /><br /> }<br /> CountryValues = CountryValues.TrimEnd(',');<br /> var command = sqlCommandInsert + CountryValues + ")";<br /><br /><br /> Excel_OLE_Cmd.CommandText = command;<br /> Excel_OLE_Cmd.ExecuteNonQuery();<br /> <br /> }<br /><br /> Excel_OLE_Con.Close();<br /> }<br /> <br /> }<br /> Dts.TaskResult = (int)ScriptResults.Success;<br /> }<br /><br /> catch (Exception exception)<br /> {<br /><br /> // Create Log File for Errors<br /> using (StreamWriter sw = File.CreateText(Dts.Variables["User::FolderPath"].Value.ToString() + "\\" +<br /> datetime + ".log"))<br /> {<br /> sw.WriteLine(exception.ToString());<br /> Dts.TaskResult = (int)ScriptResults.Failure;<br /><br /> }<br /><br /> }<br />Karinahttps://www.blogger.com/profile/03219986550746430916noreply@blogger.comtag:blogger.com,1999:blog-2379842053067321961.post-88278406679286290942019-07-15T02:35:57.163-07:002019-07-15T02:35:57.163-07:00string datetime = DateTime.Now.ToString("yyyy... string datetime = DateTime.Now.ToString("yyyyMMdd");<br /> try<br /> {<br /><br /> //Declare Variables<br /> string FolderPath = Dts.Variables["User::FolderPath"].Value.ToString();<br /> string TableName = Dts.Variables["User::TableName"].Value.ToString();<br /> string ColumnNameForGrouping = Dts.Variables["User::ColumnNameForGrouping"].Value.ToString();<br /> string ExcelFileName = "";<br /><br /> OleDbConnection Excel_OLE_Con = new OleDbConnection();<br /> OleDbCommand Excel_OLE_Cmd = new OleDbCommand();<br /><br /> //USE ADO.NET Connection from SSIS Package to get data from table<br /> SqlConnection myADONETConnection = new SqlConnection();<br /> myADONETConnection = (SqlConnection)(Dts.Connections["DBConn"].AcquireConnection(Dts.Transaction) as SqlConnection);<br /><br /><br /> //Read distinct Group Values for each Excel Sheet<br /> string query = "Select distinct " + ColumnNameForGrouping + " from " + TableName; //+ " where Key_Month = '201907'";<br /><br /> //MessageBox.Show(query.ToString());<br /> SqlCommand cmd = new SqlCommand(query, myADONETConnection);<br /> //myADONETConnection.Open();<br /> DataTable dt = new DataTable();<br /> dt.Load(cmd.ExecuteReader());<br /> myADONETConnection.Close();<br /><br /> //Loop through values for ColumnNameForGroup<br /> <br /> foreach (DataRow dt_row in dt.Rows)<br /> {<br /> string CountryValue = "";<br /> object[] array = dt_row.ItemArray;<br /> CountryValue = array[0].ToString();<br /> ExcelFileName = CountryValue + "_" + datetime;<br /><br /> //Construct ConnectionString for Excel<br /> string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + FolderPath + ExcelFileName<br /> + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";<br /><br /> //drop Excel file if exists<br /> File.Delete(FolderPath + "\\" + ExcelFileName + ".xlsx");<br /><br /><br /> //Load Data into DataTable from SQL ServerTable<br /> string queryString =<br /> "SELECT * from " + TableName + " Where " + ColumnNameForGrouping + "='" + CountryValue + "'";<br /> SqlDataAdapter adapter = new SqlDataAdapter(queryString, myADONETConnection);<br /> DataSet ds = new DataSet();<br /> adapter.Fill(ds);<br /><br /><br /> //Get Header Columns<br /> string TableColumns = "";<br /><br /> // Get the Column List from Data Table so can create Excel Sheet with Header<br /> foreach (DataTable table in ds.Tables)<br /> {<br /> foreach (DataColumn column in table.Columns)<br /> {<br /> TableColumns += column + "],[";<br /> }<br /> }<br /><br /> // Replace most right comma from Columnlist<br /> TableColumns = ("[" + TableColumns.Replace(",", " Text,").TrimEnd(','));<br /> TableColumns = TableColumns.Remove(TableColumns.Length - 2);<br /> //MessageBox.Show(TableColumns);Karinahttps://www.blogger.com/profile/03219986550746430916noreply@blogger.comtag:blogger.com,1999:blog-2379842053067321961.post-49921130216623971372019-06-19T11:14:02.782-07:002019-06-19T11:14:02.782-07:00I have got this code to work however it is putting...I have got this code to work however it is putting an underscore in front of everysheet name. Also i do I use a template using this codeBrichhttps://www.blogger.com/profile/02103868319872029383noreply@blogger.comtag:blogger.com,1999:blog-2379842053067321961.post-17816052717545066492019-02-25T10:32:47.469-08:002019-02-25T10:32:47.469-08:00I was trying to create excel as per your tutorial ...I was trying to create excel as per your tutorial but I am getting below error.<br />I am using SSIS 2012 I am very poor in scripting <br /> at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)<br /> at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)<br /> at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)<br /> at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)<br /> at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript() Anonymoushttps://www.blogger.com/profile/15952989272931011587noreply@blogger.comtag:blogger.com,1999:blog-2379842053067321961.post-40646102268783756282019-02-15T00:52:20.190-08:002019-02-15T00:52:20.190-08:00I'm trying to create multiple excel files inst...I'm trying to create multiple excel files instead of multiple excel sheets.<br />Can you please help.Anonymoushttps://www.blogger.com/profile/07232782236129983532noreply@blogger.com