Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SSIS package for daily SQL import from Access

Author  Topic 

sccrsurfer
Starting Member

43 Posts

Posted - 2013-01-29 : 17:08:12
To start, I'm terribly new at SQL. Now what I'm looking for is not (necssarily) SQL query code, though if it's what I need to complete my task I'll gladly take the help. My question case and question are below:

I have an Access database that is updated daily by users, say about 25,000 records per day are added. No existing records are modified. The database has only one table called "data." It's really nothing special. There is a uniqueID field in this database. Each day, I want SQL to automatically INSERT all records from this Access table WHERE the uniqueID is <> to existing uniqueID's in the SQL database / table (which are imported over from the Access data).

Here is what I would like to achieve in SQL psuedo code, and I'm hoping you pros can help me fill in the blanks. Will I achieve this through code or through a package in SSIS? If a package, how do I develop one to specifically exceute at certain times every day and use the criteria set in the psuedo code below?

INSERT *
FROM [Microsoft Access].[Database1].[data]
WHERE uniqueID <> uniqueID [SQL].[destination]

Thank you all in advance. I know there must be a better way than overwriting 6+ million records per day and just inserting the 25,000 new ones!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-01-29 : 18:13:06
For the SSIS, you can schedule it as part of job in SQL that runs however often you want it. To insert only the new records, add a Lookup component to your SSIS package. The first step will be to extract everything from Access, and then pass that along to the Lookup operator. The lookup operator has 2 exit paths, matched and not matched. The not matched path would then lead to your SQL destination. Even better would be to add an Execute SQL task to the control flow that get the max(uniqueID), assuming that it's an identity, the your extract from source would look like

SELECT *
FROM [Microsoft Access].[Database1].[data]
WHERE uniqueID > ? -- with the ? being the parameter found in your Execute SQL task.

Hope this helps, I'd start by Googling the Lookup Component to learn how to fully utilize it

Jim


Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

sccrsurfer
Starting Member

43 Posts

Posted - 2013-01-30 : 08:28:09
Thanks Jim. That's great info, and I researched LOOKUP components. Where would I place this script once written? In Business Intelligence Studio?

I'm so new, I have trouble navigating the GUIs in addition to not knowing proper SQL jargon and what operations will perform the desired tasks.
Go to Top of Page

sccrsurfer
Starting Member

43 Posts

Posted - 2013-01-30 : 08:53:28
So I'm in the LOOKUP transformation editor. How do I make it so that it INSERTS instead of overwrites?
Go to Top of Page
   

- Advertisement -