SSIS PACKAGE - Script Task

Khan345 · Sep 5, 2020

I have Convert Text File to Excel Files in SSIS Package by using Script Task with the help of following help link : Welcome To TechBrothersIT: How to Convert CSV/Text Files to Excel Files in SSIS Package by using Script Task - SSIS Tutorial

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

The system where I want to use it 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 to get the output file extension in lower case?

I am pasting below script which is used in the tutorial:

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;

}

}

thanks,

SK

Continue reading...

SSIS PACKAGE - Script Task

Khan345

Guest

Similar threads