SSIS Package Deployment Method – File System or Server ?

After many implementations, I have a strong inclination and preference to deploy SSIS packages to File System rather than to the Server. File System method of deployment is clean and elegant. It encourages package reusability and in some instances it’s the only feasible option. Here are two common ETL scenarios to support my view:

1. Consider this scenario;

• There are 3  datamarts- say CallCentre-Adelaide, CallCentre-Brisbane and CallCentre-Sydney having the same schema.
• I create a generic SSIS package, say ExtractCallCentreData.dtsx to extract data from each of these call centres. This package is supposed to extract data from each of the call centres at various times independent of each other. Along with the package, I also create a package config file to pass the connection string.

Now to deploy this package to File System, all I need to do is create 3 folders and deploy the package (and package config) to each of these folders as shown below:

• C:\Program Files\Microsoft SQL Server\90\dts\Packages\ADL\ ExtractCallCentreData.dtsx
• C:\Program Files\Microsoft SQL Server\90\dts\Packages\BRS\ ExtractCallCentreData.dtsx
• C:\Program Files\Microsoft SQL Server\90\dts\Packages\SYD\ ExtractCallCentreData.dtsx

 Suppose If I have to deploy this package to the Server, I have to rename these packages 3 times so that the packages are unique. This is required because in Server deployment method sub-folders cannot be created. So I end up creating 3 different packages – ADL– ExtractCallCentreData.dtsx, BRS- ExtractCallCentreData.dtsx and SYD- ExtractCallCentreData.dtsx thereby losing the re-usability of the package.

2. Secondly, in most commercial ETL solutions, there is a main package that calls other packages using Execute Package task. In Development environment, the packages exist in file system. So if you try to deploy this package to server, you have to introducing unnecessary complexity for the package to switch between development and production environment.

Jamie Thompson has a detailed post on this -http://consultingblogs.emc.com/jamiethomson/archive/2006/02/20/SSIS_3A00_-Deploy-to-file-system-or-SQL-Server.aspx

Finally, this is the clincher.  According to Microsoft, server method of deployment is suitable when the package needs to be shared across servers. However, the package config file is stored in the FILE SYSTEM and not on the server.

Server deployments exist for purpose of backward compatibility with the DTS packages migrated from earlier versions of SQL Server when the concept of package config did not exist. For new SSIS development, File System Deployment is the way to go.

Benny Austin