Post reply

Problem with incremental load of data in SQL Server 2008

  • December 1, 2011 at 8:32 am

    #247731

    Hello,

    I'm currently migrating SSIS packages from SQL Server 2005 to SQL Server 2008. All loads from the source system are incremental. I use following method in order to fill the target table:

    1) In execute SQL task editor: SELECT MAX(rowv) rowversion FROM tableX (target table)

    (=> ROWV is available in source table, data type = timestamp)

    2) Result set: Resultname = rowversion ./ Variable name = user::RowVersion

    3) In Data flow task:

    SELECT *

    FROM table (source table)

    WHERE (rowv > CAST(? AS BINARY))

    Parameter = User::RowVersion

    This works perfectly in SQL Server 2005, but not in SQL Server 2008. He returns always all records from the source table in the target table. What am I doing wrong? What's the difference between SQL Server 2005 and SQL server 2008 in this case??

    Thanks for the help

    Regards

    Hans

  • December 1, 2011 at 1:09 pm

    #1415534

    Anybody with an idea? The problem looks stupid and the solution will be simple... but I really don't see it...

    Thanks

  • December 1, 2011 at 1:20 pm

    #1415541

    I'm not sure what you are doing, your pseudocode is not making sense to me except number 1. Please include DDL of both tables, sample data, and actual SQL syntax for your queries.

    Thanks,

    Jared

    Jared
    CE - Microsoft

  • December 1, 2011 at 1:28 pm

    #1415544

    Hans80 (12/1/2011)

    Anybody with an idea? The problem looks stupid and the solution will be simple... but I really don't see it...

    Thanks

    What is the variable type you're using to store the mid-term timestamp binary value at the package level? It sounds like on conversion it's turning itself into a 0. Also, what SP are you up to in SQL 2k8 (I assume non-R2)? There were some early bugs and it might be affected by that.

    There was no significant change to the timestamp datatype between 2k5 and 2k8 so my guess is it's an SSIS environmental issue. I'd also double check the output settings on the execute sql task and perhaps put in a script component with a msgbox of the variable to confirm that it's being set accurately.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • December 1, 2011 at 1:30 pm

    #1415546

    p-nut (12/1/2011)

    I'm not sure what you are doing, your pseudocode is not making sense to me except number 1. Please include DDL of both tables, sample data, and actual SQL syntax for your queries.

    Heh, Jared, there's no T-SQL problem here. It's values being passed between SSIS components so the DDL and whatnot isn't going to be helpful here. I think you have to work in SSIS regularly to realize how much of a wall-banger some of these silly problems can be.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • December 1, 2011 at 1:32 pm

    #1415548

    Evil Kraig F (12/1/2011)

    p-nut (12/1/2011)

    I'm not sure what you are doing, your pseudocode is not making sense to me except number 1. Please include DDL of both tables, sample data, and actual SQL syntax for your queries.

    Heh, Jared, there's no T-SQL problem here. It's values being passed between SSIS components so the DDL and whatnot isn't going to be helpful here. I think you have to work in SSIS regularly to realize how much of a wall-banger some of these silly problems can be.

    Yeah, its been awhile since I have used SSIS... I do remember though 😛

    Jared

    Jared
    CE - Microsoft

  • December 6, 2011 at 1:27 am

    #1417129

    Evil Kraig F (12/1/2011)

    Hans80 (12/1/2011)

    Anybody with an idea? The problem looks stupid and the solution will be simple... but I really don't see it...

    Thanks

    What is the variable type you're using to store the mid-term timestamp binary value at the package level? It sounds like on conversion it's turning itself into a 0. Also, what SP are you up to in SQL 2k8 (I assume non-R2)? There were some early bugs and it might be affected by that.

    There was no significant change to the timestamp datatype between 2k5 and 2k8 so my guess is it's an SSIS environmental issue. I'd also double check the output settings on the execute sql task and perhaps put in a script component with a msgbox of the variable to confirm that it's being set accurately.

    Hi,

    I'm working with SQL Server 2008 R2 (version 10.50.2500.0). The data type of the rowversion in my target table is Binary(8). The data datype of the variable is STRING, but this will be converted in a binary value.

    When I look in the SQL profiler of the source server, he always returns this:

    RPC: Completed

    Exec sp_execute 2,0x000000000000000

    So I suppose the source table will take 0 as a maximum rowversion. Is this right? What can I do to take the correct maximum rowversion?

    Rgds,

    Hans

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

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