I'm pretty inexperienced with SSIS, though I have much experience in SQL and C# and other technologies.
I am converting a task I have written as a stand-alone c# console app into an SSIS package.
I have a OLEDB input source that is a SQL command, this collects certain data in the database that I then feed into a Script Component Transform. I use the input fields as parameters to an OAuth based restful web service, which requires a lot of custom C# code to accomplish. The web service returns an XML respose that includes many rows that must be output for each input row.
My understanding of how the script transform works is that it's more or less one row in, one row out.
I have several questions here really.
- Is it a good idea to use the input source this way? Or is there a better way to feed input rows into my web service?
- Is a script component transform the correct tool to use here? I can't use a normal web service because the web service is not SOAP or WCF based, and requires OAuth in the request. (or is there a way to use the web service component this way?)
- How can output more than one row for every input row?
- Does SSIS support a way to take the XML results (that contain multiple rows) and map them to the rows of the output field in the script transform? I know there's an XML Input source, but that's not really this. I'm thinking something that takes XML input and spits out rows of data
UPDATE:
Data from the Web Service looks like this (extra cruft elided):
<user>
<item>
<col1>1</col1>
<col2>2</col2>
<col3>3</col3>
</item>
<item>
<col1>1</col1>
<col2>2</col2>
<col3>3</col3>
</item>
....
</user>
Essentially, the SQL DataSource returns a dataset of of users. The users dataset is fed into the script and used as parameters for the web service calls. The web service calls return a set of XML results, which have multiple "rows" of data that must be output from the script.
In the above data, the outputs of the script would be multiple rows of col1, col2, and col3 for each user supplied in the input source. I need a way to extract those elements and put them into columns in the output buffer for each row of xml data. Or, a way to simply make the xml the output of the script and feed that output into another component to parse the xml into rows (like an XML source does, but obviously you can't put an XML source in the middle of a data flow).