1.Control Flow:Full Business Logic.
2.Data Flow:
i.Real "Source" to "destination" data flow happens.
ii."Transformations" before inserting into Destination.
OLEDB Source
Script Component
Derived Column (enable "Data Viewer")
3.In this lesson "Script Component Transformation" example is shown.
i.In "Script Component Transformation" , one can choose as "Source","Destination" or "Transformation".
ii.Choose "Transformation".
Script Language:Visual Basic 2010
or Microsoft Visual C# 2010
Mapping
Input
Output
Create any "output column"
Choose button "Edit Script" -
Navigated to "Microsoft Visual Studio".
Public strvar1 As String
Public strvar2 As Decimal
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'Add Code here
If condition Then
.....
Else
....
End If
.....
End Sub
End Class
------------------------------------------------------------------------------------------------------------
Source:Consuming a web service in SSIS 2
Data Flow:
Script Component( prompts: choose "source")
Right Click( on above component)
1.(Custom Properties:script Language: dropdown(Microsoft Visual Basic 2005,Microsoft Visual C# 2005)- click on "Edit Script" button,it lauches the Visual Studio.
i.Define "Output Column":what the webservice returns (name,city etc)
Note:In 2008 version the "Script Language" is new)(Lauching Visual Studio is also new).
2.( takes to Visual Studio: Reference:"Add Web Reference" (prior to this :copy the url from web service) - click(Add Reference)
i.in visual studio the main.cls click the method(CreateOutputRows)
To use it ass
using (click on object explorer tab, copy the webservicename)
using system.xml;
...
Note:Inside method(CreateOutputRows)
Note:Define the object of that WebService like
train mytrain= new train();
string myresponse= mytrain.gettraindestination(xyzparam);
Note:In 2008 version "Add Web Reference" also new.
Note:It adds as a 'Proxy Class',enable it communicate with that Web Service.
Run to build it.
Back to BIDS.
3.Add component "Row Count" and chk. Variable and a "Data Viewer", see the data.
------------------------------------------------------------------------------------------------------------
Source:SSIS 2008 Series:Using Webservices in SSIS Packages
1.Start BIDS:just create a SSIS project.
2.In Visual Studio: create a Project ->"ASP.NET Web Service Application" project.
i.change say the message
...
[WebMethod]
public string Hello World( )
{
return "Hello World";
}
a. Run the package , it will return the XML
ii.The Web Service is created
Service1
HelloWorld
Invoke
Click on invoke , will show the string.
Change the url adding at the end "WSDL" : http://localhost:55345/Service1.asmx?WSDL
give the whole - XML
Click on the "Service Description" , save it as a link ( in a folder WebSrvr.asmx)
Note:Generally before using the Web Service , it's hosted in IIS, but for this example (Visual Studio running & Dev. running )
3.In BIDS create new connection :(HTTP)
Server URL:WSDL path( the step 3 link which is saved , rename it to WSDL)
( you can use proxy but for now the above)
4.Now in "Control Flow" put task "Web Service Task" , specify the connection (HTTP one), specify the WSDL file,set "OVerwriteWSDLFile:True"
Input:
Service:Service1
Output:
OutputType: File Connection ( Can be "variable" also.)
Create File
Note:If you put in variable using "Script Task" - echo the variable value.
------------------------------------------------------------------------------------------------------------
