The SSIS task should be referencing the following libraries:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net
Imports System.Xml
Imports System.Web
Imports System.IO
Declare the required variables and constants:
Dim request As HttpWebRequest
Dim response As HttpWebResponse
Dim login As String = "http://server.domain:port/sso/SSOServlet"
Dim logout As String = "http://server.domain.com:port/sso/logout.htm?http%3A//server.domain.com%3A20502/lawson/portal/index.htm"
Dim query As String = "http://server.domain.com:port/servlet/Router/Data/Erp?PROD=library&FILE=GLAMOUNTS&FIELD=COMPANY;ACCT-UNIT;ACCOUNT;SUB-ACCOUNT;CYPYTDperiod;FISCAL-YEAR;GLMASTER.ACCT-DESC&SELECT=GLMASTER.ACTIVE-STATUS%21%3DI&INDEX=GAMSET1&KEY=*company%3D*year%3D%3D&OUT=CSV"
Dim loginData As String = "_ssoUser=" & user & "&_ssoPass=" & password & _
"&_ssoLogin=Login&_action=LOGIN&_fromLoginPage=" & _
"FALSE&_language=en-us&_ssoOrigUrl=http%3a%2f%2f" & _
"server.domain.com%3a20502%2flawson%2fportal%2f"
Const userAgent As String = "Mozilla/4.0 (compatible; MSIE 6.0; " & _
"Windows NT 5.1; SV1; MCNA6020; .NET CLR 1.1.4322; " & _
".NET CLR 2.0.50727; OfficeLiveConnector.1.3; " & _
"OfficeLivePatch.0.0)"
Const _POST As String = "POST"
Const _GET As String = "GET"
Const _timeout As Integer = 240000 '300000 Five minutes '240000 4 min '180000 3.5 min
Const _ContentType As String = "application/x-www-form-urlencoded"
The following line is critical otherwise the header will be sent with a extra element the target web server might not know how to deal with:
System.Net.ServicePointManager.Expect100Continue = False
Create your credential object and cookie component:
Dim credentials As NetworkCredential = New NetworkCredential(user, password)
Dim cookies As CookieContainer = New CookieContainer
Dim collection As CookieCollection
Create the stream reader to deal with the response file:
Dim answer As StreamReader
Dim text As String
Dim post As StreamWriter
My variable has some wild cards to modify on run time so in case you do something similar adjust the query in the next step:
login = login.Replace("server", CStr(Dts.Variables("pServer").Value))
login = login.Replace("port", CStr(Dts.Variables("pLawsonPort").Value))
logout = logout.Replace("server", CStr(Dts.Variables("pServer").Value))
logout = logout.Replace("port", CStr(Dts.Variables("pLawsonPort").Value))
query = query.Replace("server", CStr(Dts.Variables("pServer").Value))
query = query.Replace("port", CStr(Dts.Variables("pLawsonPort").Value))
query = query.Replace("library", CStr(Dts.Variables("pAS400Library").Value))
query = query.Replace("period", CStr(Dts.Variables("pPeriod").Value))
query = query.Replace("*year", CStr(Dts.Variables("pFiscalYear").Value))
query = query.Replace("*company", CStr(Dts.Variables("pLaws_com").Value))
loginData = loginData.Replace("server", CStr(Dts.Variables("pServer").Value))
In the following steps you will be requesting the session cookies:
Try
request = CType(WebRequest.Create(login), HttpWebRequest)
request.Method = _POST
request.ContentType = _ContentType
request.Credentials = credentials
request.UserAgent = userAgent
request.CookieContainer = cookies
request.Timeout = _timeout
post = New StreamWriter(request.GetRequestStream, System.Text.Encoding.ASCII)
post.Write(loginData)
post.Close()
'Look for cookies
response = CType(request.GetResponse, HttpWebResponse)
collection = request.CookieContainer.GetCookies(request.RequestUri)
For Each cookie As Cookie In collection
cookies.Add(cookie)
Next
If Not cookies.GetCookieHeader(request.RequestUri).Contains("C.LWSN") Then
Throw New Exception("User " & user & " has problems accessing Lawson on " & CStr(Dts.Variables("pServer").Value) & _
" due an authentication issue ")
End If
response.Close()
Catch ex As Exception
Try
Dts.Events.FireError(-1, "", ex.Message + " : " + ex.InnerException.Message, "", 0)
Catch ex1 As Exception
Dts.Events.FireError(-1, "", ex.Message, "", 0)
End Try
Dts.TaskResult = Dts.Results.Failure
End Try
Now you are ready to run your query and receive the csv file:
Try
request = CType(WebRequest.Create(query), HttpWebRequest)
request.Method = _GET
request.Credentials = credentials
request.UserAgent = userAgent
request.CookieContainer = cookies
request.Timeout = _timeout
'Look for the answer
response = CType(request.GetResponse, HttpWebResponse)
answer = New StreamReader(response.GetResponseStream)
text = answer.ReadToEnd
response.Close()
If Not text.Contains("COMPANY,ACCT-UNIT,ACCOUNT,SUB-ACCOUNT,CYPYTD") Then
Throw New Exception("User " & user & " has problems accessing Libraries and Files in Lawson on " & CStr(Dts.Variables("pServer").Value) & _
" using the following query :" & query)
End If
You should be able to deal with the csv file now, in my case I'm only going to save it into the harddrive:
Dim output As File
output.WriteAllText(CStr(Dts.Variables("cFilePath").Value) & "Lawson.csv", text)
The next step will be appreciate by Lawson admin people and your very soul because you will be closing the session propperly:
request = CType(WebRequest.Create(logout), HttpWebRequest)
request.Method = _GET
request.UserAgent = userAgent
request.CookieContainer = cookies
request.Timeout = _timeout
'Look for the answer
response = CType(request.GetResponse, HttpWebResponse)
response.Close()
Catch ex As Exception
Try
Dts.Events.FireError(-1, "", ex.Message + " : " + ex.InnerException.Message, "", 0)
Catch ex1 As Exception
Dts.Events.FireError(-1, "", ex.Message, "", 0)
End Try
Dts.TaskResult = Dts.Results.Failure
End Try
And you are done and ready to go for another cup of caffee and make some phone calls or answer some emails.
