Post reply

DTS trailer

  • July 11, 2005 at 4:28 pm

    #60157

    I am writing a DTS which pulls the data from an Oracle database by a query and writes it into a text file. Now, I want to add a trailer record with how many records were transferred. How do I do that? I am using SQL server 7.0

    Thanks in advance.

     

  • July 11, 2005 at 5:26 pm

    #573172

    Maybe this could be considered cheating but it would work :

    Select col1, col2, col3 from dbo.OracleTable

    UNION ALL

    Select cast(count(*), null, null as SameDataType as col1) from dbo.SameQuery

  • July 11, 2005 at 6:35 pm

    #573185

    I like Remi's solution. Might want to add another column to order by

    Select col1, col2, col3, 1 'ord' from dbo.OracleTable

    UNION ALL

    Select cast(count(*), null, null as SameDataType as col1), 2 'ord' from dbo.SameQuery

    order by ord

  • July 11, 2005 at 7:33 pm

    #573194

    Little error in my solution... the nulls should be OUTSIDE the cast .

    UNION ALL

    Select cast(count(*) as SameDataType) as col1, null, null from dbo.SameQuery

    Hey Steve I taught that the union all was just concatenating the 2nd resultset. Have you experienced a situation where an unexpected order by screwed up the data (ya I know that you must specify an order by if you need the data in a certain order )?

  • July 12, 2005 at 7:49 am

    #573362

    I know that you can also grab that info with a Multiphase Data Pump . Lookup Multiphase Data Pump Functionality in Books Online. You can write footer information, etc. with the Pump Phase Complete step, so you would have the DTS process itself record how many rows were processed. You can also do this on a batch basis (with the Batch Complete phase).

  • July 12, 2005 at 1:02 pm

    #573639

    Thanks for all your replies.

    With Steve and Remi's solutions, I see a problem, we are concatenating two query results; I guess it works as long as the number of records stays the same. However, if the database is adding more records and each query takes its own sweet time, the numbers may not match. I haven't tested this, DTS is still executing, I would post the results.

    Looks like multiphase datapump feature is available from 2000 version onwards, as we are still using version 7, I am not having any luck with it...

     

     

  • July 12, 2005 at 1:11 pm

    #573645

    Well you could always insert the results in a temp table, extract the count from there and reselect the results... but that obviously is somewhat slower. Now it depends on what is the most pressent need: accuracy or speed.

  • July 12, 2005 at 1:23 pm

    #573654

    Though that is very resource intense(I hate to write a million records to temporary table, though I am gonna truncate it), I would go in that direction, as accuracy is needed for this task.

    I appreciate all your help...

  • July 12, 2005 at 1:39 pm

    #573657

    Alright... good luck with the rest of the project.

Viewing 9 posts - 1 through 8 (of 8 total)

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