none
File loop dilema

    Question

  • Hello,

    I have an SSIS package that is importing a series of files using a for each loop. I am trying to create a table on the fly for each text file. I know this can be done using the import/export wizard, but can this be done in SSIS? If so, how can I include that logic within my looping structure. Any help would be appreciated.

    Thanks,

    Dave

    Dave SQL Developer
    Tuesday, January 05, 2010 2:23 PM

Answers

  • Why not use Import/Export wizard?
    Im not aware of writing stored proc which will work the way you want.


    Hope this helps !!
    Sudeep   |    My Blog
    Tuesday, January 05, 2010 2:47 PM

All replies

  • If all files is in one folder and column names are same than you can use for each loop .By doing this data of all files would be transfer to one table in SQL sever through SSIS .If all files contain column names than you do not need to create table in SQL server .Import/export wizard is use to transfer all tables of particular database into new database or other exixting database ,here do not need.
    Tuesday, January 05, 2010 2:32 PM
  • You mentioned ....... on the fly ......
    can you give an example
    do you mean "as you go" defining the source and destination tables and/or files , dynamic fields ?

    please provide an example


    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post(s)
    Tuesday, January 05, 2010 2:33 PM
  • I dont think you have the same metadata in each file.
    And I can say for sure SSIS will not come in handy in ur case.
    You will have to use a Script task inside the For Each loop which reads the 1st record for the column names and may be table name from the file name(my assumption).
    Then insert each row.

    I dont think this is a very elegant approach.
    Hope this helps !!
    Sudeep   |    My Blog
    Tuesday, January 05, 2010 2:37 PM
  • Sudeep is correct here.
    Just want to know if the metadata for all the tables is same or different.
    Nitesh Rai- Please mark the post as answered if it answers your question
    Tuesday, January 05, 2010 2:41 PM
  • i agree with you Sudeep , if thats his case,
    he can use .NET code to make a package but will lead him to a bigger problems and issues.
    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post(s)
    Tuesday, January 05, 2010 2:42 PM
  • Thank you all for your quick response. What I mean by "On the fly" is that I want a new table generated for each file. The Import/Export wizard has this capability, (just not as a looping structure).The main issue that I have is that each file has a different table schema. I am trying to avoid writing a parser script if I can. If what I am asking for is not possible, could someone point me in the right direction on how to write a stored procedure that parses one row with the column delimiter ';'? 

    Thank you all again,

    Dave

    Dave SQL Developer
    Tuesday, January 05, 2010 2:45 PM
  • Why not use Import/Export wizard?
    Im not aware of writing stored proc which will work the way you want.


    Hope this helps !!
    Sudeep   |    My Blog
    Tuesday, January 05, 2010 2:47 PM
  • Sundeep,

    I was using the import/export wizard as an example of what I needed to accomplish. Can you use the import/export wizard within a VSS project structure?

    Dave

    Dave SQL Developer
    Tuesday, January 05, 2010 2:50 PM
  • Import Export implicitly creates a ssis package(.dtsx file)
    this can be stored in VSS.
    Hope this helps !!
    Sudeep   |    My Blog
    Tuesday, January 05, 2010 2:56 PM
  • so basically you have few files that needs to insert into muliple tables
    e.g
    Salesfiles.xls -> tblSales
    Purchasingfiles.xls -> tblPurchasing
    AccountingFile -> tblAccounting
    and so on

    questions
    1- will you file have a prefix or post fix name that can define the type of the file (e.g Sales2010-01-01.xls   Purchasing 2010-01-01.xls  , etc....)??
    2- will each group of files (Sales, Purchasing , etc...) have a fixed filed names?

    if all the answers are Yes you can make one or multi packages that LOOPS through a folder(s) that filters (Sales*.xls , Purchasing*.xls) by the file type (the filter is in the loop) and in that loop you will have one DFT related to that file (e.g sales only)

    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post(s)
    Tuesday, January 05, 2010 3:12 PM