I have a SSIS package in which I've added a Script Task. In the Script Task I've given a oledb connection manager and acquired a data table from the database and the query is just working fine. Now I have to put the data table into an excel file. This is the primary solution I need to arrive at.
There are two scenarios though
Scenario 1 : Creating an excel file template WITHOUT HEADERS and put the data into that excel file (DataTable has headers too) including DataTable's headers
Scenario 2 : Dynamically creating an excel file and put the data into it (I'm searching an answer for this scenario for a long time).
Please guide me through to find a solution :)
Below is my code which I've implemented in my Script Task :
public void Main()
{
DataTable DtUsers = new DataTable();
ConnectionManager ConnManager;
OleDbConnection ConnOledb;
OleDbDataReader dataReader;
string SqlString = "MYQUERY";
try
{
Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100 ConnParams;
ConnManager = Dts.Connections["OledbConnectionUnit"];
ConnParams = (Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100)ConnManager.InnerObject;
ConnOledb = (OleDbConnection)ConnParams.GetConnectionForSchema();
OleDbCommand CommOledb = new OleDbCommand();
OleDbDataAdapter DaUsers = new OleDbDataAdapter();
try
{
CommOledb.CommandText = SqlString.ToString();
CommOledb.CommandType = CommandType.Text;
CommOledb.Connection = ConnOledb;
dataReader=CommOledb.ExecuteReader();
DtUsers.Load(dataReader);
foreach (DataRow DrRows in DtUsers.Rows)
{
DrRows["Column_Name"].ToString();
}
}
catch (Exception ex)
{
throw ex;
}
Dts.TaskResult = (int)ScriptResults.Success;
}
catch(Exception ex)
{
throw ex;
}
}
}
Thanks in advance