November 10, 2015 at 1:06 am
Hi,
Am a newbie in SSIS and need help with following:
Have a load and the last step in the Control Flow I update LastImportDate in a Table
update ParamSettings set
value = convert(date,getDate(),120)
where ParamID = 1 and Parameter = 'LastImportDate'
Next time I run the SSIS package I want to check LastImportDate value in that table and only load Incremental data.
Source and destination databases are on different servers
Have tried with Lookup but can´t understand how to check one cell for all rows in source table.
Have search google, but cant find any solution, I hope I have give enought info.
Thanks
Aikman
November 10, 2015 at 1:14 am
aikman2222 (11/10/2015)
Hi,Am a newbie in SSIS and need help with following:
Have a load and the last step in the Control Flow I update LastImportDate in a Table
update ParamSettings set
value = convert(date,getDate(),120)
where ParamID = 1 and Parameter = 'LastImportDate'
Next time I run the SSIS package I want to check LastImportDate value in that table and only load Incremental data.
Source and destination databases are on different servers
Have tried with Lookup but can´t understand how to check one cell for all rows in source table.
Have search google, but cant find any solution, I hope I have give enought info.
Thanks
Aikman
Just because you have a table containing last import date does not necessarily mean that you can perform incremental loads. In order to do that, you need a 'date last modified' column in your source data and you can compare that with your last import date (and make sure that there cannot be a gap between the two).
November 10, 2015 at 1:21 am
Hi Phil,
I have two columns in my source data, Created and Change that I want to check against.
I have succed it with a Stored Procedure:
where
cast(o.created as date) >= dateadd(day, -1, @lastImportDate ) or cast(o.changed as date) >= dateadd(day, -1, @lastImportDate )
But I read that if I have Source and Destination on different servers it could be performance problem to use T-SQL and linked server.
So I wounder if it is possible to do this with a SSIS Lookup or some other Transform.
I understand how to do this if I have a column at destination table with import dates that I can compare against source created and changed columns, but now i just have one cell that I need to check every source rows against.
One more thing, the destination table is in Stage and it is truncated as the first step in SSIS package.
November 10, 2015 at 2:38 am
aikman2222 (11/10/2015)
Hi Phil,I have two columns in my source data, Created and Change that I want to check against.
I have succed it with a Stored Procedure:
where
cast(o.created as date) >= dateadd(day, -1, @lastImportDate ) or cast(o.changed as date) >= dateadd(day, -1, @lastImportDate )
But I read that if I have Source and Destination on different servers it could be performance problem to use T-SQL and linked server.
So I wounder if it is possible to do this with a SSIS Lookup or some other Transform.
I understand how to do this if I have a column at destination table with import dates that I can compare against source created and changed columns, but now i just have one cell that I need to check every source rows against.
One more thing, the destination table is in Stage and it is truncated as the first step in SSIS package.
No need for any linked servers. Use OLEDB connections in SSIS.
Step 1: Get last import date into a variable using an ExecuteSQL task.
Step 2: Feed that variable into a data flow task to select your source data.
Leave o.created as a datetime rather than casting to date, if you can. It will improve performance.
November 11, 2015 at 2:41 am
Hi Phil and thanks for your help, but do you think you can explain in more detail how to do Step 2?
regards
Aikman
November 11, 2015 at 6:17 am
Sure.
Let's assume your source proc is called dbo.proc and it has a parameter called @MaxDate.
Point your OLEDB data source at your source database and in SQL commend text, enter:
exec dbo.Proc @MaxDate = ?
Click on the Parameters button.
In the Mappings section, you'll need a row that maps your variable to the parameter:
@MaxDate | User::MaxDate | Input
where User::MaxDate is your SSIS variable.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply