How to deploy an SSIS package

ssis

I am very new to SSIS. I have inherited a bunch of databases and SSIS packages. I notice many jobs utilizing many different packages. When I look at an existing job and try to see where a package it is using is located I just see that it is in the "SSIS Packages" folder.

I know you can either deploy a package to the file system or as a database object(?). How can I tell which type of deployment a package has taken?

Also, in an existing job, an associated SSIS solution where multiple projects contain multiple packages utilized by this job, there is one package I needed to modify. It isn't currently scheduled as part of a step of the job. When I go to add the new step, I don't see as an option that I can choose this package. I assume it must not be deployed. So how do I deploy it and how do I make sure I deploy it the same way the other packages in this job were deployed? (Really – how do I not mess things up!?)

Best Answer

How can I tell which type of deployment a package has taken?

Look at the definition of your SQL Agent Job Step.

If it specifies /file then the package has been to deployed to the file system somewhere.

If it specifies /DTS then it's using a well known location on the SQL Server installation path (precise location eludes my memory as of this posting but \Program Files\Microsoft SQL Server\XXX\DTS\Packages and/or C:\Program Files (x86)\Microsoft SQL Server\XXX\DTS\Packages are good guesses)

If it specifies /sql then it is deployed to the specified SQL Server in msdb.dbo.sysdtspacakges90 (SQL Server 2005) or msdb.dbo.sysssispackages (SQL Server 2008/2008R2+)

As to scheduling a package that you can't see, this sounds suspiciously like you're not on 2008 R2 as you think but much more like 2012+ as this is the SSISDB with the folder/project/package structure. If this is the case, packages can be specified as being Entry Point Packages or not. It's just a UI convention, there's nothing that prevents them from running. There's a selector that specifies whether you want Entry Point Packages or All. Switch to All packages

Option A

That could be a reason why they use a batch file to kick off the SSIS packages. They want to ensure the correct dtexec is being used to run the package. Shamless self promotion on determining whether it's 32 vs 64 http://billfellows.blogspot.com/2015/02/is-my-ssis-package-running-in-32-or-64.html Also https://stackoverflow.com/a/13092260/181965

Option B

Another reason I've seen batch scripts used as they didn't use configuration (file, table, whatever) but wanted to get a different behaviour per environment. Assuming I had a ConnectionString variable in my SSIS package and the design time value was Dev but now I'm in production, one way to change that value is to always set the property when we run, psuedocode is

dtexec.exe /file MyPackage /SET ConnectionString;Prod

If I'm lazy, I could use the same job definition of across all my environments but change the contents of the /SET value as need be. I don't see it being that useful but some people view tasks with varying degrees of difficulty.

The above doesn't have to be just for /SET. They can set logging or anything else up really. Maybe they want to log the output to an execution log because they didn't know SSIS supports that natively. There might be /REP EWIP > CurrentRun.txt in the dev batch file but production only have /REP E

Option C

Another reason would be the illusion of security. At a former employer, they used the batch script approach to run SSIS packages. Developers were not granted access to SQL Agent. xp_cmdshell - oh hell no to that. Nor could we be trusted to log onto the pre-production boxes (Load, stage, UAT) but we still had the need to run SSIS packages on demand.

So, after a few weeks of submitting urgent request tickets every time the packages need run, they created a pair of stored procedures that provided a gated package execution experience. RunSSISPackage and RunSSISPackage32 which would take the name of the package to be run. For every package we created, they would create a batch script that ran it. Lots of busy work for all parties but at the time it was the only way they were letting us run packages. The procedures used xp_cmdshell and EXECUTE AS to allow us to run the packages as they would be - with full sysadmin rights. By the way, when you allow people to deploy SSIS packages without auditing, they could add an Execute SQL Task in there that adds the developer group into the sysadmin role. I think. Maybe. Hypothetically.

Option D

Not SQL Agent. I've seen other scheduling tools used at clients that weren't SQL Agent. The support for SSIS specifically has been spotty but they've all supported batch files so perhaps you have some Enterprise scheduler that wasn't compatible with SSIS.

Otherwise, if it's just dtexec.exe /file foo.dtsx ... I got nothing

Sql-server – Change SSIS package authors

You would need to check the security setting of the package configuration (inside the package). The default for a package is "EncryptSenstiveWithUserKey", which means only that user can modify the package or read the connection manager objects.

You also don't provide how the packages are deployed. If they are deployed to the Package Store or SQL Server then the security setting mentioned above does not apply. If they are using file level deployment who executes the job or owns it can play a role in the issue you are having....meaning you will have to adjust the packages.