January 9, 2012 at 12:31 pm
I have a staging database and also a production database.
I also have two ssis package to load data.
One package is to import all flat files into staging databases
THe other package is to do incremental loading data from staging database to production database. I use SQL task to call a stored procedure that uses new Merge statement in SQL 2008.
The problem now is I found there are some foregin key and primary key constraints to check before doing the merge.
How can add that?
Thanks
January 9, 2012 at 1:30 pm
It really depends on what you're loading and how.
For example, if table A depends on table B, and you're just loading table A in your process, you could run a query before you load table A, which checks table B and either deletes or flags rows in the staging environment.
On the other hand, if you load A and B, you'll have to load B first, then check A.
But, if you load B, and then have to use values generated in that to load A, you'll need to somehow reference data back to the staging environment (most likely) so that you can tie values in B to the staging values that will be used to load A.
There are other possibilities. Can you provide a bit more data on how the load works?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 9, 2012 at 1:48 pm
I know I need to load the primary key table first, then load forign key table.
The problem is from the source flat files, sometimes they have data integrity issue, we cannot always to rely on them to prevent constraints violations.
I have two packages, one is to load from Flat source file to staging databases. I do some conversion here too.
I then use Merge statment to load data from staging database to production database.
I am told Merge statement in SQL 2008 is a really good tool to do incremental upload.
But I came to the difficulty to redirect constraints violations.
I wrote quite a few merge statement procedures to load each table. and the stored procedure is called in SSIS SQL task. For now, I still want to stick on using SQL merge, because I have done lots of work about that.and because it is a SQL task in ssis, there is no way to redirect errors like key constraints violations.
January 9, 2012 at 1:55 pm
Can you run an SQL task before the Merge, after the staging load, that checks for rows in the staging data that would violate the constraint?
Update dbo.MyStagingTable
set RowCheck = 'fail'
where ID not in (select ValidIDs from dbo.MyForeignKeyTable);
Something like that?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 9, 2012 at 2:11 pm
January 9, 2012 at 2:15 pm
sqlfriends (1/9/2012)
The problem is from the source flat files, sometimes they have data integrity issue, we cannot always to rely on them to prevent constraints violations.
Have you taken these concerns to the source(s) of the flat files?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 9, 2012 at 2:20 pm
GSquared (1/9/2012)
Can you run an SQL task before the Merge, after the staging load, that checks for rows in the staging data that would violate the constraint?
Update dbo.MyStagingTableset RowCheck = 'fail'
where ID not in (select ValidIDs from dbo.MyForeignKeyTable);
Something like that?
Thanks for that, I also thought of this. I have about 30 packages that call the SQL merges.
I think this is a good option for me, but just don't how to do this.
Currently I just do insert and update in the merge, so what SQL should I use for checking pk or fk violation, I see your example, then should I use a conditional split task to direct violation values.
January 9, 2012 at 2:26 pm
!Aaron Aardvark! (1/9/2012)
Perhaps these will help:http://www.kimballgroup.com/html/08dt/KU107_UsingSQL_MERGESlowlyChangingDimension.pdf
Thanks, the first article is certainly helpful for my future reference, but I just didn's see anywhere it mentions to handle pk or forign key constraints violations.
thanks
January 9, 2012 at 2:30 pm
sqlfriends (1/9/2012)
Thanks, the first article is certainly helpful for my future reference, but I just didn's see anywhere it mentions to handle pk or forign key constraints violations.
It's been some time since I touched SSIS, but aren't you performing a Lookup in the SSIS Data Flow to get dimension surrogate ids? The usual thing is to route rows that don't successfully find an id to some error flow, or there might be something to handle early-arriving facts or whatever they are called. Anyway, I don't know how sophisticated your SSIS flow is, but you might find MVP Andy Leonard's stairway to SSIS series helpful:
http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/75331/
January 9, 2012 at 2:40 pm
Thanks, I am not using look up.
I have read the series before, it becomes quite complicated by using those.
I also read somewhere ever since SQL server 2008 has the new Merge statement, it is a good tool for incremental load in many ways than using lookup along with others, for it can do all the upate and insert /delete in one batch , so I started my packages using that.
At this time, I really don't want to go back to change it to use Look up for my 30 packages.
I am aware of redirect errors is a way but Merge SQl task cannot do that, this is where I get stuck.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply