Dynamically choose source and destination

  • Hey Gurus,

    I have just started working with SSIS and have following task at hand and I feel lost. Can any of you please help me?

    Currently, I have a 2 different data flow tasks that do the same thing: Accept data from a SQL Store proc, put it to flat file destination and archive it. One of them is stored procedure giving us revenue data and the other gives us accounts data. Now, I need to combine both of these different data flows as a single data flow using parameters / variables for picking up appropriate stored procedure based on whether user wants to see account data or revenue data and then, out it in the correct file and file location (Files and file locations are different for revenue and accounts data).

    I do understand I should use a for-each loop maybe. However, I really need assistance in getting started with how to set up variables (system / user defined) and how can I go about it.

    I have attached a sample data flow diagram of how I need it.

    Can you please pass me some tips / ideas. Please note that am a novice is SSIS and just started.

    Thanks in advance!

  • If I'm understanding you correctly, you're running into a twofold problem. One of them you can't fix.

    First, you cannot use a source in a dataflow that will change its metadata. So, if your procs return different column names, different datatypes, etc, you can't swap them on the fly. You must have unique sources/destinations for each. You CAN adjust the source of the information, but the metadata for the information (think result schema) must stay exactly the same, and flow to the exact same locations in the target.

    Now, the next problem is the attempt to use the Loop. Because of the above assumption that the format is different, you won't be able to trap them into the loop.

    What I recommend is you use a simple script task to evaluate the options sent in as parameters to the package, and then use conditional activation (right click on the success arrow between them) and set internal package variables in the script task to activate/deactivate certain paths depending on what you require.


    - 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

  • Basically to re-iterate what Craig said, you'll need two data flows within the package and conditional execution.

    No need for a script task though. Just create a variable in the package, set its value at runtime and the precedence constraint can directly evaluate that variable.

  • Technically there is a way to do this, but it would mean using a Script task which creats the connection, executes the relevant stored proc, and manipulates the data into the Destination file, but it has draw backs in terms of troubleshooting and overheads.

    I personally would use the method that Kriag and Howard have recommended, with two data flows and a variable that dicates which part of the process flow is executed.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • Thanks a lot guys for your replies. Based on the information you provided, can you please help me / direct me of how to script using VB.Net or C# to get the following:

    a) data connections

    b) stored procedures from those data connections

    c) destination locations based on the stored procedures that were executed

    Again, thanks in advance for all your valuable time and inputs!

  • Hey Gurus,

    After scratching my head some more and based on your replies, I came up with the following approach:

    1. I want to have the following user variables:

    a) DB Connection

    Type: String

    Scope: package

    b) Business Process - Finance or sales

    Type: String

    Scope: package

    c) Stored Procedure - If @Business Process = 'Finance' then EXEC USP_Name1 else EXEC USP_Name2

    Type: String

    Scope: package

    d) Destination - If @Business Process = 'Finance' then Location1 else Location2

    Type: String

    Scope: package

    My questions are as follows:

    1. Is it possible to call one user variable inside the other? If yes, can someone please explain me the code as to how to do it?

    2. If not, then, can this be done in a script task and / or SQL Execute task? If yes, can someone please explain me the code as to how to do it?

    Thanks in advance!!

  • Just got back from vacation, Cert, but you're still overthinking this.

    First, do proc_1 and proc_2 have the same results? If not, you cannot 'switch' them in an OLEDB source. Not without third party components and I don't recommend any of them due to a lack of familiarity.

    Everything else you're trying to do stems from this. You have to create two unique data flows and use conditional pathing at the control flow level.

    You can swap the sql and stuff via expressions (object properties) but I don't recommend doing this.

    This is an ETL optimization software, not a fully customizable programming language. Some of it is simplified (or hardened, like metadata) so that it can do those optimizations without concern for incredible versatility in any particular component.


    - 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

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

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