December 17, 2008 at 11:06 am
Hi,
I have a package that takes an ACCESS data source table, does some validations and loads into a SQL table in the end. I'm writing a second package (same project) that takes a 2nd ACCESS table and loads in the data, but I only want to pull in the data from the 2nd ACCESS table where the PK exists in the SQL table that I just populated.
Does anyone have ideas on the best way to accomplish this?
Thanks,
Learning SSIS the hard way 😉
December 17, 2008 at 11:37 am
Do you know the PK at run time? Meaning are you using the PK from the Access database? Or are you inserting it into SQL and the PK is being created on PK identity? Knowing this would kinda give you a direction since you'd do it different depending on whether you already know the PK at run time.
I'm going to assume you know the PK from the Access DB at runtime. I'd create one package to handle both. That way you can leverage what you are inserting because you would have 2 source components (access table 1 and access table 2) in your package joined by the PK. So here's something that may be what you want to do:
In a data flow component drop data 2 source tasks for access table 1 and 2. Drop a multicast task and connect source 1 to the multicast task (creates a copy of dataset). Drop a merge join task and connect source 2 and the multicast to it joining by your PK
Now drop 2 destination components on the grid. Connect just the multicast to the destination 1. This'll send all of source 1 to destination 1. Connect the merge join task to destination 2. This will send only records where the PK between source 1 and source 2 match to destination 2
Thats it. Of course I left out whatever transformations and validation you'd need to do but for the most part that looks like all you'd need to do.
Hope that helps!
Thanks,
Strick
December 22, 2008 at 1:02 pm
This is an interesting way to address it. It doesn't do exactly what I need it to do, but since I hadn't thought of a dropping in multiple data sources before it opened some doors to me.
If nothing else it helped me think out of the box. Thanks a million,
Not so perplexed anymore...Sabrina:)
December 22, 2008 at 2:32 pm
Hi,
Great, glad I was of some help. That's the beauty the about SSIS. It really lets you think outside the box. I'm a programmer by trade so that really lets me be creative with what I'm able to do in SSIS. In my opinion I feel there's nothing I can't do in terms of data movement with SSIS.
If you're not already, I'd recommend really aquainted with VB.NET because it will really open up how you use SSIS (script components and script tasks). SSIS can't do everything out the box, but when combined with the cusomization of a programming language, the sky's the limit. And if you're using version 2008 you can use either VB or C#
Thanks,
Strick
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply