SSIS : Create C# Script that creates an excel file.

Question

Post reply

SSIS : Create C# Script that creates an excel file.

river1

SSC-Insane

Points: 24309
More actions
December 14, 2017 at 7:42 am

#402751

Dear all,
I have created a script task
1) connects to a txt file - sucessfully
2) Reads the first line of this file - sucessfully
3) tries to create a new excel file - Not sucessfull
As code, please find it below:
public void Main()
{
// TODO: Add your code here

try
{
//Declare Variables
string SourceFolderPath = Dts.Variables["User::SourceFolderPath"].Value.ToString();
string DestinationFolderPath = Dts.Variables["User::DestinationFolderPath"].Value.ToString();
string FileExtension = Dts.Variables["User::FileExtension"].Value.ToString();
string FileDelimiter = Dts.Variables["User::FileDelimiter"].Value.ToString();
string CreateTableStatement = "";
string ColumnList = "";
//Reading file names one by one
string SourceDirectory = SourceFolderPath;
string[] fileEntries = Directory.GetFiles(SourceDirectory, "*" + FileExtension);
foreach (string fileName in fileEntries)
{
// do something with fileName
//MessageBox.Show(fileName);

//Read first line(Header) and prepare Create Statement for Excel Sheet
System.IO.StreamReader file = new System.IO.StreamReader(fileName);
string filenameonly = (((fileName.Replace(SourceDirectory, "")).Replace(FileExtension, "")).Replace("\\", ""));
//CreateTableStatement = (" Create Table [" + filenameonly + "] ([" + file.ReadLine().Replace(FileDelimiter, "] Text,[")) + "] Text)";

CreateTableStatement = (" Create Table [" + filenameonly + "] ([" + file.ReadLine().Replace(FileDelimiter, "] Text,[")) + "] Text)";
file.Close();
//Construct ConnectionString for Excel
string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + DestinationFolderPath + "\\" + filenameonly
+ ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";
OleDbConnection Excel_OLE_Con = new OleDbConnection();
OleDbCommand Excel_OLE_Cmd = new OleDbCommand();
//Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";

//drop Excel file if exists
File.Delete(DestinationFolderPath + "\\" + filenameonly + ".xlsx");
Excel_OLE_Con.ConnectionString = connstring;
MessageBox.Show(connstring.ToString());
Excel_OLE_Con.Open();
MessageBox.Show("5");
Excel_OLE_Cmd.Connection = Excel_OLE_Con;
MessageBox.Show("6");
//Use OLE DB Connection and Create Excel Sheet
Excel_OLE_Cmd.CommandText = CreateTableStatement;
Excel_OLE_Cmd.ExecuteNonQuery();
MessageBox.Show("4");
//Writing Data of File to Excel Sheet in Excel File
int counter = 0;
string line;
System.IO.StreamReader SourceFile =
new System.IO.StreamReader(fileName);
while ((line = SourceFile.ReadLine()) != null)
{
if (counter == 0)
{
ColumnList = "[" + line.Replace(FileDelimiter, "],[") + "]";
}
else
{
string query = "Insert into [" + filenameonly + "] (" + ColumnList + ") VALUES('" + line.Replace(FileDelimiter, "','") + "')";
// MessageBox.Show(query.ToString());
var command = query;
Excel_OLE_Cmd.CommandText = command;
Excel_OLE_Cmd.ExecuteNonQuery();
}
counter++;
}
Excel_OLE_Con.Close();
SourceFile.Close();
Dts.TaskResult = (int)ScriptResults.Success;
}
}
catch (Exception exception)
{
MessageBox.Show("I am in the errors catch");
// Create Log File for Errors
using (StreamWriter sw = File.CreateText(Dts.Variables["User::DestinationFolderPath"].Value.ToString()
+ "\\" + "ErrorLog_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".log"))
{
sw.WriteLine(exception.ToString());
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
}
I get the error on nthe command ; Excel_OLE_Con.Open();
The error is:
System.Data.OleDb.OleDbException (0x80004005): External table is not in the expected format.
   at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
   at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.OleDb.OleDbConnection.Open()
   at ST_4cbc72eca9334175970bb0becb97fb76.ScriptMain.Main()
Can you please help me understand what is happening?
Thank you
April 1, 2019 at 12:41 pm

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

river1 SSC-Insane Points: 24309 More actions · Answer 1

I was able to overcome the problem, the connection string was with txt instead of creating the

.xlsx

Now I receive a different error, please see below:

System.Data.OleDb.OleDbException (0x80004005): Could not find installable ISAM.
   at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
   at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.OleDb.OleDbConnection.Open()
   at ST_4cbc72eca9334175970bb0becb97fb76.ScriptMain.Main()

river1 SSC-Insane Points: 24309 More actions · Answer 2

river1

SSC-Insane

Points: 24309

December 14, 2017 at 9:15 am

#1972163

What I get as connection string

tim.ffitch 25252 SSCrazy Points: 2055 More actions · Answer 3

Having recently converted a package to use the ACE drivers instead of JET to export to Excel, you need to ensure you install the 32bit version of the ACE drivers and not the 64bit. Then make sure you package runs in 32bit mode.