Upgrading DTS Packages to SSIS - 2012

Upgrading SSIS Packages from SQL Server 2005/2008 to SQL Server 2012 - Friday Tech Call

Upgrading DTS Packages to SSIS

DTS packages

-------------------

Option1:

------------------

To have dts packages backward compactability with 2005,2008 , so that the dts can run. The components that need to be installed

Step 1: ->

Microsoft Download Center:

Feature Pack for Microsoft Sql Server 2005

search on : Backward Compatiability 

Scroll down on it.

Microsoft 2005 Backward  compatibility  components.

This one will install the backward components for 2005,2008.

Here the 90% work is done.

Step 2:->

Now need the DTS package Designer. This will allow to open the DTS packages in 2005, 2008 Sql Server.

This only works in 32 bit mode.

Microsoft 2000 Sql Server  DTS Designer Component. 

If  your machine is 64 bit machine or 2008 machine it has issues

This is one is only   for  32 bit machine and for 2005.

This ddl is copying files to wrong directory i.e. /80/ for it to work for 2008 & 2008 R2 copy to /100/

Go to SSMS of sql server & right click then you can open packages.

It would error out saying you do not have sql server 2008 R2 backward compatibility.

The copy script copy for 2008 R2 it need to copy to 105 not /100/.

Right click on DTS -> Import Packages ->

-------------------

Option2:

------------------

Upgrade with Microsoft Package Upgrade Wizard.

-Built Into Sql Server 2005/2008.

-Pros:

  -Free

 -Work on Simple Packages

-Cons:

 - Does not handle ODBC

 - Only handles a few types of text file use cases

 - No Dynamic Properties Task

 - No UDL or legacy database support in data pump

 -Packages only have about a 20% chance of working

Demo how it works:

In BIDs, in the solution explorer, right click on SSIS packages,

"Migrate DTS 2000 Package"

Wizard will prompt to choose 

Sql Server 

or  " Structured Storage File" 

-There are some changes like the Script window neatly done

- But another , 

Sample Upgrade Project Plan:

-Scope and Number of Packages

-How long will it take you to migrate each type of task

-Use a tool to migrate

-Upgrade the Activex Script Task logic

-Test, test and test

Source: Free Sql Training - Upgrading DTS Packages to SSIS

DTS vs SSIS Engine Speed Test

- On 32 bit dual core machine

-Pulling 1 million rows out and writing to SQL table

with no transformation

-SSIS 65% + faster than DTS

-Adding transformation would add more SSIS advantage

                                  DTS          SSIS SQL Server   SSIS OLE DB

Average Runtime                        Destination              Destination

(Seconds)                 33.2 s         11.3 s                      12.3 s

Designing Packages:

DTS Designer

DTS 2000 Package Designer

Create Table - 

There is not whole lot to it. You have connection,Task.

For SSIS package, In BIDS , it's in Microsoft Visual Studio and whole lot of features to it.

It's all XML , so you can view it. You may not want to change it.

Comparing DTS to SSIS Objects

-Transform Data Task

Here just from a file you put into the destination , there is nothing much

to transformation. Seems you have to write Activex to do that.

But in SSIS, there is totally a "Data Flow Task"  where you have lot

transformation and no need to write Activex.

Connections:

DTS Connections: 

Every time you want to grab the data from a file to destination you have

to every time specify the connection.

In SSIS it's changed.

Connection Managers:

You can put your connection & you can call these connections through out entire package.

To see DTS package, you need to log on to "SSMS" & choose "Management" then "Legacy"  then "Data Transformation Services"

open it & 

just can see the file & the connection & Destination but can not

interact with them.

DTS Migrations

DTS Package Profiler Promo

Free SSIS Training - Upgrading DTS Packages to SSIS

DTS Package Profiler by Pragmatic Works