Post reply

dynamic import of multiple flat files

  • May 23, 2013 at 4:31 am

    #394836

    hi need your help regarding dynamically importing multiple flat files into different table of sql server database.

    the format of files are different for different files. its like importing 25 different files from a folder to 25 different tables of the database.

    thanks in advance

  • May 23, 2013 at 4:58 pm

    #1618059

    If you have 25 different file formats going into 25 different tables with different schemas then you will need 25 different Flat File Connection Managers, and at minimum one Data Flow with 25 different Flast File Source Components going to 25 different Database Destinations (e.g. OLE DB Destinations).

    If you want a fully dynamic solution you could look into writing .NET code in a Script Task or Script Component.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • May 24, 2013 at 2:16 am

    #1618126

    thanks for the revert however i know the procedure informed but to get this dynamically i assume there is procedure of format files with xml format and then script to call variables defined.

    my issue is i am not good at scripting and completely aware about this procedure so stuckd

  • May 24, 2013 at 4:33 am

    #1618164

    I am sorry that you are not a strong programmer but if you want to do this dynamically it will require a level beyond basic skills so you can implement some custom coding. This can be done in .NET using a Script Task in SSIS, or you could switch gears and use something like PowerShell along with SQL Server command-line tools like sqlcmd.exe and bcp.exe.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • May 26, 2013 at 11:52 pm

    #1618677

    yes now you got it the bcp.exe process , can you help with that procedure of creating XML format file , assign variable to import, change format of imported files .

  • May 27, 2013 at 7:22 am

    #1618785

    ...wrong post...

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • May 27, 2013 at 7:44 am

    #1618790

    What is the difference from your perspective of creating 25 XML format files and creating 25 Source-to-Destination pairs in an SSIS Data Flow? Just curious.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • May 28, 2013 at 1:19 am

    #1618870

    ok you help me out which is better way out to solve this issue of 25 different format files to be imported into 25 different tablesof s1l database dynamically,

  • May 28, 2013 at 9:20 am

    #1619013

    Somewhere in your process you need to define the format of your files, I.e. to specify the metadata of your files. You can do this in SSIS with Flat File Connection Managers, or you could do this with bcp format files, or you could go a different route to store the metadata some other way. If you go with SSIS there will be no custom coding. If you go with bcp there will be some time spent creating the format files. If you go a different route then you're likely in for some custom coding.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • May 30, 2013 at 12:42 pm

    #1619819

    thanks for the options but what if in future the number of files increase

    with increase of files the data flow tasks would look messy with individual source -destination

    then again we create connection managers for new ones so isnt the BCP format file option good which dynamically would take new file names and add in the database without much changes .

    i am also keen to adopt the ssis task multiple file option but its a one time solution so just curious if we have any option within data flow task to dynamically import multiple files into different tables

    please advise

  • May 30, 2013 at 12:44 pm

    #1619821

    lakshyanda (5/30/2013)

    thanks for the options but what if in future the number of files increase

    with increase of files the data flow tasks would look messy with individual source -destination

    then again we create connection managers for new ones so isnt the BCP format file option good which dynamically would take new file names and add in the database without much changes .

    i am also keen to adopt the ssis task multiple file option but its a one time solution so just curious if we have any option within data flow task to dynamically import multiple files into different tables

    please advise

    If you add more files you will need to create a new bcp format file, no?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • May 30, 2013 at 12:51 pm

    #1619827

    yes but thats just a command line where in if we go by Data flow task then there would n number of files with source -destination linking and wont it be messy

  • May 30, 2013 at 1:15 pm

    #1619845

    A format file is more than just a command line. Its a separate file you have to Create manually.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • May 30, 2013 at 1:49 pm

    #1619873

    As I said earlier, if you want to do this dynamically in SSIS it will require a level beyond basic skills. This can be done in .NET using a Script Task or Script Component, or by using SQL Server command-line tools like sqlcmd.exe and bcp.exe along with the Execute Process Task in SSIS where the command line changes dynamically based on an SSIS Expression.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • May 30, 2013 at 1:54 pm

    #1619882

    can u help me with detailed procedure or any sample data if u have

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply