November 8, 2006 at 3:50 pm
I want to create a generic data flow that can be re-used across packages. Ideally, I would define a set of inputs and a set of outputs and I would be able to hook up fields from my current data flow to these inputs and outputs. For example, I might want to send in a state code, state name, and country code and have the process return a corrected state code, state name and country code. Ideally, this would be accomplished using native SSIS controls similar to how Informatica would allow a disconnected lookup. If I have to build a custom task then using SSIS is an obstruction rather than a valuable tool -> DBA’s should not have to write c#.
- tk
November 8, 2006 at 4:14 pm
"Ideally, I would define a set of inputs and a set of outputs and I would be able to hook up fields from my current data flow to these inputs and outputs. For example, I might want to send in a state code, state name, and country code and have the process return a corrected state code, state name and country code. "
If I understand you correctly, then you are trying to use SSIS to pass data to an object and return data from an object based on the information that you initially passed in. Am I going in the right direction?
If that's what you're trying to do, then you have a couple of options. You can use stored procedures in conjunction with either an Execute SQL Task or with the OLE DB Data Flow Source Component. You can populate the parameters required by the stored proc by using package variables. You could also use a stored procedure in conjunction with the Lookup Dataflow Component so that the values returned are determined by what you pass in. Then you could map the values returned by your stored proc to either a database object or a package variable. If you use a package variable then that value can be used in other transformations and/or as part of a precedent task.
If this sounds like what you are looking to do and if you need more info about those tasks, and SSIS help doesn't 'help', then let me know and I'll see what I can dig up.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply