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 System | SSIS Package Store | SQL Server Database | |
| Encryption Support | Yes | Yes | Yes |
| Backup Support | File backup | File backup | Database backup |
| Execution controlling | SSIS execution utilities | SSIS execution utilities | SSIS execution utilities and database jobs |
| Access controlling with possible | Not possible | Not possible | Supported with server roles |
0 Comments:
Post a Comment