For Each Loop Container

  • Hi All,

    I have a requirement,

    I have 2 different types of files(.txt and .csv) in a folder , I need to extract them, .csv files data need to sent to one database and .txt files should sent to another second database.

    Can any body help me out this ....

    Regards

    Chetan

  • are the file formats the same?

    because with two different files going to two different databases - why don't you use two seperate data flows instead of a loop?

  • Ok - have two forEach Loops one looking for .csv and one looking for .txt files (it's part of the ForEach loop config). That's it, process accordingly using the two connectinos for each database. This also means if configured to do so, both will run in parallel with the associated performance improvement

    hth

    Andrew

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

  • Andrew above has the best 'simple' method. An alternative would be, when this gets more complex, to include a script object and sequence containers that you enable/disable as necessary according to the package variables.

    This can get out of hand very quickly, but I've seen it work very well for massive SSIS packages when (and ONLY when) your SSIS packages are considered production maintenance tasks and not development tasks with full lifecycle.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Ah - in that case - what I've done in the past is to have the script object parse the filename - set a value in a variable and then use conditional branching to do the transformations - eg where you have files with the same extension, different formats though, but with a definitive naming convention meaning you know which file format relates to which file name pattern.

    Class sig BTW - mind if I use it in interviews? 🙂

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

  • andrew gothard (10/29/2010)


    Ah - in that case - what I've done in the past is to have the script object parse the filename - set a value in a variable and then use conditional branching to do the transformations - eg where you have files with the same extension, different formats though, but with a definitive naming convention meaning you know which file format relates to which file name pattern.

    Conditional branching though doesn't give you enough options, just pass or fail. If you're dealing with say, 30 different clients all dumping different formats into the same ftp (the pain, THE PAIN....) you need a better option. We found that disabling the different components inside the foreach loop worked well. You disable everything, then enable only the one you want, let it flow, and when it's done it hits the starter script again and repeats for the next file. 🙂

    Class sig BTW - mind if I use it in interviews? 🙂

    Be my guest. Not entirely sure how you intend that to be of use in an interview, but may it bring you fortune. 🙂


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Craig Farrell (10/29/2010)


    andrew gothard (10/29/2010)


    Ah - in that case - what I've done in the past is to have the script object parse the filename - set a value in a variable and then use conditional branching to do the transformations - eg where you have files with the same extension, different formats though, but with a definitive naming convention meaning you know which file format relates to which file name pattern.

    Conditional branching though doesn't give you enough options, just pass or fail. If you're dealing with say, 30 different clients all dumping different formats into the same ftp (the pain, THE PAIN....) you need a better option. We found that disabling the different components inside the foreach loop worked well. You disable everything, then enable only the one you want, let it flow, and when it's done it hits the starter script again and repeats for the next file. 🙂

    Sort of similar problem - what you can do as well as success or fail on a branch is to have a condition too, for example based on a variable. So, set up your script task to look at the filename (for example). If it starts with 'A' then it's an A Company file, assign "Company A" to the CompanyName variable in your script component (or company B etc ... according to appropriate criteria), then your branching is set up to the transformations based upon a constraint based both upon the variable value AND success. Trust me, it works. I'm not saying it's better than in your circumstances - but it worked for me in mine. Going to be playing with your idea though

    Class sig BTW - mind if I use it in interviews? 🙂

    Be my guest. Not entirely sure how you intend that to be of use in an interview, but may it bring you fortune. 🙂

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

  • Class sig BTW - mind if I use it in interviews? 🙂

    Be my guest. Not entirely sure how you intend that to be of use in an interview, but may it bring you fortune. 🙂

    If they don't laugh, it's time to run like the wind! :w00t:

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

Viewing 8 posts - 1 through 7 (of 7 total)

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