I have over 100 child packages, each designed to load a specific flat file into its own table due to differences in the structures of the data. I am trying to call each child package from the parent package by pointing the latter to the former in the File System using Execute Package Task that sits in a Foreach Loop Container. The enumerator type selected in the Collection tab is "Foreach From Variable Enumerator". I have an Excecute SQL Task (just before the Execute Package Task) whose Parameter Mapping is populated by a table variable name (vTempTableName - see below). The SQLStatement property of the Execute SQL Task has a stored procedure which truncates the staging table. The stored procedure expects the table variable name to be passed to it as a parameter and this seems to work fine. Outside the Foreach Loop Container is a Script Task that gets a list of data files, which the former loops through.
The parent package has, among others, the following variables:
NAME SCOPE DATATYPE VALUE
DataFileList Parent Object System.Object
DataFilePath Parent String
PkgName Parent String E:\GEH_SSIS\MR5\MR5
PKName Parent String
vTableName Parent String
vTempTableName Parent String
The variable settings in the child package are as follows:
NAME SCOPE DATATYPE VALUE
FileName Child String
PkgName Child String E:\GEH_SSIS\MR5\MR5
PKName Child String ADMDC_REFNO
vTableName Child String S_ADM_DECISIONS
vTempTableName Child String TMP_ADM_DECISIONS
I have added a parent package variable package configuration in the child package to pass the parent variable (DataFilePath) value into the child variable (FileName). Inside the Foreach Loop Container, just after the Execute Package Task, is another Execute SQL Task whose Parameter Mapping is configured as below:
VARIABLE NAME DIRECTION DATATYPE PARAMETER NAME PARAMETER SIZE
User::vTableName Input Varchar 0 -1
User::vTempTableName Input Varchar 1 -1
User:
KName Input Varchar 2 -1
The Execute SQL Task has a stored procedure to which three parameters must be passed (EXEC usp_ManageCDC_Test ?,?,?).
Create Procedure [dbo].[usp_ManageCDC_Test]
@TableName Varchar(100),
@TempTableName Varchar(100),
@PrimayColName Varchar(100)
As
Set @SQL = '
DELETE ' + @TableName + '
WHERE EXISTS (SELECT TMP.' + @PrimayColName + '
FROM
' + @TempTableName + ' TMP
WHERE ' + @TableName + '.' + @PrimayColName + '=TMP.' + @PrimayColName + ')
INSERT INTO ' + @TableName + '
SELECT *
FROM ' + @TempTableName + '' [/color green]
Upon execution, the parent package calls the child package and loads the data into the staging table (TMP_ADM_DECISIONS). My problem is even though it completes successfully, it does not transfer the data from the staging table to the storage table .(S_ADM_DECISIONS). When I insert this script ("EXEC usp_ManageCDC_Test " + @[User::vTableName] + "?," + @[User::vTempTableName] + "?," + @[User:KName] + "?") [/color green] in the expression property - SQLStatementSource, I get the same results.
When I move the values of table variables from the child package and place them in the parent package, it runs and transfers the data from the staging to the storage table successfully. However, this is not how I want it to work as it would mean creating different variables for each table in the parent package. I want to use the same table variable names (created in the child package) for each flat file even though they are being loaded into different tables.
Assuming the above stored procedure (usp_ManageCDC_Test) was working as expected, i.e transfering data to the storage table, I would know whether the next child package would be called or not. As things stand, I can't say for sure that the next package would be called since the package completes without any errors (and/or wihout calling the next child package).
I have tried adding a child package variable package configuration in the parent package to pass the child variable values into the parent variable but it has not worked either. I want the parent package to call each child package and load every flat file type one at a time. I have got two queries:
1) How do you get the values of the table variables from the child package into the parent package so that data in the staging table can be copied into the storage table?
2) Assuming the parent package is working (in its current state) simply because the Connection Manager I have named "CALL_CHILD" is pointing to the child package (CHILD_ADM_DECISIONS.dtsx), how do you configure the package to call other child packages?
Thank you so much for your help.
JCAD1
The parent package has, among others, the following variables:
NAME SCOPE DATATYPE VALUE
DataFileList Parent Object System.Object
DataFilePath Parent String
PkgName Parent String E:\GEH_SSIS\MR5\MR5
PKName Parent String
vTableName Parent String
vTempTableName Parent String
The variable settings in the child package are as follows:
NAME SCOPE DATATYPE VALUE
FileName Child String
PkgName Child String E:\GEH_SSIS\MR5\MR5
PKName Child String ADMDC_REFNO
vTableName Child String S_ADM_DECISIONS
vTempTableName Child String TMP_ADM_DECISIONS
I have added a parent package variable package configuration in the child package to pass the parent variable (DataFilePath) value into the child variable (FileName). Inside the Foreach Loop Container, just after the Execute Package Task, is another Execute SQL Task whose Parameter Mapping is configured as below:
VARIABLE NAME DIRECTION DATATYPE PARAMETER NAME PARAMETER SIZE
User::vTableName Input Varchar 0 -1
User::vTempTableName Input Varchar 1 -1
User:
KName Input Varchar 2 -1 The Execute SQL Task has a stored procedure to which three parameters must be passed (EXEC usp_ManageCDC_Test ?,?,?).
Create Procedure [dbo].[usp_ManageCDC_Test]
@TableName Varchar(100),
@TempTableName Varchar(100),
@PrimayColName Varchar(100)
As
Set @SQL = '
DELETE ' + @TableName + '
WHERE EXISTS (SELECT TMP.' + @PrimayColName + '
FROM
' + @TempTableName + ' TMP
WHERE ' + @TableName + '.' + @PrimayColName + '=TMP.' + @PrimayColName + ')
INSERT INTO ' + @TableName + '
SELECT *
FROM ' + @TempTableName + '' [/color green]
Upon execution, the parent package calls the child package and loads the data into the staging table (TMP_ADM_DECISIONS). My problem is even though it completes successfully, it does not transfer the data from the staging table to the storage table .(S_ADM_DECISIONS). When I insert this script ("EXEC usp_ManageCDC_Test " + @[User::vTableName] + "?," + @[User::vTempTableName] + "?," + @[User:
KName] + "?") [/color green] in the expression property - SQLStatementSource, I get the same results. When I move the values of table variables from the child package and place them in the parent package, it runs and transfers the data from the staging to the storage table successfully. However, this is not how I want it to work as it would mean creating different variables for each table in the parent package. I want to use the same table variable names (created in the child package) for each flat file even though they are being loaded into different tables.
Assuming the above stored procedure (usp_ManageCDC_Test) was working as expected, i.e transfering data to the storage table, I would know whether the next child package would be called or not. As things stand, I can't say for sure that the next package would be called since the package completes without any errors (and/or wihout calling the next child package).
I have tried adding a child package variable package configuration in the parent package to pass the child variable values into the parent variable but it has not worked either. I want the parent package to call each child package and load every flat file type one at a time. I have got two queries:
1) How do you get the values of the table variables from the child package into the parent package so that data in the staging table can be copied into the storage table?
2) Assuming the parent package is working (in its current state) simply because the Connection Manager I have named "CALL_CHILD" is pointing to the child package (CHILD_ADM_DECISIONS.dtsx), how do you configure the package to call other child packages?
Thank you so much for your help.
JCAD1