Post reply

parent pkg executing child pkg

  • June 10, 2008 at 7:13 pm

    #122594

    I am trying to write an application that receives flat files in various formats, determines the likely format of each file, and calls the appropriate child package to load the file to a staging table.

    It is already working in Informatica but my company has decided to switch to SSIS so I need to figure out this complex logic in a brand new tool.

    Is there a way to call the selected child package when I do not know which one it will be ahead of time? For instance, can the Execute Package task use a variable for the package name? If not, is there another approach?

    In case it helps, the approach in Informatica is to build a flat file with one row per file to be processed. The row contains a key for the file and the name of the workflow to use. This is passed to a DOS batch script which goes through the file and runs each workflow one by one, passing the key of the file as a parameter (Informatica for variable).

    Any help will be appreciated!

    Thanks!

  • June 10, 2008 at 7:16 pm

    #827029

    I think this may do what you want.

    http://www.mssqltips.com/tip.asp?tip=1487

  • June 10, 2008 at 10:00 pm

    #827048

    Thanks. It looks like this requires an execute package task for each possible child package. There could easily be a hundred different file formats so I was hoping for something a little more compact.

    We receive rebate claim data from pharmacy benefit managers (pbms) and process it for the drug manufacturers.

    About 75% of the pbms use some variant of a standard layout -- but then there are the rest of them who make up their own formats. We have to handle whatever they throw at us. It makes for an interesting job!

  • June 10, 2008 at 10:10 pm

    #827053

    Have a look at the "Expressions" property of the execute package task. You should be able to supply the name of the package to call at runtime in a similar way to your informatica solution. The equivalent in SSIS would be to

    - determine the name of the package to run and store the name in a variable

    - in the execute package task, assign the package name using the "Expressions" property and the variable you just set

    You may need to wrap this up in FOREACH container to handle looping through a set of files.

  • June 10, 2008 at 10:24 pm

    #827059

    Thanks! I will try that out tomorrow.

Viewing 5 posts - 1 through 4 (of 4 total)

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