I am pretty inexperienced with SSIS, although I have a lot of experience with SQL and C # and other technologies.
I am converting a task that I wrote as a standalone C # console application into an SSIS package.
I have an OLEDB input source, which is an SQL command that collects certain data in a database, which I then pass to the Script Component Transform. I use input fields as parameters for an OAuth-based web service, which requires a lot of custom C # code. The web service returns an XML request, which includes many lines that must be displayed for each line of input.
My understanding of how the Script transform works is that it contains more or less than one line, one line.
I have a few questions here.
- Is it possible to use the source source this way? Or is there a better way to feed input strings to my web service?
- Does the Script component convert the correct tool to use here? I cannot use a regular web service because the web service is not SOAP or WCF and requires OAuth in the request. (or is there a way to use the web service component this way?)
- How to output more than one line for each line of input?
- Does SSIS support a way to retrieve XML results (containing multiple lines) and map them to output field lines in a Script transformation? I know there is a source of XML data, but it is not. I think something that takes XML input and splashes out rows of data.
UPDATE:
The data from the web service is as follows:
<user>
<item>
<col1>1</col1>
<col2>2</col2>
<col3>3</col3>
</item>
<item>
<col1>1</col1>
<col2>2</col2>
<col3>3</col3>
</item>
....
</user>
, SQL DataSource . Script -. - XML, "" , script.
Script col1, col2 col3 , . xml. xml Script , xml (, XML, , , XML ).