Please suggest

Question

Please suggest

susmitha117

SSC Eights!

Points: 890
More actions
July 15, 2014 at 7:46 am

#397058

Hi,
we get data every week in ms access. It doesn't come in incremental form but comes in full database. I created a ssis package to bring Msaccess data to sql table. To do that i am taking the main table backup and truncating the main table and uploading that with new data.
Can i compare the data and load only the incremental data instead of truncating the table. What are the best ways to do..
Please suggest
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

gbritton1 SSCertifiable Points: 6522 More actions · Answer 1

You can add a Lookup transformation to your dataflow. Lookup the incoming rows in the database based on whatever makes them unique. Only insert to the database those that do not match. Consider the partial cache option on the Lookup.

BTW, be sure the destination table is indexed on the lookup columns, or else expect sub-par performance.

susmitha117 SSC Eights! Points: 890 More actions · Answer 2

Thanks gbritton1. I would work on that and when the new file comes in. I lost the mappings in ssis. How can i maintain the mappings. Everytime i get the new file i have to manually map the column names

MMartin1 One Orange Chip Points: 27779 More actions · Answer 3

Hi, by using the Lookup transform, you will be only inserting rows that had no match.

So to do this in the Lookup transformation editor> general ...

Specify how to handle rows with no matching entries = redirect rows ...

From here drag the red (not green) pipeline arrow to a destination adapter that points to your SQL table. Do consider partial cache or no cache, if your data is large. Normally a error gets thrown if a lookup is not made but here we are re-directing those rows and considering them as new. This could potentially cause duplication in the destination table if the full cache simply could not hold the all the lookup records.

In the Lookup you simply drag the two fields you want to join on , one to the other. You can pull other columns, it wont matter since the normal behavior will not occur, only not matched records are handled.

Also, is the existing data in your sql server table updateable? If so you have another issue here that this simple solution will not address. If a record does not exist in the Access file but does in the sql server , do you delete it from the destination? Mark it as obsolete with a bit field? Another thing to consider so do pay attention to the business rules regarding this ETL.SSIS has a SCD (slowly changing dimension ) transform to learn about in this case.

Best.

----------------------------------------------------

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 4

susmitha117 (7/15/2014)
Thanks gbritton1. I would work on that and when the new file comes in. I lost the mappings in ssis. How can i maintain the mappings. Everytime i get the new file i have to manually map the column names

How can you lose the mappings? Are you re-running the import wizard each time you get a new file, instead of running the SSIS package that was previously created?

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]