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