February 4, 2015 at 6:53 am
We have data that comes from a 3rd party in an Excel spreadsheet. We have successfully loaded that data to a staging table and now we have to try and perform some data edits against it. Try matching by Name, SSN, and Birthdate...then pairing down by name and Birthdate if that doesn't match in our application database. Making sure there is only one and only one member match. Then making sure that these patients have "Diabetes" claims. Stuff like that. I know I could do the lookups via a Lookup. I'm just at a point where I don't know if I can do all of this through a series of OLE DB Commands using the SSIS GUI or to put all of this editing into a Stored Procedure and call it using an OLE DB Command. I guess I'd like to know what best practice is based on experience. I am a newbie and novice to SSIS and I just don't know how far to take this and what is the most efficient means to approach this.
In the SSIS World, where I am a newbie and a novice, do you try to do everything through the SSIS GUI? I know that's a real broad question. I just don't know whether to make my edits as OLE DB Commands or through one main Stored Procedure calling it from an OLE DB Command. I think at one point I was going to string my OLE DB Commands together with each edit check and I ran into a couple of problems...I didn't know if I could use raw SQL in the OLE DB Command. I sure don't want to create Stored Procedures for each edit check. And I think I ran into a problem trying to connect serial OLE DB Commands. I don't think SSIS and Visual Studio allowed me to do that. Sooooo I've sort of gone in a full circle on this. I am now doing my data edit checks in one Stored Procedure. Don't get me wrong...it's only 5 or 6 edits...but it seems to be handling it fine in the Stored Procedure. (Testing it now...) And then I'll implement that into my SSIS through an OLE DB Command within a Foreach Loop Container to treat it like a cursor.
I just don't know what the best practice is here.
Perhaps someone here can reply and give me their opinion as to what the best practice might be on something like this.
Thanks for your review and am hopeful for a reply.
February 4, 2015 at 7:40 am
ITBobbyP85 (2/4/2015)
We have data that comes from a 3rd party in an Excel spreadsheet. We have successfully loaded that data to a staging table and now we have to try and perform some data edits against it. Try matching by Name, SSN, and Birthdate...then pairing down by name and Birthdate if that doesn't match in our application database. Making sure there is only one and only one member match. Then making sure that these patients have "Diabetes" claims. Stuff like that. I know I could do the lookups via a Lookup. I'm just at a point where I don't know if I can do all of this through a series of OLE DB Commands using the SSIS GUI or to put all of this editing into a Stored Procedure and call it using an OLE DB Command. I guess I'd like to know what best practice is based on experience. I am a newbie and novice to SSIS and I just don't know how far to take this and what is the most efficient means to approach this.In the SSIS World, where I am a newbie and a novice, do you try to do everything through the SSIS GUI? I know that's a real broad question. I just don't know whether to make my edits as OLE DB Commands or through one main Stored Procedure calling it from an OLE DB Command. I think at one point I was going to string my OLE DB Commands together with each edit check and I ran into a couple of problems...I didn't know if I could use raw SQL in the OLE DB Command. I sure don't want to create Stored Procedures for each edit check. And I think I ran into a problem trying to connect serial OLE DB Commands. I don't think SSIS and Visual Studio allowed me to do that. Sooooo I've sort of gone in a full circle on this. I am now doing my data edit checks in one Stored Procedure. Don't get me wrong...it's only 5 or 6 edits...but it seems to be handling it fine in the Stored Procedure. (Testing it now...) And then I'll implement that into my SSIS through an OLE DB Command within a Foreach Loop Container to treat it like a cursor.
I just don't know what the best practice is here.
Perhaps someone here can reply and give me their opinion as to what the best practice might be on something like this.
Thanks for your review and am hopeful for a reply.
I think the answer to "do you try to do everything" is "it depends".
SSIS is good at moving large amounts of data around, and good at transformations on that data that it can perform synchronously to moving it. It also can be run on a separate server from your target which can be handy.
It is less good when it needs to do something individually for every row, sort things, or do other transformations that require it to read all the data first.
As for the example you listed, it still depends, but a procedure likely makes more sense.
If all you are doing is running OLE DB Commands, the target server is still doing all the work regardless of whether it is encapsulated in a procedure. So you'd only really want to do those commands separately in SSIS if you wanted take build in some advanced logging and error handling depending on the result of each command (which you can't do from the stored proc, nor easily do based on its results).
Also, FYI, OLE DB Command does not limit you to a single T-SQL statement. You can actually do multiple things within one OLE DB Command if needed (although, again, its probably just better for clarity to have it in a procedure).
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply