Passing values from Parent to Child package in SSIS

~Variables

Variables: declared and "Expression" are used to take on runtime all values.

Variables: GetDB of System.Object Type

~Execute Sql Task

Execute Sql Task: Sql to get from sys databases online databases.

Resultset:

Result Name Variable Name

0 User:GetDB

Foreach Loop:

Enumerator:Foreach ADO Enumerator

ADO object source variable:User::GetDB

Variable Mapping:

Variable Index

User:PassDB 0

Execute SQL query:

Declare @TableName SYSNAME

Declare @Database SYSNAME

Declare @SqlString NVARCHAR(2000)

SET @TableName=?

SET @Database=?

SET @SqlString = 'SELECT @OutRowCount=COUNT(*) FROM '+@Database=' sys.tables where name=" +@TableName='"

EXEC sp_executed @SqlString,N'@OutRowCount INT OUTPUT

,@OutRowCount=? OUTPUT

Parameter Mapping:

Variable Name Direction Data Type Parameter Parameter

User::TableName Input VARCHAR 0 -1

User:ParentDB Input VARCHAR 1 -1

UserFlag Output LONG 2 -1

Execute Package Task Editor:

Package

Location:File System

Connection:Child.dtsx

Password:

ExecuteOutOfProcess:false

----Child.dtsx

When you want to pass a variable vaue from parent package.

1.In Child package create a variable say C_DBName

2.Right click-choose "Package Configuration" , in the drop down list box choose "Parent Package"

write exact name as it is case sensitive:ServerName

say map: Table Name: Variable: C_DBNAME

so now the child package has the "Server Name" from parent package.

Exploring