Post reply

DTS handling multiple CSV file format

  • 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.

  • August 18, 2004 at 4:56 am

    #519539

    1. Bring the data into a single column SQL table and PARSE away
    2. 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

  • August 19, 2004 at 7:51 am

    #519785

    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.

  • August 20, 2004 at 2:58 am

    #519961

    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).

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

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