DTS handling multiple CSV file format

Question

Post reply

DTS handling multiple CSV file format

Tiago Rente

Default port

Points: 1403
More actions
August 18, 2004 at 3:14 am

#360687

Hi,

I need to write a DTS that is able to read a file that can have different format.

E.g., I have a CSV file created by the Performance Monitor (it is a counter log) on a server running Windows 2003 (64-bit) that I need to load to a SQL Server table. This process has been running in another 64-bit servers (only running SQL Server) without any problems, but in the new one (running SQL Server and Analysis Services) sometimes the SQL Server counters are present in the file, other times are not. Apparently, the problem may be occurring due to http://support.microsoft.com/default.aspx?scid=kb;en-us;839506. Nevertheless, I would like to make sure that I am able to read the same file with different file format.

Thanks in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

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

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

Bring the data into a single column SQL table and PARSE away
Use VB to crawl through the text file and PARSE away and send the data into SQL via a SP that accepts NULL for the missing columns

Just off the top of my head. Hope it helps

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Scott Cupstid-145037 SSC Enthusiast Points: 126 More actions · Answer 2

As a thought, start by attempting to load the table using the CSV file format that contains the counters. If the counter columns are not present, the Data Transformation task should fail. Using an On Failure workflow, attempt executing a Data Transformation task that uses the CSV file format that does not contain the counters.

Tiago Rente Default port Points: 1403 More actions · Answer 3

I solved the problem by breaking the "Transform Data Task" into sub-tasks.

I.e., instead of copying all columns at the same time (faster), I copied the non-problematic columns with the standard "Copy Column" transformation.

For the problematic I used an ActiveX do to the copying and to avoid additional problems this copy does not select any "Source Columns" only selects the "Destination Columns".

The DTS is not slower but solved all my problems.

PS: For the time problem I solved it by selecting the column without using the column name but the position (in my case the Time is always the first column).