Quick Question

Hi SSIS Gurus, 

I would like to know your thought about below questions. 

1) Which transformation/object that we have in SSIS that don't have in DTS.

2) Where DTS & SSIS Package stored.

3) When we upgrade package from DTS to SSIS which transformation didn't update.

I can google those questions but i would like to know real GURUS thought about those questions. 

Thanks in advance!

1. We tend to Google that ourselves. Most of the transforms are pretty new in SSIS compared to DTS. :-)  MS really did a complete overhaul when moving from DTS to SSIS.

2. DTS - stored within the MSDB database. SSIS - depends on what you mean. You store the working files locally (DTSX files) and can deploy to a server within msdb (similar to DTS) or to a file store on the SSIS server. SQL 2012 adds some more goodness to the mix as well.

3. Sadly, my experience is that if you have even a slightly complex DTS package, you're better off just re-doing it in SSIS. My "upgrade" experiences were pretty much all to see my DTS package turned into an SSIS package with a "RUN DTS Package" task. :-(

1) SSIS is a completely different beast, a much better beast. There are lots of things that you can do in SSIS with native tasks that you simply couldn't do, or couldn't do without a lot of pain (read also: ActiveX programming), in DTS. I highly recommend you spend some time learning SSIS, it's truly awesome tool.

2) As Peter mentioned, SSIS gives you choice of file system, SSIS store or msdb. With 2012, you have a few more options.

3) Disclosure: I work for Pragmatic Works - A quick (and quite honestly cool) way to check this out would be to use/try our tool called DTSxChange. Our product automatically converts DTS to SSIS packages for you. One of the neat parts of our product is a tool called Profiler. Use Profiler to run against your existing DTS packages and it will tell you what our product will convert for you automatically.

There are some things that can't convert, like ActiveX scripts. In DTS, a lot of folks used ActiveX scripts to do things like dynamic connections and then do simple transform. Depending on what is in your scripts, you may be able to easily replace the functionality of a script with a simple Data Flow task. If you have a lot of business logic embedded, you may want to instead take that code and convert it to VB.NET/C# and place it in a script task. Again, all depends on what you have.