DTS Function

[Andy Cudlip]

Hi

I'm creating DTS packages to load data from text files. I'm finding that a lot of the data needs to be transformed in the same way (e.g trailing "." removed). Rather than writing individual activex scripts to do this, is it possible to write my own DTS function which I call from my DTS packages?

Thanks

Andy

[Stephen]

The short answer is no. Out of the box, DTS doesn't allow you to do this.

The slightly longer answer is that you might be able to achieve this if you created your own custom task (e.g. write some VB code and register this to DTS).

[ljmou9]

Hi Andy,

If all your files have the same fields, you can create a Stored Procedure for your simple transformation first, then in your DTS package, add a SQL Task to execute the Stored Procedure. You can Execute your Stored Procedure in Multiple DTS packages as long as your stored procedure and the destination table in the same database. If you need more help for this, just send me an email.

Jun

[Andy Cudlip]

Thanks for the replies.

Unfortunatley, I have 20 tables, all of different designs - and this is just a pilot, if the project goes ahead there will be about 600 tables! (DB2 to SQL Server migration)

I have tried writing a vb function and saving this as a DTS package and calling it from my other DTS packages with no joy.

[Andy Cudlip]

Thanks for the replies.

Unfortunatley, I have 20 tables, all of different designs - and this is just a pilot, if the project goes ahead there will be about 600 tables! (DB2 to SQL Server migration)

I have tried writing a vb function and saving this as a DTS package and calling it from my other DTS packages with no joy.

[dwestmore]

I have also had a similar problem, it seems strange that it is difficult to create VBScript functions which have scope across various tasks in a package or (even better) across all packages.

Maybe it is possible, I solved my little problem just duplicating the function, which fortunately was well proven, so I've never needed to update it.

I suppose SQL Server UDFs (user defined functions) could be an alternative, but I've never tried building one and I find T-SQL rather difficult as a language (I mean all the non standard procedural parts of the language). I'd just prefer VBScript.