Parent-child variable issues that may impact deployment

We are using SSIS for the first time. My team is working on a project that involves putting a date time stamp into a series of tables. These tables are being assembled in a series of child packages being executed by the parent. When the parent runs, we evaluate our timestamp variable as a GETDATE() expression and pass it to the children to be included as a derived column. We don't want the actual runtime of each step to be the timestamp, just the start of the batch (parent).

In order to get the variable to pass over to the child, we needed to set the package location to "file system"instead of "SQL Server". It seems unusual that this would be so. Are we doing something wrong?

What implications does this have for deployment? Will we need to customize the packages for each instance we plan to run this on? Can you have a parent run a child package on a different instance? This would be a performance plus since we have really huge source databases and would like to distribute the processing.

Hmmm, my boss just told me to scratch the whole idea of parent-child and go with a control table to store the variable for all the packages to access. Oh well, I'm still interested in why this is so cumbersome when really its just passing a parameter from one procedure to another.

Oh, and I think you could use a spellchecker on this message box. At least I could use one.

In the child package, create a variable to hold the parent variable. Same name is fine. Then in the control flow, right click on the background and select "package configurations".

Enable package configurations. Then add a new one. Change the configuration type to "parent package variable." Then, in the specify configuration settings entry, enter the name of the variable in the parent package. Next, on the following screen, select the "value" property of the variable created in your child package. (Expand the child variable until you can select the value property.) Click next. Give the configuration a name and hit finish. Done.

Phil|||

Phil,

Thanks, but we already have that functionality. The problem is that we don't like it. We want something more robust, sort of like passing a parameter from one function to another. And we can't even pass a value parameter with this thingy. This parent-child functionality is just plain ugly. We are using a control table instead, but we don't like doing that either.

Parent Package Variables and SQL Package Store

Hello All,

I have a Master Package which calls a group of other packages out on the SSIS Package Store using parent / child and a number of variables. These seem to work when passing various audit information (audit key, record counts, etc) but when I try to pass a variable for the connection string and assign it via an expression to the connection manager, I get the Master Package writing to the correct database and the children package writing to their "default" database which is supplied as the default value in the variables which should be populated by the parent task.

i.e., I end up with my master package audit information in database a and child package audit and transactions in database b, even though the packages associated with these transactions are supposed to be reading the connection string from a parent variable. Any clues or suggestions?

Edit: It is still passing along the correct parent audit information and record counts, it's just not connecting to the correct database by the variables holding the connect string

Thanks for your time.

After your issue the other day, it occurred to me that I do something differently, yet achieve the same thing.

I pass in pieces of the connection strings to separate variables. (ServerName, DatabaseName, etc...)

Then, on the connection manager objects, I use their expressions to build its connection string based on those variables. So I'm not passing in a connection string -- I'm just passing in pieces. Then the connection manager's expression for "ConnectionString" takes it from there. Does that make sense?|||

yeah, that makes sense... I've been thinking about doing that for about a day now myself, although I was just wanting to get this to work first before going through with it. I think I will go ahead and change things over to build up the string dynamically, that way I won't have to retype everything in the set values tab every time I want to tinker with which database or server it is going to.

Right now I am building a test package that is passing the connection string and it works fine, however the test package is passing it to a pacakge on the file server (not in the sql package store). There shouldn't be any difference in how a package accepts parent variables between a file server executed package and a sql store executed package should there?

|||

Well, I just spent a few hours converting those packages over to store the data source, initial catalog, name and password as seperatre variables and build the string up in the expression for the connection string. When I ran the package it was still pointing to the "default" values defined in the child package, even though I had changed the parent to point to a different data base within the source...

Any ideas?

|||

EWisdahl wrote:

Well, I just spent a few hours converting those packages over to store the data source, initial catalog, name and password as seperatre variables and build the string up in the expression for the connection string. When I ran the package it was still pointing to the "default" values defined in the child package, even though I had changed the parent to point to a different data base within the source...

Any ideas?

Are you certain that the parent package variable configuration is set up properly? You may want to try adding a Script Task to your control flow, and within it using a MessageBox to pop up the value being configured just to validate.

Personally, I solve this problem with XML file configurations. I have one dtsConfig file that drives the ConnectionString property of the connection, and have connection managers with the same name in my master and child packages, and this works great for me.

|||Also try setting DelayValidation to true on your connection manager that you are configuring.|||

