Welcome to blogs.conchango.com

I have just been watching Joy Mundy's webcast " Loading a Kimball method data warehouse using SQL Server

In SQL Server Integration Services (SSIS) variables have scope (this was not the case in the forerunner

Addressing the blog below by Jason,

Would you please provide few steps how I can assign the variable values (Scope:Package) to the properties of Connection Managers, and Few Tasks using Package Configurations.

I couldn't find solution in clear steps even after googling and going through lots of blogs and related links for using package configurations.

Many thanks for your help...

-------------------------------------

jason said:

Hello again Jamie,

I just wanted to drop you a line and let you know I found another way to pass variables without setting in script objects, it has been staring me in the face this whole time. go to package configuration organizer -> add new package config, set the config type: "parent package variable" and whatever your parent variable is called….. tada!

Just thought I would give you that tid-bit just incase you didn't know:)

Cheers,

Jason

August 2, 2005 9:36 PM

Hi, I have a project that is using SSIS for the ETL process. Since it was installed SP2 on SQL Server 2005, the ETL process begun to work wrong. At first i don't realize what it was, then, after several tests i notice that the SSIS with SP2 does not work well when we have a Package that calls Child Packages in parallel and then those Child's Packages call Task Packages to execute a task. All of those 3 tier packages have variables with the same name, and those variables are passed from the First Package to the Child's Packages and from the Child's to the Task Packages.

What happens is that (sometimes) the Task Packages get the wrong variable (Task_1 receives the variable that is passed to Task_2)

****************************************

             Correct

****************************************

Package:                Father

variables:        A_1           B_2

Child Packages: Child_A         Child_B

variables:        A_1           B_2

Task Packages: Task_1 Task_2            

****************************************

             Incorrect (1)

****************************************

Package:                Father

variables:        A_1           B_2

Child Packages: Child_A         Child_B

variables:        A_1           A_1

Task Packages: Task_1 Task_2

****************************************

             Incorrect (2)

****************************************

Package:                Father

variables:        A_1           B_2

Child Packages: Child_A         Child_B

variables:        B_2           B_2

Task Packages: Task_1 Task_2

This situation work well in SQL Server 2005 SSIS

- RTM

- SP1

This situation work wrong in SQL Server 2005 SSIS

- SP2

Can anyone notice this situation yet?

Regards,

Telmo Teixeira

Hi all,

Here's another gotcha to look out for when using parent package configurations. I spent a few hours today dealing with this. I finally solved it, but I wish I had of found this feedback article first...sometimes google is not your friend  :-(

Parent Package Configurations Fail to load:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=174510

hello,

i have a question regarding that.  In my parent package, i get data, i clean it , and at the end i save it in the result set, and now i have to access that result set from my child package, i have defined all the variables, but i just dont knw how to access that result set in parent package from child

hey i am getting these errors if u have an idea please let me know:

The ProcessInput method on component "Result_Set" (240) failed with error code 0xC020207D. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

Error: Error writing to the ADODB.Recordset object.

Hi,

I wanted to do this without using Script  task. My aim is to assign the System:ErrorCode value into one of my loal variables without using script tag.

please help me.

sanjay

sanjay.bollina@gmail.com

I used to Execute SQL task to do this, but i need to use some connection manager. I used ADO.net, But I wanted to use OLEDB as i am having all connections of type OLEDB. what to do regarding assingiing system avriable values to local variables.

please advice me'

sanjay.bollina@gmail.com

Thanks for the package configurations idea. it works like a breeze

An other option is a script task. Instead of pulling the variables from the child package, you can push them from the parent package:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

   Public Sub Main()

       ' Call child package and pass through parameters / variables

       ' The child package is called with the same connection

       ' manager as the Execute Package Task would do

       Try

           ' Configure the child package

           Dim app As New Microsoft.SqlServer.Dts.Runtime.Application()

           Dim package As Package

           package = app.LoadPackage(Dts.Connections("child.dtsx").ConnectionString.ToString(), Nothing)

           package.Variables("User::variable1").Value = "some value"

           ' Execute package and return result

           Dim result As DTSExecResult

           result = package.Execute()

           Dts.TaskResult = result

       Catch ex As Exception

           Dts.TaskResult = Dts.Results.Failure

       End Try

   End Sub

End Class

- Joost (Atos Origin)

Hi Jamie,

Got a bizarre problem.

Have PackageA which has a connection called "Metadata" and a variable called "LogDate" which is got from a ForEach loop ADO Enumerator. I want to pass this to PackageB which has the same names as in Package A.

So in PackageB I create a parent package configuration which assigns both "Metadata" & "LogDate"

Name: MetadataConnectionString

Type: Parent Package Variable

Parent Package Variable Name: \PackageA.Connections[Metadata].Properties[ConnectionString]

Target Property: \Package.Connections[Metadata].Properties[ConnectionString]

Name: LogDateValue

Type: Parent Package Variable

Parent Package Variable Name: \PackageA.Variables[User::LogDate].Properties[Value]

Target Property: \Package.Variables[User::LogDate].Properties[Value]

When I deploy the package to MSDB and run them either from desktop or from SQL Agent, the variable in PackageB jsut doesn't get assigned.

Any ideas? What am I doing wrong?

Note - the variables in both packages are defaulted to a value and tha tis what is showing up in runtime.

Regards

VinGus

Am currently debugging this and found out this error:

---

Information: 0x40016042 at Thread: The package is attempting to configure from the parent variable "\PackageA.Connections[Metadata].Properties[ConnectionString]".

Warning: 0x8001201C at Thread: Configuration from a parent variable "\PackageA.Connections[Metadata].Properties[ConnectionString]" did not occur because there was no parent variable. Error code: 0xC0010001.

Information: 0x40016042 at Thread: The package is attempting to configure from the parent variable "\PackageA.Connections[Metadata].Properties[ConnectionString]".

Warning: 0x8001201C at Thread: Configuration from a parent variable "\PackageA.Connections[Metadata].Properties[ConnectionString]" did not occur because there was no parent variable. Error code: 0xC0010001.

SSIS breakpoint 'Break when the container receives the OnPreExecute event' at executable 'NextJobCheck' has been hit

---

Does it imply that I can't directly pass a connection. Do I've to put it in a variable?

Can the parent package see changes that a child package makes?

E.g. I have a parent package that has a variable called NbrLignes. The child package also has this variable, with the configuration set up to get the value from the parent. The child package writes to this variable using the RowCount transform.

Now, from the parent, I want to see this value and store it. I have tried, but I am seeing nothing at the parent level.  I.e I can't see the change that the child package made.

Jamie,

I have a scenario to execute child packages from a Parent Package. I try to call these child Packages from a Parent Package using For Each Loop Container  and Exectue Package Task.

Each child package has its own variables.

I have configured the child pacakge variable to the Parent Pacakge Variables using Package Configuration --> Parent Package Variable.

The problem that i am facing is I could not able to get the value from the Parent Package.

When i try to get the value in the child package, the script task says "Warning: Configuration from a parent variable "PackageName" did not occur because there was no parent variable. Error code: 0xC001F032. "

Please help me to resolve this.

Regards,

Madhavan.TR

I have had problems passing variables down more than 1 package.  For example, I can go from Parent to Child no problem.  But I cannot seem to get the Parent variable to Child, then to GrandChild.  It seems that I can only go 1 level deep and it does not appear to work beyond that.  Is this correct?

using parent package configuration that gets the value of a variable.  I have variable called LogID.

Package 1, starts, creates logID 5.  Package 1 calls package 2, Package 2 gets LogID 5.  Package 2 does some work and calls Package 3.  Package 3 attempts to get the variable LogID from Package2, but it does not work.  I have done some research on this and I read that it has something do with the variable being the same name in all packages.  Package 3 with a variable of LogID cannot get the LogID from Package 2 for some reason.