question

DharmanDave avatar image
DharmanDave asked

SSIS Package for incremental data insert

I want to insert data in a table from an Access (mdb) file. Only the data where date is greater than the maximum date present in the Destination table should be inserted. please suggest the best way to do it using SSIS.
ssissql-server-2008-r2
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
JohnM avatar image
JohnM answered
Here's how you can connect SSIS to Access: http://msdn.microsoft.com/en-us/library/cc280478(v=sql.105).aspx Once connected, you should be able to do two things: 1). You could write a specific query against that Access data source to filter out the appropriate date values or 2). You could import the data (the whole table) into a SQL table and then use native SQL commands/functions/queries to filter the data and insert the appropriate records into the table. I'm usually fan of option 2 only because I'm more comfortable in SQL Server, however, if the table is large, I'd go with option one and do the filtering on the front end. Just some options! Hope this helps!
12 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

So one way that you could do it is to pull the max date out of the destination table into a variable and then dynamically build a SQL Query around that variable. You can then provide that variable to the OLEDB data source in the data flow task by using the 'SQL Command from Variable' option in the Data Access mode. I've never tried this specifically, however the sql command should then filter the result set down to only the data that you want. At that point, the data flow will then transfer them into the destination table. Hope this helps!
3 Likes 3 ·
Does this help? http://www.sqlis.com/post/The-Execute-SQL-Task.aspx How many records are we talking about?
2 Likes 2 ·
If you did find the answer useful, then please return the favor by accepting the answer. This will also help the future users.
2 Likes 2 ·
Just re-reading your question, is this a one time run or are you looking to run it multiple times?
0 Likes 0 ·
Thanks John, the issue here is how do i compare dates in the Access data with the destination table dates ?
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.