DTS: Data Transformation w/ ActiveX Problem

Question

DTS: Data Transformation w/ ActiveX Problem

Tom Carnahan

SSCommitted

Points: 1788
More actions
October 25, 2005 at 8:14 am

#67146

I have a process that queries an external API for data that is in a "flat file" format. Unfortunately, the other source only transfers its data one column at a time.
My pre-processing (prior to DTS) takes and writes the data out into text files (one text file per column/field to a directory) with names like "1.txt" for the first column, "2.txt" for the second, etc.
My DTSDatatransform is set to write the columns to a temporary table that contains all of the columns of the external data source. I have named these columns "a1", "a2", etc.
My ActiveX script works very well to import each column and put it into the correct field in my temp table. As the ActiveX script copies a text file to its corresponding column, it picks up the next file, determines the correct column and starts updating the new field.
PROBLEM:  When the ActiveX process completes copying to the temp table and goes to get another file, it does not reset the row of the temp table to Row 1.
The data in the temp table will look something like this:
a1       a2        a3
-----------------------
1        null      null
2        null      null
null       3       null
null       4       null
null     null        5
null     null        6
Note:
Records 1 & 2 came from text file "1.txt", 3 & 4 came from text file "2.txt", etc.
Does anyone know how to RESET the Destination Table Row postion?
Thanks,

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Don Staten SSC Eights! Points: 957 More actions · Answer 1

Tom - As I understand your question, it looks like you have three text sources in your DTS package which are then attempting to populate a table using a Transform Data Task. Personally, I would use one ActiveX Script Task.

Take a look at the following script to see if this would accomplish what you are trying to do. I tested this on some test files and seems to work well. Keep in mind that this script will work only if all three text files have the same number of rows. And I am also assuming that each row will correspond to the same row number in each text file.

Good Luck!

Don

'**********************************************************************

' Visual Basic ActiveX Script

'************************************************************************

Option Explicit

Function Main()

Const ForReading = 1, ForWriting = 2, ForAppending = 8

Const adOpenKeyset = 1

Const adLockOptimistic = 3

Const adCmdTable = &H0002

Const strTable = "Test"

Const strPath1 = "C:\Temp\1.txt"

Const strPath2 = "C:\Temp\2.txt"

Const strPath3 = "C:\Temp\3.txt"

Dim fso1,fso2,fso3

Dim adoConn

Dim adoRS

Set adoConn = CreateObject("ADODB.Connection")

Set adoRS = CreateObject("ADODB.Recordset")

adoConn.Open = "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=YourCatalog;Integrated Security='SSPI';"

adoRS.Open strTable,adoConn,adOpenKeyset,adLockOptimistic,adCmdTable

Set fso1 = CreateObject("Scripting.FileSystemObject")

Set fso2 = CreateObject("Scripting.FileSystemObject")

Set fso3 = CreateObject("Scripting.FileSystemObject")

Set f1 = fso1.OpenTextFile(strpath1,ForReading)

Set f2 = fso2.OpenTextFile(strpath2,ForReading)

Set f3 = fso3.OpenTextFile(strpath3,ForReading)

Do While Not f1.AtEndOfStream

adoRS.AddNew

adoRS("col1") = f1.ReadLine

adoRS("col2") = f2.ReadLine

adoRS("col3") = f3.ReadLine

adoRS.Update

Loop

Main = DTSTaskExecResult_Success

End Function