January 13, 2012 at 11:42 am
I have a ssis that step1 is to load flat files sources to a staging database.
Then step2 is load data from staging to production database.
In my ssis from staging database to production, I have some stored procedures setup that is used for loading the data. My question is where should I put this stored procedure, in the staging database, or in the production database, basically these stored procedures are merge statments to compare tables in staging and production to do incremental load, my ssis will call a sql task that calls this stored procedure.
Thanks
January 13, 2012 at 11:54 am
I'd put them in the staging database, just to keep it self-contained. Easier to document that way.
- 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 13, 2012 at 12:07 pm
Do you have backup of your staging database ?
Do you have a repository with all your staging database script ?
If both answers are no, I'll put that into production database.
Otherwise, staging database.
January 13, 2012 at 12:15 pm
azdzn (1/13/2012)
Do you have backup of your staging database ?Do you have a repository with all your staging database script ?
If both answers are no, I'll put that into production database.
Otherwise, staging database.
I'm so used to using source control that not having the procedures saved somewhere didn't occur to me as a possibility.
Definitely make sure they're saved, along with anything else from the staging database (table definitions, any static lookup table content that's not part of the load data, etc.).
- 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 13, 2012 at 12:29 pm
I'm so used to using source control that not having the procedures saved somewhere didn't occur to me as a possibility.
Just in case 🙂
January 13, 2012 at 12:32 pm
isn't it more efficient to process the load all in the SSIS pipeline?
if you are just calling stored procedures from the SSIS package then you might as well just skip SSIS and run the procedures from a SQL agent job. or am I missing something?
January 13, 2012 at 2:07 pm
andersg98 (1/13/2012)
isn't it more efficient to process the load all in the SSIS pipeline?if you are just calling stored procedures from the SSIS package then you might as well just skip SSIS and run the procedures from a SQL agent job. or am I missing something?
The packages have other tasks other than SQL tasks, and it has parent pacakges, and 30 child packages. So calling from SQL agent job just for that stored procedure is not what I want.
Thanks
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply