Calling Child Packages in SSIS

re: Calling Child Packages in SSIS

Price — Fri, 29 Oct 2010 19:55:01 GMT

Another way of referencing a variable in a child package is to simply use the variable name used in the parent without the User:: / System:: Prefix. I'm not quite sure if that's a supported scenario since I rarely see people mention it.

re: Calling Child Packages in SSIS

Jimmy — Fri, 29 Oct 2010 22:13:16 GMT

The challenge for me has been the fact that we do our development on the file system but our deployment is to the package store using msdb. The attributes are different in calling child packages depending on where the package source is and there is not a good way to dynamically set it.

re: Calling Child Packages in SSIS

paulhutagalung — Sun, 31 Oct 2010 16:03:58 GMT

>The challenge for me has been the fact that we do our development on the file system but our deployment is to the package store using msdb. The attributes are different in calling child packages depending on where the package source is and there is not a good way to dynamically set it.

Just use expression for that. and if you need to run by designer it can automatically switch to file system

re: Calling Child Packages in SSIS

Joao Silva — Wed, 03 Nov 2010 15:42:02 GMT

Eric, i have tried do use the calling chil packages as a solution for a problem but it did not work. My problem is to use OLE DB SOURCE dynamically with different tables in the same job. I have created a variable in data acess mode which contain the name of table to be used. In my loop when i went for the second table the job abends. Any idea to solve this? tks Joao Silva

re: Calling Child Packages in SSIS

ejohnson2010 — Wed, 03 Nov 2010 16:19:10 GMT

Joao: The problem is likely caused by the schema of the tables being different. Your OLE DB data source has its schema defined at design time and when you dynamically change tables at run time, you will get a schema mismatch. Without knowing your specific details, it is hard to make a recommendation, but you might have to do some processing in t-sql to move data to a staging table and then use SSIS to process from the staging table. That way the schema is a known element.

re: Calling Child Packages in SSIS

Joao Silva — Wed, 03 Nov 2010 16:59:29 GMT

Eric, first of all thank you for your answer. I am starting now in SSIS tasks and your help is really usefull :)

I have created a loop which the driver is a table where each line has 2 columns: TABLE_NAME (contains each table name) and SQL_COMMAND where i have the sql instruction: "SELECT * FROM TXXXX" XXXX - is the name of the table.

example DRIVER TABLE:

TABLE_NAME SQL_COMMAND

TW4A0 SELECT * FROM TW4A0

TW4B0 SELECT * FROM TW4B0

I call the child package passing this 2 columns:

In the child package i use OLE DB SOURCE where i want to use the variable SQL_COMMAND with the value "SELECT * FROM TXXXX"

this is just to migrate to another database thru OLE DB destination where i use the value of column TABLE_NAME to load data.

tks

João Silva

re: Calling Child Packages in SSIS

Mark Zatx — Mon, 19 Sep 2011 03:51:41 GMT

Just have to say many thanks for this. Very clear and concise instructions.