Problem incremental load data in SQL Server 2008

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem incremental load data in SQL Server 2008

Author

Topic

Hans80
Starting Member

10 Posts

Posted - 2011-12-06 : 04:00:57

Hello,

I'm currently migrating SSIS packages from SQL Server 2005 to SQL Server 2008 R2 (version 10.50.2500.0). 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 table X (target table)
(=> ROWV is available in source table, data type = timestamp)

2) Result set: Resultname = rowversion / Variable name = user::RowVersion (data type = string)

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.

When I look in the SQL profiler of the source server, he 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?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 04:29:31

i think better way of doing this is to add a variable to hold sql query and in expression use like

"SELECT *
FROM table (source table)
WHERE (rowv > CAST(" + @[user::RowVersion] + "AS BINARY))"

then use sql command from variable for data flow task source

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hans80
Starting Member

10 Posts

Posted - 2011-12-06 : 06:39:24

What do you mean with "then use sql command from variable for data flow task source"?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 06:47:30

you've property sql command from variable for oledb source task in data flow. you need to select it and map source variable as variable you created to hold the sql query in package

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hans80
Starting Member

10 Posts

Posted - 2011-12-06 : 07:26:07

quote:
Originally posted by visakh16

you've property sql command from variable for oledb source task in data flow. you need to select it and map source variable as variable you created to hold the sql query in package

I've created a new variable with the expression:

"SELECT *
FROM table (source table)
WHERE (rowv > CAST(" + @[user::RowVersion] + "AS BINARY))"

When I validate the expression, I receive the message Expression cannot be validated. The expression might contain an invalid token, an incomplete token or an invalid element.

Do you know what the problem is?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 07:30:52

whats the datatype of rowversion?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hans80
Starting Member

10 Posts

Posted - 2011-12-06 : 07:39:19

quote:
Originally posted by visakh16

whats the datatype of rowversion?

In the source table it's a timestamp, in my target table a binary(8) and in the variable a string. But that part works now.

When I select in the OLE DB Source SQL command from variable and I select the new variable, I get this in the variable value text box:

SELECT
FROM table
WHERE rowv > CAST( AS BINARY)

Errors: Statements could not be prepared & Incorrect syntax near AS

...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 10:23:34

that means the variable that you use is not getting the correct values. check the step which populates it ie execute sql task

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hans80
Starting Member

10 Posts

Posted - 2011-12-06 : 10:50:25

If I run the step in the execute sql task, he's giving me the correct maximum rowversion... I don't see it anymore ;-)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 10:56:32

quote:
Originally posted by Hans80

If I run the step in the execute sql task, he's giving me the correct maximum rowversion... I don't see it anymore ;-)

then its losing the value at some point. see where its losing value by putting variable in watch window and stepping through using breakpoints

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hans80
Starting Member

10 Posts

Posted - 2011-12-07 : 04:34:47

quote:
Originally posted by visakh16

quote:
Originally posted by Hans80

If I run the step in the execute sql task, he's giving me the correct maximum rowversion... I don't see it anymore ;-)

then its losing the value at some point. see where its losing value by putting variable in watch window and stepping through using breakpoints

In the Execute step, he's ging me indeed a 0 as maximum rowversion...
However, when I run the same code in Mgtm Studio, he's giving me the correct value...

So, to summarize:

Code:
SELECT MAX(rowv) rowversion FROM table X

Result set: Resultname = rowversion / Variable name = user::RowVersion (data type = string)

Result in watch window = 00000000
Result in mgtm studio = correct value

Any idea?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-07 : 04:48:09

that means you've not set up variable mapping correctly in variables tab.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hans80
Starting Member

10 Posts

Posted - 2011-12-07 : 04:51:07

quote:
Originally posted by visakh16

that means you've not set up variable mapping correctly in variables tab.

Yes, I know... but which is the correct mapping to use? He has to give me the max(rowversion) of the target table. In the target table, the type is a Binary(8). Which type should I choose for the variable?

Hans80
Starting Member

10 Posts

Posted - 2011-12-07 : 05:27:39

quote:
Originally posted by Hans80

quote:
Originally posted by visakh16

that means you've not set up variable mapping correctly in variables tab.

Yes, I know... but which is the correct mapping to use? He has to give me the max(rowversion) of the target table. In the target table, the type is a Binary(8). Which type should I choose for the variable?

If I convert the max(rowversion) to a string, he's giving me the correct value. But the rowversion in my source table is a timestamp, so my "WHERE (rowv > CAST(? AS BINARY))" is not working anymore

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-07 : 05:33:10

then that means its a t-sql query issue

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hans80
Starting Member

10 Posts

Posted - 2011-12-08 : 04:59:27

quote:
Originally posted by visakh16

then that means its a t-sql query issue

No sorry, problem still not solved and I don't see it anymore...

It works to store the max roversion only as a VARCHAR in a variable (data type = string).

But in the OLE DB Source editor, it's not possible to do the comparison with the rowversion from the source table (stored as a timestamp)...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 07:00:53

for appending value to query you need to cast it to string type using DT_STR or DT_WSTR

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 07:01:03

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources