Sql-server – How to deploy SSIS package with XML config, using SQL agent

sql serversql-server-2008-r2ssis

I wonder if anyone can help me with a question.

Context
I've created a database which needs a front end in MS Excel. When the user presses upload button I want it to start an SSIS package which moves the data into some tmp tables then runs a couple of stored procs to move the data into the correct tables. I'm struggling to deploy my package successfully.

Hurdles I've already jumped over

The SSIS package is functional in debug mode.
I've got a SQL Agent job which can run the package on the integration services server file system.
I've managed to create a way of the users starting the job from excel and inheriting SQLAgentOperatorRole through the very convoluted process of creating certificates and a new stored proc, I called it spTriggerJob [with parameter for job name], which then runs EXEC msdb.dbo.sp_start_job @job_name = @strJob with the correct privileges.
I've created and implemented a proxy with permissions to access the network file server.
I've created XML configurations to pass the required variables (e.g. the filepath of the file to be uploaded) and a way of updating them on-the-fly.

Problem 1
When I run the package manually from SSMS I can load the XML configurations successfully. However I haven't been able to do this from the SQL Agent job. If I go into job properties > steps > edit > configurations tab > add the only options I get are C:\ D:\ and a couple of obscure/unhelpful network drives. Ideally I want to point it at a full UNC filepath. Is it necessary to map the drive on the server?

I suspect there is a more optimal way of deploying my SSIS package which circumvents this. Can anyone suggest a better way? I'm fairly new to all this so don't make any assumptions as to my existing knowledge!

Problem 2
When I use my spTriggerJob [name of job], I see a weird message in the job history. It says "The job succeeded. The Job was invoked by user " then it gives the Windows account login of one of the network administrators. However I've been able to verify that the permissions of the proxy (which have nothing to do with Mr Network Admin) are applied correctly by using it to create an entry in a test table and recording the user name.
However if I call msdb.dbo.sp_start_job directly, then the login which appears in the job history message is my own.
This seems wrong! Does anyone know how to resolve this?

Edit: I figured out problem 2. It is because 'spTriggerJob' has to have 'WITH EXECUTE AS OWNER'. Whilst I thought I was owner, in fact it is Mr Network Admin. The confusing bit was that the job was running with the correct privileges inherited from the proxy, which threw me off a bit.

Required solution
I need to reach a point where the package is run on an ad hoc basis at the click of a button, with different configuration settings each time it is run. Surely it can't be that hard!!!

Thanks in advance for your help.

Cheers,
Andy

Best Answer

If you want to store the configuration file and/or the file you need to "pick up" via the SSIS package on a UNC path, SQL Server is going to need permissions to that direct UNC path. This permission is based on your service account being used for SQL Server Agent. If you are in a domain environment, then it is best to have your service run as a domain account and it will allow you to easily provide access to network resources.

If this is a configuration that is meant to change often and I would not be the one to do it necessarily every time, then I might chose to store the configuration in SQL Server. There are a few examples out there online that show how to do this, just one example here.

Related Solutions

Sql-server – Running an SSIS package owned by a domain user from SQL Server running on a local service account

My personal opinion is that option #1 is the way to go. But I see no need for you to have to grant the domain account local administrator access. It seems to me that it requires access to certain folders and files and so you could grant the domain user access to only the resources that it needs to run the package successfully. This can be done through the file/folder properties dialog box and select the security tab - there should be no need to set it for every file and folder as you could set the permissions of the parent directory and set them to override child properties.

I hope this helps you.

Sql-server – How to override SSIS 2008 package config file path

You dont have to use SET. Instead you can do that using DTExec.exe /CONFIG parameter as below:

SQL Agent Job --> Steps --> General --> Type (Operating System(CmdExec)) ---> command

"Drive\Microsoft SQL Server\110\DTS\Binn\DTExec.exe" /FILE "D:\SSIS\SSIS_PackageName.dtsx" /DECRYPT "STRONG_PASSWORD" /CONFIGFILE "D:\SSIS\CONFIG_FILE_PATH.dtsConfig" /CHECKPOINTING OFF /REPORTING E

EDIT: Below is an excerpt from msdn

/Conf[igFile] filespec (Optional). Specifies a configuration file to extract values from. Using this option, you can set a run-time configuration that differs from the configuration that was specified at design time for the package. You can store different configuration settings in an XML configuration file and then load the settings before package execution by using the /ConfigFile option. You can use the /ConfigFile option to load additional configurations at run time that you did not specify at design time. However, you cannot use the /ConfigFile option to replace configured values that you also specified at design time. To understand how package configurations are applied, see SSIS Package Configurations and Behavior Changes to Integration Services Features in SQL Server 2008 R2.

You have asked on How to override SSIS 2008 package config file path?

What you are talking about is SET switch -- Overrides the configuration of a variable, property, container, log provider, Foreach enumerator, or connection within a package.

I have been dealing with SSIS dev's and I use /CONFIG when deploying it to PROD .. provided the paths are same in the package.

EDIT: 2

Agree with OP that the behaviour has changed in 2008 and up :

In SQL Server 2008 Integration Services, events occur in the following order:

The utility first applies the design-time configurations.
The utility then applies the run-time options that you specified on the command line when you started the utility.
Finally, the utility reloads and reapplies the design-time configurations.

In above scenario, you can do as below:

option 1:

DECLARE @ssis_cmd VARCHAR(4000)
DECLARE  @Packagepath VARCHAR(50)
DECLARE  @FileName VARCHAR(50)
SET @Packagepath = 'D:\SSIS\SSIS_PackageName.dtsx' -- SSIS package location
SET @FileName = 'D:\SSIS\CONFIG_FILE_PATH.dtsConfig' --Passing dynamic variable i.e 'file location' into ssis package.
SELECT @ssis_cmd = 'dtexec /F "' + @Packagepath + '"'
SELECT @ssis_cmd =
@ssis_cmd + ' /SET \Package.Configurations[Config].Properties[ConfigurationString];"' + @FileName + '"'
EXEC master..xp_cmdshell @ssis_cmd

Option 2:

As described here, you can create table SSIS_Configurations and then load them during runtime. Good example is given here.

Found a connect item here

HTH

Best Answer

Related Solutions

Sql-server – Running an SSIS package owned by a domain user from SQL Server running on a local service account

Sql-server – How to override SSIS 2008 package config file path

Related Question