I am fairly certain that I have them set up correctly in that I am also passing along other information such as audit id, filename, recordcount, etc and that is all populating correctly. It seems like it has predetermined what the connection managers connection string will be before the package receives the parent variable configurations...

|||

Phil Brammer wrote:

Also try setting DelayValidation to true on your connection manager that you are configuring.

Didn't seem to help any...

|||

EWisdahl wrote:

Phil Brammer wrote:

Also try setting DelayValidation to true on your connection manager that you are configuring.

Didn't seem to help any...

Then make sure your parent package configurations are working correctly. Make sure you're mapping to the .Value parameter of your child variable. Also, CaSE matters, I believe. And I always just type in the name of the variable. No "User::" or "System::" prefix.|||

Double checked and triple checked. As I said, I am able to pass off certain values (auditkey, etc) no problem. As a matter of fact, I am able to pass the same connection string (or portions thereof to piece together into the connection string) if I am passing it to a package via the file system, it only seems to come up when passing parent package configuration variables from a parent to a child stored on the SSIS package store.

On a positive note, the set values through sql agent job maintenance works fine for the master package now ... If only it would pass these to the child packages (or if the child packages would pick it up from the master's "default") igh:

|||

After reading another thread I think I may have described something a bit inaccurately. I am storing these packages on the SSIS Server under the msdb subfolder (i.e. connect to integration server, click subnode stored packages, msdb, packagefolder -> import) as opposed to the package store (same as above but click on the file system within the stored packages node). Would that make any difference?

|||

Question: Are you using any other configurations in your child packages, besides the Parent Package Variables? If so, can you describe those?

|||

Nope, just the parent package variables.

|||

Well, i am currently facing something like this...

And i am starting to think that Parent Variables are somehow desync with the child package execution...

In my situation i have Execute SQL Task at the beggining of the package registering the inicial log record... and its beeing inserted with the default values on my child package instead of using the configured parent package values!

Later on i have that kind of connectionString mechanism and its working right... i think its a matter of delay... probably if you try putting a script task before using the connection, you will get your parent variables in time.

Had you tried it?

Just a though...

Regards

Parent Package Variables and SQL Package Store

Hello All,

I have a Master Package which calls a group of other packages out on the SSIS Package Store using parent / child and a number of variables. These seem to work when passing various audit information (audit key, record counts, etc) but when I try to pass a variable for the connection string and assign it via an expression to the connection manager, I get the Master Package writing to the correct database and the children package writing to their "default" database which is supplied as the default value in the variables which should be populated by the parent task.

i.e., I end up with my master package audit information in database a and child package audit and transactions in database b, even though the packages associated with these transactions are supposed to be reading the connection string from a parent variable. Any clues or suggestions?

Edit: It is still passing along the correct parent audit information and record counts, it's just not connecting to the correct database by the variables holding the connect string

Thanks for your time.

After your issue the other day, it occurred to me that I do something differently, yet achieve the same thing.

I pass in pieces of the connection strings to separate variables. (ServerName, DatabaseName, etc...)

Then, on the connection manager objects, I use their expressions to build its connection string based on those variables. So I'm not passing in a connection string -- I'm just passing in pieces. Then the connection manager's expression for "ConnectionString" takes it from there. Does that make sense?|||

yeah, that makes sense... I've been thinking about doing that for about a day now myself, although I was just wanting to get this to work first before going through with it. I think I will go ahead and change things over to build up the string dynamically, that way I won't have to retype everything in the set values tab every time I want to tinker with which database or server it is going to.

Right now I am building a test package that is passing the connection string and it works fine, however the test package is passing it to a pacakge on the file server (not in the sql package store). There shouldn't be any difference in how a package accepts parent variables between a file server executed package and a sql store executed package should there?

|||

Well, I just spent a few hours converting those packages over to store the data source, initial catalog, name and password as seperatre variables and build the string up in the expression for the connection string. When I ran the package it was still pointing to the "default" values defined in the child package, even though I had changed the parent to point to a different data base within the source...

Any ideas?

|||

EWisdahl wrote:

Well, I just spent a few hours converting those packages over to store the data source, initial catalog, name and password as seperatre variables and build the string up in the expression for the connection string. When I ran the package it was still pointing to the "default" values defined in the child package, even though I had changed the parent to point to a different data base within the source...

Any ideas?

Are you certain that the parent package variable configuration is set up properly? You may want to try adding a Script Task to your control flow, and within it using a MessageBox to pop up the value being configured just to validate.

Personally, I solve this problem with XML file configurations. I have one dtsConfig file that drives the ConnectionString property of the connection, and have connection managers with the same name in my master and child packages, and this works great for me.

|||Also try setting DelayValidation to true on your connection manager that you are configuring.|||

I am fairly certain that I have them set up correctly in that I am also passing along other information such as audit id, filename, recordcount, etc and that is all populating correctly. It seems like it has predetermined what the connection managers connection string will be before the package receives the parent variable configurations...

|||

Phil Brammer wrote:

Also try setting DelayValidation to true on your connection manager that you are configuring.

Didn't seem to help any...

|||

EWisdahl wrote:

Phil Brammer wrote:

Also try setting DelayValidation to true on your connection manager that you are configuring.

Didn't seem to help any...

Then make sure your parent package configurations are working correctly. Make sure you're mapping to the .Value parameter of your child variable. Also, CaSE matters, I believe. And I always just type in the name of the variable. No "User::" or "System::" prefix.|||

Double checked and triple checked. As I said, I am able to pass off certain values (auditkey, etc) no problem. As a matter of fact, I am able to pass the same connection string (or portions thereof to piece together into the connection string) if I am passing it to a package via the file system, it only seems to come up when passing parent package configuration variables from a parent to a child stored on the SSIS package store.

On a positive note, the set values through sql agent job maintenance works fine for the master package now ... If only it would pass these to the child packages (or if the child packages would pick it up from the master's "default") igh:

|||

After reading another thread I think I may have described something a bit inaccurately. I am storing these packages on the SSIS Server under the msdb subfolder (i.e. connect to integration server, click subnode stored packages, msdb, packagefolder -> import) as opposed to the package store (same as above but click on the file system within the stored packages node). Would that make any difference?

|||

Question: Are you using any other configurations in your child packages, besides the Parent Package Variables? If so, can you describe those?

|||

Nope, just the parent package variables.

|||

Well, i am currently facing something like this...

And i am starting to think that Parent Variables are somehow desync with the child package execution...

In my situation i have Execute SQL Task at the beggining of the package registering the inicial log record... and its beeing inserted with the default values on my child package instead of using the configured parent package values!

Later on i have that kind of connectionString mechanism and its working right... i think its a matter of delay... probably if you try putting a script task before using the connection, you will get your parent variables in time.

Had you tried it?

Just a though...

Regards

Parent Package Variables and SQL Package Store

Hello All,

I have a Master Package which calls a group of other packages out on the SSIS Package Store using parent / child and a number of variables. These seem to work when passing various audit information (audit key, record counts, etc) but when I try to pass a variable for the connection string and assign it via an expression to the connection manager, I get the Master Package writing to the correct database and the children package writing to their "default" database which is supplied as the default value in the variables which should be populated by the parent task.

i.e., I end up with my master package audit information in database a and child package audit and transactions in database b, even though the packages associated with these transactions are supposed to be reading the connection string from a parent variable. Any clues or suggestions?

Edit: It is still passing along the correct parent audit information and record counts, it's just not connecting to the correct database by the variables holding the connect string

Thanks for your time.

After your issue the other day, it occurred to me that I do something differently, yet achieve the same thing.

I pass in pieces of the connection strings to separate variables. (ServerName, DatabaseName, etc...)

Then, on the connection manager objects, I use their expressions to build its connection string based on those variables. So I'm not passing in a connection string -- I'm just passing in pieces. Then the connection manager's expression for "ConnectionString" takes it from there. Does that make sense?|||

yeah, that makes sense... I've been thinking about doing that for about a day now myself, although I was just wanting to get this to work first before going through with it. I think I will go ahead and change things over to build up the string dynamically, that way I won't have to retype everything in the set values tab every time I want to tinker with which database or server it is going to.

Right now I am building a test package that is passing the connection string and it works fine, however the test package is passing it to a pacakge on the file server (not in the sql package store). There shouldn't be any difference in how a package accepts parent variables between a file server executed package and a sql store executed package should there?

|||

Well, I just spent a few hours converting those packages over to store the data source, initial catalog, name and password as seperatre variables and build the string up in the expression for the connection string. When I ran the package it was still pointing to the "default" values defined in the child package, even though I had changed the parent to point to a different data base within the source...

Any ideas?

|||

EWisdahl wrote:

Well, I just spent a few hours converting those packages over to store the data source, initial catalog, name and password as seperatre variables and build the string up in the expression for the connection string. When I ran the package it was still pointing to the "default" values defined in the child package, even though I had changed the parent to point to a different data base within the source...

Any ideas?

Are you certain that the parent package variable configuration is set up properly? You may want to try adding a Script Task to your control flow, and within it using a MessageBox to pop up the value being configured just to validate.

Personally, I solve this problem with XML file configurations. I have one dtsConfig file that drives the ConnectionString property of the connection, and have connection managers with the same name in my master and child packages, and this works great for me.

|||Also try setting DelayValidation to true on your connection manager that you are configuring.|||

I am fairly certain that I have them set up correctly in that I am also passing along other information such as audit id, filename, recordcount, etc and that is all populating correctly. It seems like it has predetermined what the connection managers connection string will be before the package receives the parent variable configurations...

|||

Phil Brammer wrote:

Also try setting DelayValidation to true on your connection manager that you are configuring.

Didn't seem to help any...

|||

EWisdahl wrote:

Phil Brammer wrote:

Also try setting DelayValidation to true on your connection manager that you are configuring.

Didn't seem to help any...

Then make sure your parent package configurations are working correctly. Make sure you're mapping to the .Value parameter of your child variable. Also, CaSE matters, I believe. And I always just type in the name of the variable. No "User::" or "System::" prefix.|||

Double checked and triple checked. As I said, I am able to pass off certain values (auditkey, etc) no problem. As a matter of fact, I am able to pass the same connection string (or portions thereof to piece together into the connection string) if I am passing it to a package via the file system, it only seems to come up when passing parent package configuration variables from a parent to a child stored on the SSIS package store.

On a positive note, the set values through sql agent job maintenance works fine for the master package now ... If only it would pass these to the child packages (or if the child packages would pick it up from the master's "default") igh:

|||

After reading another thread I think I may have described something a bit inaccurately. I am storing these packages on the SSIS Server under the msdb subfolder (i.e. connect to integration server, click subnode stored packages, msdb, packagefolder -> import) as opposed to the package store (same as above but click on the file system within the stored packages node). Would that make any difference?

|||

Question: Are you using any other configurations in your child packages, besides the Parent Package Variables? If so, can you describe those?

|||

Nope, just the parent package variables.

|||

Well, i am currently facing something like this...

And i am starting to think that Parent Variables are somehow desync with the child package execution...

In my situation i have Execute SQL Task at the beggining of the package registering the inicial log record... and its beeing inserted with the default values on my child package instead of using the configured parent package values!

Later on i have that kind of connectionString mechanism and its working right... i think its a matter of delay... probably if you try putting a script task before using the connection, you will get your parent variables in time.

Had you tried it?

Just a though...

Regards

Parent Package Variable visibility when designing packages

I've created an SSIS package that uses parent package variables at several steps in the data flow. However, those parent package variables are only visible during runtime, making debugging the package practically impossible. Let me give you a simplified example:

In the parent package, I have a string variable named "sqlLookup" that looks like this:

"SELECT * from tblTest WHERE city = " + @.city

Also in the parent package is a variable named "city" which I can set to the name of the city that I want to query on. This dynamically updates the sqlLookup variable, which is being evaluated as an expression.

In the child package, I have an OLE DB Source control which is using the "sqlLookup" variable for its query. I have set up my parent package variable configuration, and it works when I run the package.

The problem is this...when I open the child package, I get an error on the OLE DB Source control using the parent package variable, "Command text was not set for the command object". Presumably, this is because the parent package variable is only available during run-time, and not at design time. And, if metadata changes (which it has), I can't get into the OLE DB Source control to edit it, because it throws the error.

So, my question is this: are there any workarounds for this problem? Is there a better way to do this? This seems like such an obvious problem that I'm wondering if I either missed a step somewhere, or if I'm just doing it the wrong way.

Thanks!

Try setting DelayValidation=TRUE on the data-flow task in the child package.

If that fails, set Work Offline on the SSIS menu.

-Jamie

|||

Jamie,

Thanks for the reply, however I'm not having any luck with either of your suggestions. I created a new Project from scratch to simplify the problem.

My Project contains two files, parent.dtsx and child.dtsx.

parent.dtsx contains only an Execute Package task pointing to child.dtsx, and one string variable called sqlGetNames, which is set to " SELECT * from tblCustomers WHERE city = 'Denver' "

child.dtsx contains a single Data Flow, with one OLE DB Source control. It contains one variable, also called "sqlGetNames". I have enabled Package Configurations, and created a Parent Package Variable that should get the value of sqlGetNames from the parent.

However, when I go into my OLE DB Source control in the child package, I get the following error when I tell it to use sqlGetNames as the variable for the SQL command:

Error at Data Flow Task [OLE DB Source[1]]: An OLE DB error has occurred. Error code: 0x80040E0C. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".

I get this error whether or not I have DelayValidation set to False. If I set SSIS to work offline, then I can't set up the OLE DB Source control because there is no connection.

Any other ideas?

|||It may work if you set the child package's variable to have an initial value that works at design time, ie child.sqlGetNames = "SELECT * from tblCustomers"|||

Thanks. That's not exactly what I was hoping for, but it's a useful workaround. I appreciate the suggestion.

Parent Child Integration using Configuration File

Hi All,

I though i'd been going about setting up my SSIS package to run via a SQL job in the correct way. It would appear however that is not the case.

I have 4 SSIS packages, one of which is the parent package which calls the other three in sequence. I want to run this from a SQL job so that it calls the parent package and it deals with the others. There are connection managers in the package which use a SQL account to access the relevant databases. In addition i have encrypted the package.

I have set up a configuration file which holds the password package and the BillingSystem connection manager password. This file is re used by each package.

After deployment i have set up the SQL agent job to run the parent package. The job returns an error. Looking at the SSIS logging it appears that it completes the SQL task in the parent package and then fails when trying to launch each of the sub packages with an error stating that the SQL account used in the connection manager login failed. It looks as though the sub packages are not getting the password value from the configuration file and this is causing the failure. I though that each package would automatically pick up the config file as it has been setup to do. I have specified the configuration file in the SQL job but this appears to make no difference. Running the integrations seperately i have to add the configuration file in manually to the Run Package dialog box before they will work. This is not saved for the next run.

Can anyone help me get around this problem as i think i have the wrong idea as to how it is supposed to work.

Cheers,

Grant

Are your packages stored in the filesystem or in MSDB?|||

MSDB; Does that make a difference in this case?

Grant

|||Is the SQL server connection for each of the Execute Package task part of a configuration file?|||

Initially the config file contained the connection manager password. It now has the connection string but doesn't seem to be used for the packages called from the initial package. I had to manually alter the i config xml to include the password in the connection string.

Grant

Parameterizing SQL connection

I'd like to know what's the best practice in parameterizing SQL connection in SSIS so that we can move from Dev to QA and to Production easily.

Thanks,

Tommy

Hi Tommy,

I'm not sure if it's a "Best Practice" but I'm using Indirect configuration files to store connection information on different servers.

Kirk Haselden has an article called "Keep your packages in the dark" in the November 2005 edition of SQL Server Magazine, which I found extremely helpful.

|||

Using configurations is certainly best practice, whether indirect or not. You can find more information on configurations here: http://msdn2.microsoft.com/en-us/library/ms141682.aspx

When using Books Online, please remember that you can score and comment on the content at the foot of each page. This helps us to improve the content continuously.

There's also a whitepaper here which may help ... www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx

Donald

Parameterized SSIS Packeges

How i can create parameterized sql query .This is my basic idea to implement.

select * from dimemployee

where name = ?

or

exec proc sp_para_employee ?

how can i pass expocit parameter (that means when i run the package that time it should ask me or when i pass the parameter should execute ,satisfy atleast one xondition ).rf any one have script please provide me because i tried all angles using books on line and other resorces.

did you try using the execute sql task, mapping the parameter to a variable?|||

JSR2005 wrote:

How i can create parameterized sql query .This is my basic idea to implement.

select * from dimemployee

where name = ?

or

exec proc sp_para_employee ?

how can i pass expocit parameter (that means when i run the package that time it should ask me or when i pass the parameter should execute ,satisfy atleast one xondition ).rf any one have script please provide me because i tried all angles using books on line and other resorces.

How hard did you look?

Execute SQL Task
(http://www.sqlis.com/default.aspx?58)

-Jamie

|||thank you it is working fine

Parameterized configuratiion in SSIS

Hello,

Does anyone know if its possible to have multiple package configuations in a SSIS package. That you can control via a parameter in some way?

For example, one configuration for each country.

Thankful for any help!

//Patrick

Assuming you're kicking off the package with a SQL Agent job, you may find the following code we're using helpful. I have highlighted the section dealing with assigning a configuration file programatically. In your case you can modify the code to point to different configuration files depending on a country parameter.

Hope this helps.

ALTER PROCEDURE [dbo].[UTIL_StartPackageWithDate]

(

@.Packagename VARCHAR(255),

@.InventoryPeriodDate datetime

)

AS

BEGIN

PRINT suser_sname()

DECLARE @.ReturnCode INT

SET @.ReturnCode = 0

DECLARE @.jobId BINARY(16)

DECLARE @.JobName varchar(255)

DECLARE @.cmdline varchar(MAX)

DECLARE @.PackageNameRoot varchar(255)

-- get root of package name

DECLARE @.I int

DECLARE @.X INT

DECLARE @.Done BIT

SET @.Done = 0

SET @.I = 0

WHILE @.Done = 0

BEGIN

SET @.X = CHARINDEX('\',@.PackageName,@.I+1)

IF @.X = 0

BEGIN

SET @.Done = 1

END ELSE BEGIN

SET @.I = @.X

END

END

SET @.PackageNameRoot = SUBSTRING(@.PackageName,@.I+1,LEN(@.PackageName)-@.I)

Print @.PackageNameRoot

SET @.JobName = N'StartPackage_' + @.PackageNameRoot

SET @.cmdline = N'/SQL "' + @.PackageName + '" /SERVER "' + @.@.ServerName + '" /MAXCONCURRENT " 1 " /CHECKPOINTING OFF '

--+ ' /LOGGER "DTS.LogProviderTextFile.1";"packagelog.log" '

--+ ' /CONNECTION "PackageLog.log";"O:\Shared\Logs\' + + @.PackageNameRoot + '.log"'

+ ' /CONFIGFILE "O:\Shared\Configuration\PackageConfig.dtsConfig" '

+ ' /SET "\Package.variables[InventoryPeriodDate]";"' + CONVERT(varchar(10),@.InventoryPeriodDate,120) + '"'

PRINT @.cmdline

IF EXISTS( SELECT * FROM msdb.dbo.sysjobs_view WHERE [name] = @.JobName)

BEGIN

-- exists, so, delete the steps

SELECT @.JobID = job_id

FROM msdb.dbo.sysjobs_view

WHERE [name] = @.JobName

EXECUTE msdb.dbo.sp_update_jobstep

@.job_id = @.JobID,

@.step_id = 1,

@.subsystem=N'SSIS',

@.proxy_name=N'SSISProxy',

@.command = @.cmdLine

END

ELSE

BEGIN

-- Create the job

EXEC @.ReturnCode = msdb.dbo.sp_add_job @.job_name=@.JobName,

@.enabled=1,

@.notify_level_eventlog=0,

@.notify_level_email=0,

@.notify_level_netsend=0,

@.notify_level_page=0,

@.delete_level=0,

@.description=N'THIS JOB IS MAINTAINED AUTOMATICALLY, DO NOT MANUALLY ALTER',

@.category_name=N'[Uncategorized (Local)]',

@.owner_login_name=N'ardent',

@.job_id = @.jobId OUTPUT

-- create the new step

EXEC @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id=@.jobId, @.step_name=N'StartPackage',

@.step_id=1,

@.cmdexec_success_code=0,

@.on_success_action=1,

@.on_success_step_id=0,

@.on_fail_action=2,

@.on_fail_step_id=0,

@.retry_attempts=0,

@.retry_interval=0,

@.os_run_priority=0,

@.subsystem=N'SSIS',

@.command=@.cmdLine,

@.proxy_name=N'SSISProxy',

--@.output_file_name=N'C:\datetestlog',

@.flags=0

EXEC @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.jobId, @.start_step_id = 1

EXEC @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.jobId, @.server_name = N'(local)'

END

EXEC msdb.dbo.sp_start_job @.job_id = @.JobID

END

--select * from msdb.dbo.sysjobs_view

--exec msdb.dbo.sp_help_job @.job_name = 'StartPackage_datetest'

|||

Patrick B wrote:

Hello,

Does anyone know if its possible to have multiple package configuations in a SSIS package.

Yes, that's possible.

Patrick B wrote:

That you can control via a parameter in some way?

Sorry, not quite sure what you mean by this. Can you explain your scenario?

Patrick B wrote:

For example, one configuration for each country.

Do you mean that you want to selectively choose which configuration to apply? There are ways to do that and Johan alluded to one way of accomplishing it above. Basically instead of defining the configuration files at design-time, you tell the package at execution-time which configuration file to use. I'm guessing that there is more than one way of skinning this particular cat though.

Is this what you mean?

-Jamie