Extract Data from Lawson 9 using SSIS

You got here because you need to extract data out of Lawson 9 and for some reason you are not accessing the Lawson's data model directly.


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.

Free SFTP for SSIS based on OpenSSH.

You got here because you have a sftp requirement for your SSIS and a third party component isn’t an option.


1. Download the latest libraries from http://www.tamirgal.com/blog/page/SharpSSH.aspx

2. The libraries at the time I downloaded them they didn’t have ‘strong name’ as the GAC complain about so you will need to follow instructions like : http://www.geekzilla.co.uk/ViewCE64BEF3-51A6-4F1C-90C9-6A76B015C9FB.htm

3. Once you have the libraries ready for the GAC then you need to registered them, I would strongly recommend to create an installer do it so you can deploy it easily on any environment but for now you can follow the next instructions:

a. C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Binn\gacutil /i c:\example\example.dll

b. copy the libraries to C:\windows\Assembly\

c. copy the libraries to Windows\Microsoft.NET\Framework\[.Net proper version]

4. Now we are 50% there. Create an ‘Script Task’ in your SSIS:

5. Go into design Script option and on the ‘class view’ do a right click and select ‘add reference’, you should see the libraries we have registered before, repeat this step for every single one of those.


6. Look for your OpenSSH formatted private key file .ppk, in my example the .ppk is in a folder and I have the path in a SSIS variable, I also hold the user and the sftp destination ip in other variables.

7. Set the SSIS ‘Run64BitRuntime’ property to false since these libraries are 32bits.

8. The following code is a sample to show you the basics you can do with the libraries which in my case are way enough:

Public Sub Main()

'Get variables values

Dim SFTPHost As String = CStr(Dts.Variables("cFtp").Value)
Dim SFTPUser As String = CStr(Dts.Variables("cFtpUser").Value)
Dim SFTPkey As String = CStr(Dts.Variables("cFtpKey").Value)

'Verifying S FTP

Try

Dim sftp As Tamir.SharpSsh.Sftp = New Tamir.SharpSsh.Sftp(SFTPHost, SFTPUser)
sftp.AddIdentityFile(SFTPkey)
sftp.Connect()

‘DO WHAT YOU NEED…

sftp.Close()

Catch ex As Exception
     Dim error_ As String = ex.Message
     error_ = error_ + ex.StackTrace
     If Not ex.InnerException Is Nothing Then
          Dim innerExc As Exception = ex.InnerException
          error_ = error_ + " : " + innerExc.Message + " : " + innerExc.StackTrace
     End If

     Dts.Events.FireError(-1, "", "*Unable to connect to Secure FTP: " + " : " + error_ + SFTPHost, "", 0)
     Exit Sub

End Try

Dts.TaskResult = Dts.Results.Success

End Sub

9. Once you are ready to create a SQL job to run your package be sure the owner and the runner profiles of the Jobs has enough permissions over the file system of the server were the job is running, especially to the SSIS working folder and the ‘C:\Documents and Settings\All Users\Application data\Microsoft\Crypto\RSA\MachineKeys’