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.
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!