Welcome To TechBrothersIT: How to Convert CSV/Text Files to Excel Files in SSIS Package by using Script Task

Scenario: Download Script

You are working as ETL Developer / SSIS Developer for Car Insurance company. They receive text files in their source folder. You need to create an SSIS Package that should convert these text files into Excel Files. The Excel File Name should be the same as Text file and Sheet Name should also be same as file name.

Here are couple of samples files I am using for test

Log File Information : In case your SSIS Package fail. Log file will be created in the same folder where your Excel File will be created.The name of log file will be same like your source file that Package was processing.

Solution:

We care going to use Script Task in SSIS Package to convert Text/CSV/Tab delimited files to Excel Files. We are going to create variable in our SSIS Package such as FileExtension and FileDilimeter. By changing the values of these variable we can use our SSIS Package to handle different type of text files.

Step 1: Create new SSIS Package with Variables to Make it Dynamic

Open SSDT ( Sql Server Data Tools) and create new SSIS Package. After that create below variables

DestinationFolderPath: Folder path where you would like to create your Excel Files

FileDelimiter : Provide the delimiter such as comma (,), Pipe( | ) Whatever your files are using.

FileExtension : Provide the extension of files you would like to convert such .txt, .csv

SourceFolderPath : Source folder path where text files exists

Create Variables in SSIS Package to use in Script Task to Convert Text Files to Excel Files

Step 2: Add Script Task to SSIS Package and Map Variables

Bring Script Task to Control Flow Pane and open it by double clicking. Add the SSIS Package variables to it so we can use inside.

Add variables to Script Task in SSIS Package to use for converting CSV files to Excel Files

Step 3: Add Script to Script task Editor in SSIS Package to Convert Text Files to Excel Files

Click Edit Button and it will open Script Task Editor.

Under #region Namespaces, I have added below code

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::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)";
                    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::DestinationFolderPath"].Value.ToString() 
                    + "\\" +"ErrorLog_"+DateTime.Now.ToString("yyyyMMddHHmmss")+".log"))
         
                {
                    sw.WriteLine(exception.ToString());
                    Dts.TaskResult = (int)ScriptResults.Failure;

                }
                               
            }

Step 4: Save Script and Run your SSIS Package to Convert Text Files to Excel Files

Save the script in Script Task Editor and execute your SSIS Package. It should read each of the file from Source Folder and create new excel file in Destination Folder. The package do not delete the files from Source Folder. You can add that script to Script Task or use File system task to do that.

As I had two text files, It created two excel files for me. One for each Text file.

How to convert CSV files to Excel Files in SSIS Package by using Script Task- C# scripting language

Check out our other posts/videos for Dynamic Excel Source and Destination

1 comment:

UnknownMarch 27, 2019 at 3:24 AM
Hello Aamir,

Good Morning Thanks for the SSIS videos with great knowledge to learn and implement in our projects. I have few queries in regards to one of the SSIS Video (Converting CSV File to Excel using Script Task).

I have a scenario where their is huge CSV File with data in some fields and with no data in some other fields, and i see that the script that you have provided has limitations and in regards to my requirement i need to add few more lines of code in the script.

As i have no knowledge on C Sharp, i am facing difficulty in making those changes. It would be a great help if you could help with those kind of videos as well.

Note: Only a member of this blog may post a comment.

Welcome To TechBrothersIT

Label

How to Convert CSV/Text Files to Excel Files in SSIS Package by using Script Task - SSIS Tutorial

Scenario: Download Script

Log File Information : In case your SSIS Package fail. Log file will be created in the same folder where your Excel File will be created.The name of log file will be same like your source file that Package was processing.

Solution:

1 comment: