DTS - ActiveX DataTransformation Script Problem

[tomatdeepwater]

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 in a known 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

Does anyone know how to RESET the Destination Table Row postion?

Thanks,

 

[SQLDenis]

I need some more info to help you.
Are you clearing out the temp table
Do you have an identity column if so truncate the table and it will be reset to 1 (truncating is also less expensive than deleteing since it's not logged)
You can also use a global variable and increment that with every row before/after doing the insert in your activex task

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[tomatdeepwater]

Thanks for your quick response!

My process drops the temp table and recreates it at the beginning of the DTS package run.

The temp table does NOT currently have an Identity column.

If the transform task finishs reading and copying 5 rows into the temp table's column 1, when it starts copying data into column 2, it starts writing to ROW 6 vs ROW 1. This won't work because when all the columns are filled in, they are supposed to be records (unfortunately, I can only get the data one column at a time from my external process).

I don't know how to tell it to start at Row 1 again.

Note: there are roughly 84 columns in this temp table that I populate.

My plan, once this data gets safely into the database, is to convert the data to its proper data types (currently comes in as text) and normalize by mapping to existing permanent tables that do have Identity columns.

Does this help?

Thanks!