Where to start?

Question

Where to start?

andysfreeemail

Mr or Mrs. 500

Points: 563
More actions
September 28, 2009 at 5:58 am

#135441

I'm trying to import data from an Access database into my SQL database. This will be a regular task and needs to do an Insert into, Update of or Delete from my target table depending on whether the record in the source table matches that in the target database.
Long time since I've done DTS, and SSIS is beating me a little. What tasks etc, can I use to check whether the record exists in my target table, and whether it has changed since the last import?
Sorry for tha basic question but any help appreciated.

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

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

Jaji03 SSC Enthusiast Points: 139 More actions · Answer 1

The best one that I can think of in SSIS is the "Lookup" task in the "Data Flow Transformation" section within the "Data Flow Task". It's not as straight forward, so it may be challenging for a beginner.

If you're a beginner, it might be best to export the data to a Stage table in SQL Server database and then execute a stored procedure to complete any tasks you'd like.

BTW, a Stage table is simply a "normal" table, that is used only for the purpose of "staging" the table before it reaches its hopefully normalized table (sadly, I was confused by this term when I was new to SQL Server). 🙂

P.S. Not sure if the table has a "TimeModified" column or any other types of "Flags". This makes it much easier to determine what rows needs to be changed/updated/added.

TangoVictor SSChampion Points: 10975 More actions · Answer 2

Here's my technique:

Read in the new data. Make sure you have a key that exactly matches the format / datatypes of the existing data, if not use conversions / derived columns.

using another data source read your existing data then sort both inputs by the same key values and use a merge join setup as a left outer. Your new data should be the left input. If your data types match and your sorting was done correctly the merge join will connect the fields.

select all the fields for your new data and just 1 key field of your existing data

On the output of this use a conditional split with the formula

ISNULL(RightTableKeyField)

Use this output from the conditional split as your "new rows". Any row where that column is not null indicates a match was found.

Typically the merge join will have better performance than the lookup.

Jaji03 SSC Enthusiast Points: 139 More actions · Answer 3

FYI: This article explains both methods. Hope this helps...

http://www.sqlis.com/post/Get-all-from-Table-A-that-isnt-in-Table-B.aspx

andysfreeemail Mr or Mrs. 500 Points: 563 More actions · Answer 4

Thanks to both. Used a staging table with a stored procedure in the end and it works very nicely.

Thanks again.A.