SSIS -How to Convert Excel File To CSV ( Comma Separated values) File in SSIS Package

Scenario:

Our business users create excel file. We need to send this file to vendor but vendor only accepts csv ( Comma separated values) file. We need to convert excel file into csv file by using SSIS Package. 

Solution:

In this post we will be using Script Task to convert our Excel file into Comma separated values file. The SSIS Package can be modified by using For- each loop container in case where you want to read multiple excel files and convert all of them to csv files. 

Step 1: 

Create an excel file if you don't have one. I have created one for this post as shown below

Fig 1: Sample Excel for to convert to csv by SSIS Package

Step 2: 

Create two variables, one that is pointing to Source Excel file and one for destination csv file as shown below.
Fig 2: Create Source and Destination variables for Excel to Csv conversion SSIS Package


Step 3: 

Bring the Script Task to the Control Flow Pane and then choose the above variables in ReadOnlyVariables list.
Fig 3: Map Source and Destination Path variables in Script Task

Click on Edit Script and paste the below script.

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;
#endregion

namespace ST_a916a8b3a6d640be9f6302fae0a06c8e
{
    /// 
    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    /// 
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute] public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase {
  public void Main() {
  // TODO: Add your code here
//File DataTable from Execel Source File. I have used Sheet1 in my case, if your sheet name is different then change it.
string ConnString; 
 ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Dts.Variables["User::Var_ExcelFilePath"].Value.ToString() +
                ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\";";
            var conn = new OleDbConnection(ConnString);
            conn.Open();
            string query = "SELECT * FROM [Sheet1$]";
            var command = new OleDbCommand(query, conn);
            OleDbDataAdapter adap = new OleDbDataAdapter(command);
            var datatable = new DataTable();
            adap.Fill(datatable);


            //Create csv File

 using (var sw = new StreamWriter(Dts.Variables["User::Var_CsvFilePath"].Value.ToString()))
            {
                for (int row = 0; row < datatable.Rows.Count; row++)
                {
                    var strRow = "";

                    for (int col = 0; col < datatable.Columns.Count; col++)
                    {
         strRow += "\"" + datatable.Rows[row][col].ToString() + "\",";
                        
                    }
                    //remove last , from row
                    strRow = strRow.Remove(strRow.Length - 1);
                    //write row to file
                    sw.WriteLine(strRow);
                }
            }
            Dts.TaskResult = (int)ScriptResults.Success;
        }

        #region ScriptResults declaration

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

    }
}

The code I added is in bold, You can copy this code to your script task and leave rest of your code as it is.

Let's run the Package and check if the file is created successfully.

Fig 4: Converted csv File from Excel by using SSIS Package

27 comments:

  1. Thanks You Very Much for your kind to sharing information ......Very Helpful
    pdf flatten online

    ReplyDelete
  2. If you have a different delimiter, apply the changes at this part

    strRow += "\"" + datatable.Rows[row][col].ToString() + "\"~";

    ReplyDelete
  3. If you are just copying the BOLD Code, dont forget to add the namespaces in BOLD
    using System.Data.OleDb;
    using System.IO;

    ReplyDelete
  4. Most likely the simplest technique to convert your slides to digital photos your self is to purchase an off-the-shelf slide to digital image converter. Sure, these do exist they usually do not price as a lot as you may assume. If you want to learn more about this topic, then please visit https://onlineconvertfree.com/

    ReplyDelete
  5. Finished edition star x is significantly more expert than iMovie, which has XML fare and the sky is the limit from there.
    AnyConv

    ReplyDelete
  6. Very interesting post, Thanks for sharing such useful information here, I must visit your site for more in the future.
    What Is Digital Marketing | Digital Marketing Career Opportunities | Career in Digital Marketing | Digital Marketer Skills

    ReplyDelete
  7. MP4 is a portable Media Player which includes FM transmitters for the transmission of information. flv to mp4

    ReplyDelete
  8. A career in data science is one of the most sought after job these days and for getting ahead in the competition it is important to have relevant skills and knowledge. 360DigiTMG data science training in hyderabad

    ReplyDelete
  9. We have sell some products of different custom boxes.it is very useful and very low price please visits this site thanks and please share this post with your friends. https://www.postinweb.com/ontiva-the-best-tool-for-youtube-converter/

    ReplyDelete
  10. Please help ( using Visual Studio 2019 community edition:

    Error at Script Task: The binary code for the script is not found. Please open the script in designer by clicking Edit Script button and make sure if builds successfully.

    when I build the script get mscorlib error "the directory is not empty"

    ReplyDelete
  11. Many plan understudies struggle changing from understudy to representatives just due to an absence of readiness.Professional graphic design

    ReplyDelete
  12. Great Article. Thank you for sharing! Really an awesome post data science course in Hyderabad

    ReplyDelete
  13. The purpose of the Excel application is to allow users to collect like data in a specific location for current and/or future use. spreadsheet consulting

    ReplyDelete
  14. Many students sooner or later have a question where it is better to order a ready-made essay. It is worth considering that the work should be not only high-quality, but also written according to your individual requirements, as well as have the correct design and meet the standards of the genre. We will help you solve all these issues best writing service reviews.

    ReplyDelete
  15. Wow it is really wonderful and awesome thus it is very much useful for me to understand many concepts and helped me a lot. it is really explainable very well and i got more information from your blog.
    DevOps Training in Hyderabad
    DevOps Course in Hyderabad

    ReplyDelete
  16. This comment has been removed by the author.

    ReplyDelete
  17. If you're looking to begin your data science journey, enroll in AI Patasala Data Science Course in Hyderabad now. Take a look at the wide range of AI Patasala's courses to start your career.

    ReplyDelete
  18. Really awesome article. Nice information. Informative and knowledgeable. Thanks for sharing this article with us. Keep sharing more.
    Data Science Course in Hyderabad

    ReplyDelete
  19. If you want to download your favorite YouTube videos, you can use a downloader. convert youtube videos

    ReplyDelete
  20. Great idea man thanks keep it up all the time. I am very happy to see your standard. That's really awesome blog because i found there lot of valuable Information. best case convert tool online

    ReplyDelete
  21. Thanks for such a great post and the review, I am totally impressed! Keep stuff like this coming.
    full stack web development course in malaysia

    ReplyDelete
  22. Welcome To Techbrothersit: Ssis -How To Convert Excel File To Csv ( Comma Separated Values) File In Ssis Package >>>>> Download Now

    >>>>> Download Full

    Welcome To Techbrothersit: Ssis -How To Convert Excel File To Csv ( Comma Separated Values) File In Ssis Package >>>>> Download LINK

    >>>>> Download Now

    Welcome To Techbrothersit: Ssis -How To Convert Excel File To Csv ( Comma Separated Values) File In Ssis Package >>>>> Download Full

    >>>>> Download LINK YZ

    ReplyDelete
  23. Welcome To Techbrothersit: Ssis -How To Convert Excel File To Csv ( Comma Separated Values) File In Ssis Package >>>>> Download Now

    >>>>> Download Full

    Welcome To Techbrothersit: Ssis -How To Convert Excel File To Csv ( Comma Separated Values) File In Ssis Package >>>>> Download LINK

    >>>>> Download Now

    Welcome To Techbrothersit: Ssis -How To Convert Excel File To Csv ( Comma Separated Values) File In Ssis Package >>>>> Download Full

    >>>>> Download LINK wW

    ReplyDelete
  24. Usually, I never comment on post but your post is so convincing that I never stop myself to say something about Online Free Ico Converter. You’re doing a great job Man, Keep it up.

    ReplyDelete
  25. Hey All, Can you please help me to convert multiple xls to CSV files.

    ReplyDelete