SSIS PACKAGE - SCRIPT TASK to Convert txt to XLS

[Khan345]

Hi,

The Following code convert the text file to Excel file in SSIS Package by using Script Task;

When I run this package, it convert the text files to Excel files but the output excel file extension is always UPPER CASE (.XLSX)

Unfortunately The system where I want to use the output file does not accept Capital latter extension. It only accept Lower case extension like (.xlsx).

Can anyone please let me know what needs to be change in below script code to get the output file extension in lower case?




using System.IO;
using System.Data.OleDb;


Under public void Main() {
I have added below code.

try
{

//Declare Variables
string SourceFolderPath = Dts.Variables["User::SourceFolderPath"].Value.ToString();
string DestinationFolderPath = Dts.Variables["User:estinationFolderPath"].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)";
file.Close();
//MessageBox.Show(CreateTableStatement.ToString());


//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();



//drop Excel file if exists
File.Delete(DestinationFolderPath + "\\" + filenameonly + ".xlsx");
Excel_OLE_Con.ConnectionString = connstring;
Excel_OLE_Con.Open();
Excel_OLE_Cmd.Connection = Excel_OLE_Con;

//Use OLE DB Connection and Create Excel Sheet
Excel_OLE_Cmd.CommandText = CreateTableStatement;
Excel_OLE_Cmd.ExecuteNonQuery();

//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)
{


// Create Log File for Errors
using (StreamWriter sw = File.CreateText(Dts.Variables["User:estinationFolderPath"].Value.ToString()
+ "\\" +"ErrorLog_"+DateTime.Now.ToString("yyyyMMddHHmmss")+".log"))

{
sw.WriteLine(exception.ToString());
Dts.TaskResult = (int)ScriptResults.Failure;

}

}

Continue reading...