Microsoft's SQL Server Integration Services Intro

DTS's were so... simple, but with a more robust implementation comes complexity. Using Microsoft’s SQL Server Business Intelligence Studio you can create powerful automated packages known as SSIS packages which have taken the place of the DTS. To get started select new project from the File menu and choose Integrated Services project. The toolbox pane has an assortment of control flow items for manipulation of data and common transformation, presentation, and delivery tasks that can be pulled into the design pane.

Once an action has been defined you will need to select the type of security that will be used. If any data connections are made that utilize passwords you will have several options to choose from: DontSaveSensitive, EncryptSensitiveWithUserKey, EncryptSensitiveWithPassword, EncryptAllWithPassword, and EncryptAllWithUserKey. If DontSaveSensitive is selected the connection manager will not save the password for the connections. In this example I'm going to choose EncryptAllWithPassword. This prevents users from even opening the package without the correct credentials. The password can be entered in the blank directly above the level.

Packages can be exported and run manually but it is convenient to maintain a collection of them in one location. For this reason Microsoft has provided an Integration Services Store. From File, select Save a copy of "file name". A window will appear prompting you for additional information. Select SSIS Package Store for the package location, localhost for the server, msdb is the default package path, and Encrypt all data with password for the protection level. Then click the little square beside the Protection Level field to provide the packages credentials. It’s worth mentioning here that if you try to expand the MSDB location under package path and it either errors out or doesn't display anything it may be because you are using a cluster. Within the 90DTSBinnmsdtssrvr.ini.xml file, you can specify the server name which is by default a period representing the localhost. This won't work with a cluster and requires it be changed to the cluster's host name.

After the package has been saved in the store, open the Microsoft SQL Server Management Studio and expand the localhost in the Object Explorer. Expand SQL Server Agent and right click on the subcategory “jobs”. From here you can schedule a new job. If you have used the EncryptAllWithPassword security level you will not be able to use the type "SQL Server Integration Services Package" as a job step. Rather, you can specify the type "Operating System (CmdExec)" from the list and use dtexec to call the SSIS package from the store.

"C:Program FilesMicrosoft SQL Server90DTSBinnDTExec.exe" /DTS "MSDBMy_Package_Name" /SERVER localhost /DECRYPT My_Package_Password /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V

If you run into an error code while working with Integration Services I have found it useful to use the Integration Services Error and Message Reference provided by Microsoft: http://technet.microsoft.com/en-us/library/ms345164.aspx