×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Very urgent help required

Very urgent help required

Very urgent help required

(OP)
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::PKName    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 + ''


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::PKName] + "?") 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

 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close