Post reply

DTS Global Variable doesn''t change value after being mapped to an output parameter!

  • August 6, 2007 at 1:29 pm

    #175811

    In my DTS package I added a step to do a record count of a raw txt file and then compare that with the record count from the table. Since the filename changes every time the package runs I cannot use

    select count(*) from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=D:\Packages\NPD\Data;','select * from TotalPCs.csv') where you have to specify the filename. So I used xp_cmdshell; but since xp_cmdshell is only in master db and i need to get the record count from table from a different db I have to do the two counts separately in two separate execute sql tasks. So the output parameter from the first task can be used as input parameter in the next task.

    In the first task (master db is the connection) i put this in the sql statement window:

    ---------------------------------------------------------------------------------------------------------------------------------

    DECLARE @ReturnValue int, @CountRaw int

    EXEC @ReturnValue=p_Compare_Count_For_PriceGrabberDaily_DailyProductDetails @CountRaw OUTPUT

    SELECT @ReturnValue AS ReturnValue

    -----------------------------------------------------------------------------------

    I then clicked Parameters and selected the Output Parameters tab and selected the Output Parameter Type of Row Value and then mapped it to a global variable I defined. But after I executed this task and went to check the value of the global variable. It didn't change. But when I execute the calling SQL statement in Query Analyzer it returns the correct value.

    I tried the simple test with the example on http://www.sqldts.com/234.aspx and it worked. So can someone please tell me why the DTS global variable which is mapped to this output parameter doesn't change value? Something wrong with my stored procedure? THANKS A LOT!

    My SP is like this:

    -------------------------------------------------------------------------------------------------------

    create    procedure p_Compare_Count_For_PriceGrabberDaily_DailyProductDetails

    @CountRaw int output

    AS

    DECLARE @CountFromRawData INT,

            @InitialCountFromRawData INT

                   

    delete from RawData

    INSERT INTO RawData

    exec xp_cmdshell 'type D:\Packages\PriceGrabber\download\*.txt'

    select @InitialCountFromRawData = count(*) from RawData

    select @CountFromRawData = @InitialCountFromRawData - 6

    set @CountRaw = @countfromrawdata

    return @CountRaw

    -------------------------------------------------------------------------------------------

  • August 7, 2007 at 1:26 am

    #725029

    I assume you are talking about DTS and not SSIS. This is a SSIS forum. You'd be better off posting in the DTS forum.

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

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

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