none
importing multiple csv files to excel sheets

    Question

  •  

    Hi,

     

    I am trying to import multiple .csv files to excel sheets using Script task in SSIS.

    I have trouble importing the reference that allows us to read and write to excel sheets. Can anyone help me create a script task that will import multiple .csv files to excel sheets.

    Monday, December 31, 2007 2:53 PM

All replies

  • Since the Script task in SSIS only allows .NET references, and the Excel object model is COM-based, this will probably involve developing your import functionality in a custom .NET DLL assembly (which in turn uses the Excel COM libraries) and then referencing that assembly from the Script task. There may be other ways to do it, but this is the one I've seen referenced most often before.

     

    Just as a sanity check - why are you trying to do this in the Script task? If you're using SSIS, this seems like a job for the Data Flow task, and if you're going to use code, it doesn't seem like SSIS is adding much value. There could well be some part of your project that I'm missing (of course) but it's better to ask such questions sooner rather than later...

    Monday, December 31, 2007 3:01 PM
    Moderator
  • Thank you for your reply. Right now am importing 3 csv files into 3 seperate excel sheets of one workbook. At a later stage we might get 4 or 5 csv files. So at tht point of time we dont want to open the packge and create dataflows for each csv file and import them into excel sheets. We want to use a for each loop that checks for all cvs files and imports them into seperate excel sheets using some .net code.

    Monday, December 31, 2007 3:39 PM
  •  dsmathew wrote:

    Thank you for your reply. Right now am importing 3 csv files into 3 seperate excel sheets of one workbook. At a later stage we might get 4 or 5 csv files. So at tht point of time we dont want to open the packge and create dataflows for each csv file and import them into excel sheets. We want to use a for each loop that checks for all cvs files and imports them into seperate excel sheets using some .net code.



    Can you use SSIS's ForEach container to loop over the collection of workbooks?

    -Jamie
    Monday, December 31, 2007 3:58 PM
    Moderator
  • I think we can. I never tried doing it though.

    Monday, December 31, 2007 4:36 PM
  •  dsmathew wrote:

    I think we can. I never tried doing it though.

     

    Please do, and please let us know what you find. This is almost certainly going to be a cleaner and more maintainable solution than using the Script task.

     

    Monday, December 31, 2007 5:55 PM
    Moderator
  • I need to loop through multiple text files and load them into seperate excel sheets. i will try doing it and let you guys know.

    Monday, December 31, 2007 6:39 PM
  •  dsmathew wrote:

    I need to loop through multiple text files and load them into seperate excel sheets. i will try doing it and let you guys know.



    Just a  clarification, having a ForEach loop container and a single dataflow will work ONLY if the .csv files and the Excel Sheets have identical structure.


    Tuesday, January 01, 2008 3:19 PM
    Moderator
  •  Rafael Salas wrote:
     dsmathew wrote:

    I need to loop through multiple text files and load them into seperate excel sheets. i will try doing it and let you guys know.



    Just a  clarification, having a ForEach loop container and a single dataflow will work ONLY if the .csv files and the Excel Sheets have identical structure.




    Indeed, although that doesn't mean that For each loop isn't an option...it just means that you'll need a seperate data flow for each type of csv file.

    -Jamie
    Tuesday, January 01, 2008 3:43 PM
    Moderator
  • yes. thats what I developed initially. but they dont want it that way. irrespective of the file formats they want excel workbook to create excel sheets. each cvs file needs to get imported to seperate excel sheets.

    Wednesday, January 02, 2008 1:12 PM
  • hey were you able to get this working?  i am trying to do a similar thing and would very much like to see your code if you are able to do that.  one issue is that i cant create the workbook object because i cant import the COM references in VSA.  anyway, any help is appreciated.  thanks

    adam
    Tuesday, March 04, 2008 8:24 PM

  • Myemail,


    Why are you looking into witing custom code for this? Have you looked at the for each loop container and dataflows?

    See an example here:

    http://rafael-salas.blogspot.com/2008/03/ssis-and-dynamic-excel-destinations_01.html
    Wednesday, March 05, 2008 3:28 AM
    Moderator

  • Myemail,


    Why are you looking into witing custom code for this? Have you looked at the for each loop container and dataflows?

    See an example here:

    http://rafael-salas.blogspot.com/2008/03/ssis-and-dynamic-excel-destinations_01.html


    Wednesday, March 05, 2008 3:29 AM
    Moderator
  •  dsmathew wrote:

     

    Hi,

     

    I am trying to import multiple .csv files to excel sheets using Script task in SSIS.

    I have trouble importing the reference that allows us to read and write to excel sheets. Can anyone help me create a script task that will import multiple .csv files to excel sheets.



    the microsoft jet ole db provider can be implemented with ado.net to read/write data to/from excel.  using the foreach container, you can loop through multiple .csv files and import them into excel via the script task.

    hth.
    Wednesday, March 05, 2008 9:38 AM
    Moderator
  •  

    Hi, I could not get this working. I tried doing script task but as it does not provide drivers to connect to Excel I dropped that idea as well. We had to drop the idea.
    Wednesday, March 05, 2008 8:30 PM