SSIS ile Web Sayfasını Kaydetmek – Google’da Arama Yaptırmak

6. December 2010

Bazen öyle durumlar oluyor ki istek üzerine, SSIS ile web sayfasını tarayarak sabit diskinize kaydetmek zorunda kalabiliyorsunuz. Ancak SSIS içerisinde bunun için "out-of-the-box" bir özellik bulunmuyor. Dolayısıyla bu işlemi gerçekleştirmek için SSIS içerisinde bize inanılmaz bir esneklik sağlayan Scripting özelliğini kullanabiliriz.

Web sayfalarını sabit diskimize indirmek için .NET'in neredeyse sınırsız olanakları arasından IO ve NET kütüphanelerini kullanacağız. Ancak öncelikle yazdığımız kod içerisinde parametrik olarak kullanmak için iki adet değişken yaratalım.

Bu değişkenlerden ilki String data tipinde "sURL" olsun ve burada paketin ziyaret etmesini istediğimiz web sitesinin adresini tutalım.

İkincisi ise ikinci örneğimizde kullanmak üzere HTML kodunu tutmak için string tipinde "sHTML" olsun.

 

İlk Örnek: Web Sayfasını Kaydetmek:

 

Yapacağımız işlem kaydetmek istediğimiz url'leri veritabanından alıp, object tipinde bir değişkene atayıp, for each loop ile arama dizisi üzerinden dönüp her bir değer için script task'ımızı çalıştıracağız.

Object Tipinde yeni bir değişken yaratalım. İsmi de ObjURLList olsun.

 

Ardından içerisinde URL'lerin bulunduğu basit bir tablo yaratalım.

CREATE TABLE [dbo].[URLList](

    [URL] [nvarchar](255) NULL

) ON [PRIMARY]

 

İçerisini örnek veri ile dolduralım.

INSERT INTO [URLList] ([URL]) VALUES ('http://www.amazon.com/Rational-Guide-Extending-Script-Guides/dp/1932577254/ref=sr_1_1?ie=UTF8&s=books&qid=1265558239&sr=8-1')

INSERT INTO [URLList] ([URL]) VALUES ('http://www.amazon.com/Expert-Server-Integration-Services-Programmer/dp/0470134119/ref=sr_1_2?ie=UTF8&s=books&qid=1265558239&sr=8-2')

INSERT INTO [URLList] ([URL]) VALUES ('http://www.amazon.com/Microsoft-Server-2005-Integration-Services/dp/0672327813/ref=sr_1_4?ie=UTF8&s=books&qid=1265558239&sr=8-4')

INSERT INTO [URLList] ([URL]) VALUES ('http://www.amazon.com/Successful-Business-Intelligence-Secrets-Making/dp/0071498516/ref=sr_1_1?ie=UTF8&s=books&qid=1265558549&sr=8-1')

INSERT INTO [URLList] ([URL]) VALUES ('http://www.amazon.com/Business-Intelligence-Dummies-Personal-Finance/dp/0470127236/ref=sr_1_2?ie=UTF8&s=books&qid=1265558549&sr=8-2')

 

 

Ardından paketimize bir adet Execute SQL Task, 1 adet For Each Loop Container ve bir adet Script Task ekleyelim.

 

URL Listesi isimli Execute SQL Task içerisine girip bağlantıyı gerçekleştirdikten sonra SQLstatement bölümüne aşağıdaki query'i girelim:

SELECT * FROM URLList

Ardından ResultSet özelliğini "full Result Set" e çekelim.

 

Sol taraftan Resultset bölümüne geçelim ve Add diyelim.

ResultSet Name için 0 (sıfır) yazalım ve Variable Name alanına ise az önce yaratmış olduğumuz obje tipli değişkenimizi girelim.

(Eğer birden çok kayıt kullanacaksak (Full ResultSet) ResultSet Name için sıfır girmemiz gerekmektedir.)

OK'e basıp For Each Loop Container'ın bu değişkenin değerleri üzerinde dönmesini sağlamak üzere ayarlarını yapmalıyız.

Açılan pencerede ADO Enumerator'u seçtikten sonra alt tarafta Rows in the First Table desek yeterli.

 

Variable Mapping alanına geçip, FELoop her döndüğünde üzerinde bulunduğu değeri hangi değişkene ataması gerektiğini söylüyoruz. (Örneğimizde sURL değişkeni)

 

Ardından Script Task içerisine girerek öncelikle sURL değişkenini script içerisinde kullanmak için aşağıdaki gibi ReadOnlyVariables kısmına sURL'yi ekliyoruz ve script dilini (ben bu örnekte VB.NET kullanıyorum) belirliyoruz.

 

Edit Script diyerek script editor penceresine geçip aşağıdaki kodu yapıştırıyoruz.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.Net

Imports System.IO

 

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _

<System.CLSCompliantAttribute(False)> _

Partial Public Class ScriptMain

Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

 

