SSIS:passing a value from package A to package B

Parent Package (A) and Child Package (B)

USE AdventureWorks
GO
CREATE TABLE tableName
(
Tid INT NULL,
TName VARCHAR(50) NULL
)
INSERT tableName (Tid,TName)
VALUES (100,'Kim');

Create a Integration Service Solution with two projects: P_SQL.dtsx and C_SQL.dtsx.

Double_click P_SQL.dtsx;

Drag "Execute SQL Task" and "Execute Package Task" onto Control Flow pane;

SSIS menu--Variables--Add a new variable: varName with INT32 type

Double_Click "Execute SQL Task"
In General screen, three inputs are expected:

SQL Statement: SELECT Tid FROM dbo.tableName WHERE TName='Kim';

OLE DB connection: QUEEN.AdventureWorks

ResultSet: Single Row

When the SQL statement returns a single row, the column of ResultSet can be assigned to a package variable.

Double_click "Execute Package Task": call C_SQL.dtsx package

Save the P_SQL.dtsx package.

Double_click C_SQL.dtsx package;

Drag "Execute SQL Task" onto Control Flow pane;

Create a same variable as the one in P_SQL.dtsx package: varID with INT32 type

Double_click "Execute SQL Task";

When OLEDB connectionType is chosen, you must use ? as the parameter placeholder.

SELECT * INTO test5 FROM HumanResources.Employee Where EmployeeID>?

Mapping the variable:varID to the parameter in the query

OLEDB connection type uses ordinal number (0,1,2,3,...) to identify the parameters. The query has one parameter whose ordinal number is 0.

Parameter Mapping:
INPUT: passing the value of a variable onto a query parameter
OUTPUT:assigning the value from the query parameter to a variable



C_SQL.dtsx package configuration; make sure you choose the Parent Package Variable


Save the S_SQL.dtsx package;


Double Click P_SQL.dtsx

Execute the package

You will find a Test5 table created and populated with data whose EmployeeID number is greater than 100.

Observation:
Don't define a table parameter. I tried the following: SELECT * FROM ?

The variable and the parameter cannot be mapped.

======
FROM book online:

The Execute Package task can run packages stored in the SQL Server msdb database and packages stored in the file system. The task uses an OLE DB connection manager to connect to SQL Server and a File connection manager to access the file system.

The Execute Package task can also run a database maintenance plan, which lets you manage both SSIS packages and database maintenance plans in the same Integration Services solution. A database maintenance plan is similar to an SSIS package, but a plan can include only database maintenance tasks, and it is always stored in msdb.

A child package requires access to data that is dynamically derived by the parent package. For example, the parent package extracts data from a table and loads the rowset into a variable, and the child package performs additional operations on the data.