Tuesday, December 19, 2006

SSIS Package persistance locations

SSIS Packages can be persisted in three types of locations. They are File System, SSIS Package Store and SQL Server. The most common and widely used storage location is File System and saving package to a file system is pretty simple and straight forward. When ever a package is created using either BIDS or VS.NET environments, the package with the is persisted in to the location where the profile file is created.The default extension given for the package is .dtsx(DTS Extension) and content in the file are are well structured XML. The second storage location is SSIS Package Store which is similar to File System storage. When a package is stored in a SSIS Package Store then it is stored as a file in SQL Server's folder Program FilesMicrosoft SQL Server90DTSPackages. The third location to store the package is SQL Server database msdb. When a package is stored in SQL Server database, then content of the package is persisted as rows in to tables sysdtspackages90, ssydtscategories, sysdtslog90, sysdtspackagefolders90, sysdtspackagelog, sysdtssteplog, and sysdtstasklog.

File SystemSSIS Package StoreSQL Server Database
Encryption SupportYesYesYes
Backup SupportFile backupFile backupDatabase backup
Execution controllingSSIS execution utilitiesSSIS execution utilitiesSSIS execution utilities and database jobs
Access controlling with possibleNot possibleNot possible Supported with server roles