SSIS packages backup

  • Hi, I am developing SSIS packages in our development server. How do I backup my SSIS packages so that I do'nt lose those packages incase something happens to the dev box. Thanks!

  • If you're saving them to SQL Server, they're stored in msdb and will be backed up when you backup that database. If you save them to the file system, you could use a source control solution like Visual SourceSafe.

    Greg

  • Thanks for the reply. Per the properties of the SSIS package, the full path of the package shows as "C:\SSISpackage\package1.dtsx", so based on this I think the package is getting saved in C:\SSISpackage. If I want to save the package in SQL server how can I do that? Usually which one is preferable saving in a folder or saving in SQL server?

    Thanks!

  • After you save the package to a .dtsx file, you can import it into SQL Server by opening SSMS, connect to Integration Services, expand Stored Packages, right-click on MSDB, and select "import package...".

    Opinions vary on which storage location is better. We've chosen to store packages as files because we can use configuration files and because the packages seem to be easier to move from instance to instance.

    Greg

  • Storing packages in the file system is usually better for development, primarily because you can use source control. (Hopefully your source control archive is part of a regular backup.) You can only store the package itself in a server, but you can store a complete project in a file system. A project might include documentation files, a group of related packages, or anything else that might aide the development effort. In my opinion, package development should always be done with a Visual Studio project stored in a file system.

    Is your package intended to execute on a server, or on a client? If it runs on a client the file system version is probably sufficient. If it runs on a server there may be good reasons for deploying the finished package to that server (stored in msdb). In a secure network environment, a remote server may not have access to the file system used by developers, and developers may not have rights to copy files over to the server. Developers can work with the file-based package without affecting the production copy. If a package is used for a critical scheduled job, there are more ways for it to fail if the server has to go over the network to another file system to find the package. It may be easier for the DBA to manage security issues if the package is on the server.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply