Import, Export, Copy and Delete SSIS Packages

By: Jeremy Kadlec | Read Comments (1) | Print Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009. Related Tips: More

Problem
I have seen many of the changes with SQL Server 2005 Integration Services (SSIS) versus SQL Server 2000 Data Transformation Services (DTS).  Integration Services certainly has much more functionality out of the box than DTS and I am learning SSIS as my projects move forward.  One item that has seemed to be a thorn in my side is deploying an SSIS package.  So, I have read your tip (SQL Server Crosswalk - Deploying a SQL 2000 DTS vs. a SQL 2005 SSIS package) related to deploying a package and wanted to find out if any other options are available?  Can you shed some light on the situation?

Solution
You are right about SSIS vs. DTS.  SSIS certainly has a great deal of functionality out of the box as compared to DTS, but in some respects some of the simpler aspects of DTS Packages have been over shadowed by a standardized development platform (Business Intelligence Management Studio) with SSIS.  The net result is for the sake of new technology, new processes are needed.  One of those items could be considered the deployment of an SSIS Package with the deployment manifest wizard.  In the SQL Server Crosswalk - Deploying a SQL 2000 DTS vs. a SQL 2005 SSIS package tip we talked about creating a deployment manifest file and using that file for deployment purposes.  Let's also outline another approach in this tip which is using Management Studio to import or export an SSIS Package.

Deploying an SSIS Package

Although the detailed steps for deploying an SSIS Package are outlined in this tip (SQL Server Crosswalk - Deploying a SQL 2000 DTS vs. a SQL 2005 SSIS package) let's outline the general steps:

• Build the package in Business Intelligence Management Studio (BIDS)
• Change the package configurations to build the deployment utility
• Build the deployment directory with all of the needed files
• Execute the deployment manifest file which launches the wizard
• In the course of the wizard, deploy the SSIS Package to either the file system or the MSDB database
• Execute the SSIS Package via the DTExec utility, a SQL Server Agent Job or a script

As you can tell, this is a much different process as compared to the SQL Server 2000 DTS Packages where all of the development and deployment was directly in Enterprise Manager.

Importing an SSIS Package in Management Studio

As compared to the process above, importing an SSIS package via Management Studio may be considered a much simpler approach.  Let's walk through the process of importing the SSIS Package via Management Studio once the package is saved in BIDS.

Exporting an SSIS Package in Management Studio

Since we covered importing an SSIS Package with Management Studio, let's also cover exporting an SSIS package with Management Studio:

Delete an SSIS Package in Management Studio

The deletion process in Management Studio is very straight forward.  Just right click on the package and select the 'Delete' option.

Save Copy of Package in Business Intelligence Development Studio

Command line management with dtutility

Although the Management Studio and Business Intelligence Development Studio offer rich interfaces to manage SSIS Packages, Microsoft also offers the option to copy, move, delete, or verify the existence of an SSIS Package with the dtutil command. This alternative may prove priceless if you are faced with a situation where you need to manage large numbers of SSIS Packages in an automated manner.  Here are some simple coding examples with the dtutil command:

The sample code in this tip for the dtutil command is only the tip of the iceberg.  For additional information about the dtutil command visit - dtutil Utility.

Next Steps

• With all of the change between DTS and SSIS, be sure to have a standardized deployment process with a consistent location, naming standard, etc.  If not, the SSIS Packages can quickly become unwieldy whether they are stored on the file system or in the MSDB database directory structure.
• Make sure the deployment is also included in your standards.  You do not want to have SSIS Packages living all over your SQL Servers as well as reading and writing from numerous locations in the file system.  Check out the functionality that you need and build a set of standards.
• Since a four primary options (deployment manifest file, Management Studio, BIDS, command line interface) are available, be sure to test out each of these processes and see which one best meets your needs.
• Check out all of the SQL Server Integration Services related tips.
• Have you seen more pros and cons from your work from DTS to SSIS?  If so, share your experiences in the forums by clicking on the link below.

Related Tips: More | Become a paid author

Last Update: 2/6/2008

Share:

Share

Tweet

Free SQL Server Learning The SQL Server Security THREAT - It’s Closer Than You Think Tutorial - SQL Server Performance Tuning and Monitoring

Comments and Feedback:

Monday, February 14, 2011 - 1:34:43 AM - Vikash Kumar Singh	Read The Tip
Nice article. Good info on DTUTIL.

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Try the award winning SQL diagnostic manager as a free 14-day trial! SQL Monitor – For database professionals who need results on Day One. Try it online. SQL Servers keeping you up at night? Contact expert SQL Server consultants for a Health Check. Free Trial: Get Proactive Insight with Spotlight® for SQL Server Enterprise. Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now. Valuable SQL Server web casts on Performance Tuning, Development, Administration, SSIS and more...