SQL Server Integration Services simply called as SSIS. SSIS is the enhanced version of DTS. The person one who much familiar with earlier version of DTS he/she can able to migrate to Dtsx (SSIS) package development without much difficult. I had not developed much complex DTS package using DTS Designer 2000, but I am able to create complex package using new ‘Business Intelligence Development suit for integration service’.
SQL Server management studio is having many feature related to DTSX. Once start Integration Services using SQL Server management Studio like ‘SQL Server database Engine’ we can create (simple DTSX package), manage (Import, Export and Run) and migrate (from earlier version) the package
A) Connect SQL Server Integration Services
Select Server type as Integration Services from available services from the drop down.
Select or Type server name in the server name box.
Select either windows authentication or SQL Server authentication as you wish (Depends your server authentication)
B) Manage package
Select the server type either from tool bar or from the menu.
Select Server under integration services.
Manage the package either right click on the package or select the relevant menu from menu bar.
C) Create Simple package
SQL Server management studio having so many optional tool under ‘C:\Program Files\Microsoft SQL Server\90’ folder. One of the optional tools ‘DTSWizard’
I have given some of the screens when we are creating simple DTSX package using this wizard. This package export data from flat file to SQL Server table. (Similar manner, we can create any available destination to any availabel source)
It is possible to open and edit the packages created by this wizard using ‘Business intelligence’.
D) DTS Migration:
As mentioned in the DTSWizard, Migration wizard tool available under ‘DTS\Binn’ directory
After configured migration tool into my Sql Server management studio, I am going to migrate DTS 2000 structred storage file to DTS 2005 (SSIS) structured storage file.
Similar manner migrate the package from 2000 server to 2005 server (or) structured file of 2000 version to 2005 server …