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

40 comments:

  1. Hey! I'd like to share this useful online file converter that just work awesome. Make sure to check out. https://onlineconvertfree.com/

    ReplyDelete
    Replies
    1. 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

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

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

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

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

    ReplyDelete
  5. 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
  6. Finished edition star x is significantly more expert than iMovie, which has XML fare and the sky is the limit from there.
    AnyConv

    ReplyDelete
  7. 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
  8. MP4 is a portable Media Player which includes FM transmitters for the transmission of information. flv to mp4

    ReplyDelete
  9. i am browsing this website dailly , and get nice facts from here all the time .

    ReplyDelete
  10. 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
  11. Thanks for the Information.Interesting stuff to read.Great Article.
    I enjoyed reading your post, very nice share.
    Data Science Course Training in Hyderabad

    ReplyDelete
  12. 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
  13. 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
  14. Many plan understudies struggle changing from understudy to representatives just due to an absence of readiness.Professional graphic design

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

    ReplyDelete
  16. keep up the good work. this is an Ossam post. This is to helpful, i have read here all post. i am impressed. thank you. this is our site please visit to know more information
    data science course in Hyderabad

    ReplyDelete
  17. Nice blog, it's so knowledgeable, informative, and good looking site. I appreciate your hard work. Good job. Thank you for this wonderful sharing with us.data science course in Hyderabad

    ReplyDelete
  18. 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
  19. 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
  20. 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
  21. Thank you for posting this great article! You did a wonderful job. Keep it up.
    Data Science Training in Hyderabad
    Data Science Course in Hyderabad

    ReplyDelete
  22. Your blog contains unique and great articles! I was impressed with the way you effectively get your point across.
    AWS Training in Hyderabad
    AWS Course in Hyderabad

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

    ReplyDelete
  24. 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
  25. Really awesome article. Nice information. Informative and knowledgeable. Thanks for sharing this article with us. Keep sharing more.
    Data Science Course in Hyderabad

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

    ReplyDelete
  27. Your work is very good and I appreciate you and hopping for some more informative posts
    data science course in malaysia

    ReplyDelete
  28. Glad to chat your blog, I seem to be forward to more reliable articles and I think we all wish to thank so many good articles, blog to share with us. data science training in mysore

    ReplyDelete
  29. 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
  30. You actually make it look so easy with your performance but I find this matter to be actually something which I think I would never comprehend. It seems too complicated and extremely broad for me. I'm looking forward for your next post, I’ll try to get the hang of it! data science course in mysore

    ReplyDelete
  31. 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
  32. Watch and Download world's famous drama series Kurulus Osman in English on link below
    👇
    Kurulus Osman in English

    📢Get high quality backlinks for your
    Website with BacklinksIndexer
    BacklinksIndexer

    Crypto trading online course
    Join on link below
    Crypto quantum leap

    📒 Read Home doctor book online
    Then you will be a doctor for your family
    Home Doctor Book

    💰Create own NFTs and earn 1000$
    Complete guide
    Create NFT

    Join online YouTube course
    And be a professional YouTuber
    Tube Mastery and Monetization by matt

    🦷Steel Bite Pro
    Best product for
    Teeth pain, cavities,teeth whitening and other oral health issues with money back guarantee
    Steel Bite Pro

    ReplyDelete
  33. A good blog always comes up with new and exciting information and while reading, I feel that this blog has all those qualities that qualify a blog to be one.
    cyber security course in malaysia

    ReplyDelete
  34. 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
  35. 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
  36. Thanks for such a great post and the review, I am totally impressed! Keep stuff like this coming.
    full stack web development course

    ReplyDelete