DTS to SSIS - is my approach correct? 1

[sqlsadie108]

Hi,

I am converting several DTS pkgs to SSIS. Several of the pkgs contain complicated "Active X script" transformations on text files. That is, it would take me a long time to have to re-write this!

In the meanwhile, do you think it's just best to use the EXECUTE DTS 2000 task until I have a better grip on SSIS??

Also, what is the equivalent of "Active X script" validation in SSIS?

For example, I have an Active X script that checks the values of a particular column in a text file. If the column contains a datefield, then load into the database, if not, then discard... what task in SSIS would replace this logic? (not now, but for later reference)

Thanks so much

 

[mrdenny]

There are .NET scripts which would be used to replace this.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[sqlsadie108]

so you're saying that .net is the way to go? aren't there equivalent tasks in ssis,

for example, to perform data validation?

you would think so, since they microsoft went thru the effort of removing active x tasks in ssis

 

[MDXer]

If you look at the types of validation being done you might actually be able to replace them using some of the Data Flow transforms.

For example if one of these scripts looks for Null data and then replaces it with some default value this could easily be done in the data flow.

You may find that while it takes an effort to rebuild your DTS packages in SSIS, but the performance gain make it worth it. I have a DTS process that takes 2.5 Hours to process 5 days worth of data, the same package built using SSIS now takes about 45 minutes.

I have found the key to quickly developing SSIS packages is have a clear picture of where you start and what needs to occur to get the desired output before you begin.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[sqlsadie108]

Thanks for the info, also seems like at some point microsoft will phase out support for DTS packages, yes? Although that could be a while down the line. Does anybody know?

 

[mrdenny]

DTS is not supose to be included in the next version of SQL 2005 (SQL 2005 R2, SQL 2007, or what ever they decide to call it).

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com