Should I use vbscript or a vb.net exe for a DTS

[joelwenzel]

Hello,

I am writing a tedious DTS that requires data to be transferred from a big database into multiple tables in another big database.

I could program the DTS using a buch of activeX scripts or using a VB.NET program.

I am leaning on the VB.Net program because I can easily reuse code, debugging is better, and I can divide the code into separate files for the different tables. These seem like good reasons to go with VB.NET. Any reasons why I should stick with vbscript? I can't really think of any except that vbscripting is built in to SQL server.

 

[DrSql]

I am not a VB script person, but you can save the dts pacakge and execute from SQL syntaxes.
If you are just data transfer from one db to another why dont you just create a straight DTS package Source and Destination with your required transformation, unless its more complicated. You can schedule ot execute as you desire, also exciet from SQL statements.

Dr.Sql
Good Luck.

 

[joelwenzel]

Well,

I started with a straight forward TSQL script file and did exactly what you suggest but it got really long an unmanageable. Plus, since TSQL does not have arrays and other such useful features found in vb, it was very complicated proforming certain simple tasks.

So, what I'm trying to do now is have a DTS package that jsut executes a vb.net exe acording to a schedule from sql server agent. My transformations involve a lot of data manipulation during the process. This seems like a good reason to use vb.net, right?

I only ask because I am relatively new at my current job and I don't want to go too much against the flow

 

[DrSql]

If you have a lot of data minuplation then my suggestion would be import the data into a temp table and massage the data and import. Thats the process I uses. This will make troubleshooting easier.
Step 1:
Import the Table from Db1 to DB2 TempTable
Then massage the data in DB2
Then Insert those data into appropriate tables as you desire.

If you need assistance I can guide you through.


Dr.Sql
Good Luck.

 

[joelwenzel]

What do you mean by massage the data in DB2? Does that mean that once you import it in the temptable, you adjust it in the temptable and then import it into the final table?

 

[DrSql]

Yes Import the data make all the minuplations prior to import and import should be your last setp. This will make your trouble shooting easier..
Sorry..DB2 I meant Database 2 or your import destination DB.


Dr.Sql
Good Luck.