Getting Weather Report Using SSIS

Getting Weather report using SSIS through WEB Services.

URLS:

 Weather Web Service URL: http://www.webservicex.net/globalweather.asmx?WSDL

 WSDL File Data URL: http://www.webservicex.com/globalweather.asmx?wsdl 

For this Follow Below Simple Steps:

1) Create New SSIS Package.

2) In Control flow Drag the Web Service Task and configure the Connection. For that Double Click on Web service task u will get below screen

3) Click on HttpConnection u will Get  below Screen and Paste the Weather Web Service URL(URL Located in top) and click on Test Connection then click ok.

4) Next Click on WSDL file , for that first copy XML Data from Second URL( http://www.webservicex.com/globalweather.asmx?wsdl ) and paste in notepad and save as .WSDL  and map that file here.

 5) After adding the WSDL file goto Input tab and Select the Service,Method and give the input values as Variable(Create variables).Follow the below screen.

6) And finally Store the Weather Report in Variable.

7) Now Add Execute SQL Task in Control Flow and connect to the data base for getting the City and Country information from table (Find the table and data Script below) and paste this (Select City,Country  from CityandCountry where id=?) in SQLStatement.

8) Now Goto Prarmeter Mapping Tab and create a int type variable(Follow Below screen).

9) Next Goto Result Set tab and Map the Result to City and Country Variables.

10) Drag one more Execute SQL Task this is for Storing the data to Data base tables(Find the table Script below) and paste this ( INSERT INTO [dbo].[WeatherReport]

           ([Id]

           ,[City]

           ,[Country]

           ,[WeatherReport])

values(?,?,?,?) ) in SQLStatement(Follow the below screen).

11) And Goto Parameter Mapping and Again map all the variables(Follow Below screen).

12) And Finally Add ForLooooP to Control Flow and Configure this like Below.

13) And Execute the Package and check the Database table, u will found the XML formated Weather report. from that u can get the Weather Report Info.

Scrip for Tables:

CREATE TABLE [dbo].[CityandCountry](

[ID] [int] NOT NULL,

[City] [varchar](50) NULL,

[Country] [varchar](50) NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[WeatherReport](

[Id] [int] NOT NULL,

[City] [varchar](50) NULL,

[Country] [varchar](50) NULL,

[WeatherReport] [varchar](max) NULL,

[DateOfReport] [datetime] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

INSERT [dbo].[CityandCountry] ([ID], [City], [Country]) VALUES (1, N'Hyderabad', N'INDIA')

GO

INSERT [dbo].[CityandCountry] ([ID], [City], [Country]) VALUES (2, N'Delhi', N'INDIA')

GO

ALTER TABLE [dbo].[WeatherReport] ADD  CONSTRAINT [DF_WeatherReport_DateOfReport]  DEFAULT (getdate()) FOR [DateOfReport]

GO

Thanks For Visiting the Blog

Santosh