SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SSIS package for daily SQL import from Access
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sccrsurfer
Starting Member

USA
43 Posts

Posted - 01/29/2013 :  17:08:12  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 01/29/2013 :  18:13:06  Show Profile  Reply with Quote
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

USA
43 Posts

Posted - 01/30/2013 :  08:28:09  Show Profile  Reply with Quote
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

USA
43 Posts

Posted - 01/30/2013 :  08:53:28  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000