Enum ScriptResults

Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success

Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

End Enum

 

Public Sub Main()

Try

Dim URL As String = Dts.Variables("sURL").Value

Dim request As HttpWebRequest = WebRequest.Create(URL)

Dim response As HttpWebResponse = request.GetResponse()

Dim reader As StreamReader = New StreamReader(response.GetResponseStream())

Dim writer As StreamWriter

Dim str As String = reader.ReadToEnd()

 

writer = New IO.StreamWriter("d:/Test/WebPage" + URL.Substring(22, 10).ToString + ".html", True, System.Text.Encoding.UTF8)

 

writer.Write(str.ToString())

writer.Flush()

writer.Close()

 

 

Dts.TaskResult = ScriptResults.Success

 

Catch ex As Exception

Dts.Events.FireInformation(0, "Web Script Task", ex.Message.ToString(), "", 0, 0)

Dts.TaskResult = ScriptResults.Failure

End Try

 

End Sub

 

End Class

 

Bu script httpwebrequest, streamreader ve streamwriter sınıflarını kullanarak web sayfasını bir html string'i haline getirip sabit diskimizdeki d:\Test klasörü altına kaydetmemize olanak veriyor.

Paketi çalıştırdığımızda sabit diskimizde istediğimiz gibi html sayfalarını görebilmekteyiz.

 

 

 

İkinci Örnek: Google'da arama yaptırmak ve sonuçlarını html olarak kaydetmek.

İlk örneğin üzerinden basit bir kod değişikliği ile bu işlemi gerçekleştireceğiz ancak burada farklı bir objeyi Microsoft Internet Controls'ü kullanacağız. Bunu yaparken de MS SQL Server için Microsoft tarafından hazırlanmış olan AdventureworksDW veritabanını kullanalım ve Customer tablosundaki Customer isimlerini aratalım. Her birini de html sayfası olarak kaydedelim.

 

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

 

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _

<System.CLSCompliantAttribute(False)> _

Partial Public Class ScriptMain

    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

 

    Enum ScriptResults

        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success

        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

    End Enum

    

    Public Sub Main()

Dim IE As SHDocVw.InternetExplorer = New SHDocVw.InternetExplorer

Dim objUrl As Object = "http://www.google.com"

Dim IEDoc As Object = Nothing

Dim IEHTML As Object = Nothing

Dim streamwrt As IO.StreamWriter = Nothing

 

Try

IE.Visible = True 'Set it to false for faster operations.

 

IE.Navigate2(objUrl, Nothing, Nothing, Nothing, Nothing)

 

While IE.Busy

'Do Nothing

End While

 

Do

'Wait until page is loaded!

Loop Until IE.ReadyState = SHDocVw.tagREADYSTATE.READYSTATE_COMPLETE

 

IEDoc = IE.Document

 

With IEDoc

.All.Item("q").Value = Dts.Variables("sURL").Value

.Forms(0).submit()

End With

 

Do

'Wait

Loop Until IE.ReadyState = SHDocVw.tagREADYSTATE.READYSTATE_INTERACTIVE

 

Do

'Wait

Loop Until IE.ReadyState = SHDocVw.tagREADYSTATE.READYSTATE_COMPLETE

 

IEHTML = IE.Document.DocumentElement.Outerhtml

 

streamwrt = New IO.StreamWriter("d:/Test/GoogleResult" + IE.LocationName + ".html", True, System.Text.Encoding.UTF8)

 

streamwrt.Write(IEHTML)

streamwrt.Flush()

streamwrt.Close()

 

Catch ex As Exception

Dts.Events.FireInformation(0, "Web Script Task", ex.Message.ToString(), "", 0, 0)

Dts.TaskResult = ScriptResults.Failure

Finally

IE.Quit()

End Try

 

Dts.TaskResult = ScriptResults.Success

End Sub

 

End Class

 

Bu işlem sırasında Microsoft Internet Controls'ü referans olarak eklememiz gerekiyor. (Bunu için de Script Editor'un içerisindeyken Menu->Project->Add Reference ile yapıyoruz)

 

Ardından Execute SQL Task içerisindeki SQL Statement'i değiştirerek müşteri isimlerini alır hale getirelim.

SELECT TOP 5 FirstName+' ' + LastName as FullName FROM DimCustomer

Paketi çalıştırdığınızda sorunsuz bir şekilde web sayfalarının belirttiğiniz klasöre kaydedildiğiniz göreceksiniz.

 

Ekstra: GoogleMaps üzerinden koordinat bilgilerini almak ve veritabanına kaydetmek.

Düşündüm ki bazen sonuçları geri veritabanına yazmak isteyebiliriz. Örneğin, müşterinin adresini verip, koordinat bilgisini veritabanında saklamak isteyebiliriz. Son zamanlarda özellikle raporlama araçlarının çoğunda Harita özelliği geldi. Bu haritalar ise koordinat bilgileri üzerinden çalışabiliyor ve bölgesel satışları harita üzerinde göstermek gibi güzel, katma değerli bir iş yapmak isteyebilirsiniz.

Bunun için yine yapmanız gereken ilk örneğimizi biraz değiştirmek. Öncelikle dilerseniz AdventureworksDW içerisinde yeni bir tablo yaratalım.

CREATE TABLE [dbo].[CustomerWithGeographyInfo](

    [rId] [int] IDENTITY(1,1) NOT NULL,

    [FirstName] [nvarchar](50) NULL,

    [LastName] [nvarchar](50) NULL,

    [AddressLine1] [nvarchar](120) NULL,

    [Coordinate] [nvarchar](120) NULL,

CONSTRAINT [PK_CustomerWithGeographyInfo] PRIMARY KEY CLUSTERED

(

    [rId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

Ardından bu tabloyu aşağıdaki query ile dolduralım.

INSERT INTO CustomerWithGeographyInfo

SELECT TOP 100 [FirstName],[LastName],[AddressLine1],NULL FROM DimCustomer

 

Ardından yeni bir string değişken yaratalım ve adına da sGoogleMapsSearchString diyelim.

Değişkenin özelliklerine girelim. Cursor Değişkenin üzerindeyken F4'e basalım ve pencereden Evaluate as Expression özelliğini = True dedikten sonra Expression özelliğinin içerisine girip aşağıdaki kodu yapıştıralım:

"http://maps.google.com/maps/geo?q="+ @[User::sURL] +"&output=csv&oe=utf8&sensor=false&key=ABQIAAAAP6yK9BJ44wmvT1Dd7V5cKRQz9eeYvRAhuPGO19pGZUf7Pj2DYxRZ0Zbtz0o1UAkk17kmD6xk5qDnzg"

 

Yeni bir String değişken yaratalım. Bunun ismi de sCoordinate olsun. Google'dan gelen sonucu bu değişkende tutacağız. Yeni bir Integer değişken daha yaratalım bunu da iCustomerKey olarak adlandıralım ki gelen değeri bu unique key üzerinden update ettireceğiz (Müşterinin ID'si olacak bu).

Öncelikle Execute SQL Task SQL Statement'ı değiştirelim:

SELECT [rId],[AddressLine1] FROM [CustomerWithGeographyInfo] WITH (NOLOCK)

 

For Each Loop Container içerisine girelim. Variable Mapping Sekmesinde bazı değişiklikler yapacağız. Mevcutta bulunan, önceki örneğimizden kalan eşleştirmeyi silelim ve aşağıdaki gibi yeni değerleri ekleyelim.

(Bu değişkenlerin sırası önemlidir.)

 

Script Task'mız içerisine girelim ve aşağıdaki Variable Mapping işlemini gerçekleştirelim.

 

Ardından aşağıdaki kodu yapıştıralım.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.Net

Imports System.IO

 

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _

<System.CLSCompliantAttribute(False)> _

Partial Public Class ScriptMain

Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

 

Enum ScriptResults

Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success

Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

End Enum

 

Public Sub Main()

Try

Dim URL As String = Dts.Variables("sGoogleMapsSearchString").Value

Dim request As HttpWebRequest = WebRequest.Create(URL)

Dim response As HttpWebResponse = request.GetResponse()

Dim reader As StreamReader = New StreamReader(response.GetResponseStream())

Dim str As String = reader.ReadToEnd()

 

 

Dts.Variables("sCoordinate").Value = str.ToString()

 

Dts.TaskResult = ScriptResults.Success

 

Catch ex As Exception

Dts.Events.FireInformation(0, "Web Script Task", ex.Message.ToString(), "", 0, 0)

Dts.TaskResult = ScriptResults.Failure

End Try

 

End Sub

 

End Class

 

Save edip ana ekrana dönelim. Burada Update işlemini gerçekleştirmek için bir Execute SQL Task daha ekleyelim FELoop içerisine.

 

Execute SQL Task'ın içerisinde çalışacak olan Update Query'si dinamik olarak üretilmeli. Bu nedenle Execute SQL Task Expressions içerisinden SQLStatementSource özelliğini dinamik olarak hesaplattırmak için aşağıdaki expression'ı girelim:

"UPDATE [CustomerWithGeographyInfo] SET Coordinate='"+ @[User::sCoordinate] +"' WHERE rId="+ (DT_WSTR, 100) @[User::iCustomerKey]

 

 

Paketi kaydedip, çalıştıralım. Göreceksiniz ki kayıtların yanlarında artık koordinat bilgileri olacaktır.

 

Budur...

 

(Special thanks to Chuck Rivel for inspiring me: http://bisqlserver.blogspot.com/2009/07/retrieve-web-page-results-using-ssis.html)

Dataflow Components, Custom Components , ,

Add comment




biuquote
  • Comment
  • Preview
Loading