Importing flat files

  • How can i import different flat files to different tables using single flat file source and single oledb destination

    like can'nt we assign the dynamically flat file and based on the flat file which is assigned changes the destination.

    my requirement is like when run the package i am initially ftp task is retriving the all the flat files from ftp server folder to my local folder then i have to run the foreach loop for processing the files which are imported to my local folder

    so successfully retriving the files from server then i placed a foreach loop in that i placed dataflowtask

    in dataflowtask i taken flatfile source and script task and oledb destination

    so using filename variable i am assigning the file name to flat file source and then in script i am writing code like if filename variable contains text which is fixed for each type of file then i am assigning the table name to the variable

    then in the oledb destination i am assiging the table name using tablename variable.

    but unable to do this

    i am getting errors

    is this process is correct can you please tell me any one

    i am bothering withis from 4 days no one is replying me

  • I guess no one replying to you due to the lack of details in your question.

    Sample of existing script? Error details?

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Are all of these flat files and tables identical? Because the Data Flow contains meta data about the external sources and destinations. You cannot loop through files and tables that have different layouts by using the same Data Flow.

    If they are identical, then you should be able to use variables and expressions to change the source and destination connection strings.

  • I am facing with the same problem...And I think we cannot do this dynamically...Can someone please confiem this..

  • all these flat files are different from each other and destination tables are also different for every flat file

    so now i got some idea like looping through the all the files in the directory i can have the filename so by this how can i assign the destination table dynamically is it possible by script task.

    like after getting all the files into a variable in script looping through the each value in the variable

    example

    foreach filename in strfiles

    if filename.contains("condo") then

    some variable = "tableName"

    and assigning this variable dybamically to the oledb destination

    please replay on this

  • To be definite on the subject:

    you can only use the dataflow (flat file source and OLE DB Destination) dynamically if (and only if):

    * the flat files have the same identical structure

    * the destination table is always the same (maybe you can use different tables if they have the same identical structure, but I'm not sure about that)

    If this is not the case, you're stuck with scripting.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • sridharparshi (6/28/2010)


    all these flat files are different from each other and destination tables are also different for every flat file

    so now i got some idea like looping through the all the files in the directory i can have the filename so by this how can i assign the destination table dynamically is it possible by script task.

    like after getting all the files into a variable in script looping through the each value in the variable

    example

    foreach filename in strfiles

    if filename.contains("condo") then

    some variable = "tableName"

    and assigning this variable dybamically to the oledb destination

    please replay on this

    If you can use third-party solutions, check the commercial CozyRoc Data Flow Task Plus. It is an extension of the standard Data Flow Task and includes support of completely dynamic data flows at runtime. You can process a list of flat-files with different layouts using only one data flow. Also setting up dynamic data flows doesn't require programming.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • this is very helpful and good one

    but i should not use the third party tools in my work i have the restriction.

    but i am very thankful to you for your great link 🙂

  • all the files are .txt extension but different structure and they should be import to different tables.

    sorry for my late response i out of the office for two days.

  • OK then, a few more questions:

    - Do the source files always have the same name?

    - Are there always the same number of source files with unique formats?

    - Are the destination tables always the same for each file format?

  • ****Plese note the solution hereby is partly tested...It should work*****

    Hi,

    This is possible but will require certain measures are required which are to be considered:

    1. The source file name is changing...One variable to store current path name

    2. The destination SQL Table name is changing....One Variable to keep a track of DestnationTableName

    Now using a ForEachLoop container in File Enumerator mode one may traverse the folder where the source files are placed. using the FileNameRetrieval attribute we will get the names of every file in the source folder in a package variable...

    Now for the later part...we will us BIT(Bulk Insert Task)

    Now create another variable where we would store names of all tables in the destination server database.

    using the DestinationTableName attribute we will assign the new table name on each run of the for each loop

    This should work...I'll test the later part...will let you know probably tomorrow...thanks anyways it's been time since i've encountered a chiller...:-P:-P:-P

    Hope this helps

    Raunak J

  • Hi Raunak,

    Thanks for you r replay

    i got u r point but where should i place the flatfile source and how can i give the table name to variable

    please respond to this.:-)

  • Ok... I've been watching this one for a bit and I'm curious... so let me ask a couple of questions, please.

    1. It's my understanding that if a file name contains a particular "word" such as "condo", then that file should be imported into a particular table having to do with the word "condo".

    Is THAT true?

    2. If 1 above is true, does that mean that [font="Arial Black"]all [/font]files having the word "condo" in it all have the exact same file structure?

    3. If 1 and 2 above are true, how many different "words" in file names (and, thus, file types) are we talking about?

    4. If 1 and 2 above are true, does this have to be an SSIS solution or can we use a little T-SQL magic?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 13 posts - 1 through 12 (of 12 total)

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