Archive for the ‘SSIS’ Tag

Courion Tip – SSIS Data feeds

The scenario –

“I” get a daily feed from SAP of who has accounts on that system…But, the file name is generated automatically by SAP. So, it varies from day to day – and is NOT based on the date time stamp. So, basically, let’s consider it to be random.

Using this reference:

http://www.rafael-salas.com/2007/03/ssis-file-system-task-move-and-rename.html

I now copy the file and rename it to a standard file name in one step – (I have a step prior to this that makes an archival copy of the file).

Problem solved. I intend to supply more details to this post.

Advertisements

Posted August 23, 2011 by mmdmurphy in Courion Tip, SQL

Tagged with ,

Courion Tip – SSIS Active Directory and LastLogonTimeStamp

Very briefly, the last logon time stamp is something like the number of milliseconds since Jan 1, 1601 (something to do with the Year 2000 Bug and that was ANSI’s solution to it)

So, to get the last logon date (I didn’t bother with the hours minutes and seconds)

I pull in the lastlogondatestamp as a string and then do this:

UPDATE    dbo.STAGING_AD
SET              lastLogon = DATEADD([Day], CAST(LastLogonTimeStamp AS numeric) / 864000000000 – 109207, 01 / 01 / 1601)

Posted June 6, 2011 by mmdmurphy in Courion Tip

Tagged with , ,

Courion Tip – XMLAO and SSIS made easy

I was intimidated by the whole XMLAO thing, but it’s really not that bad once you get some experience with it.

I will flesh this out over the next several days, so this first post will be a little cryptic.

Start by adding an ADO data source connection. I set mine to pass the workflow name, the action, and the user ID several times. Side note – why have SEVERAL ‘unique’ columns in the same table? Seemed redundant to me, so, I make sure that the user id is positively unique in the data source, and then I don’t need an AutGUID, and a record number.

Here’s a list of what I am passing to the scripting object:

Next, add a Scripting object to your SSIS package, and then change it’s type to visual basic – by default, it’s C++.

Add these includes to the top:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports System.Xml
Imports System.Net
Imports System.Net.Security
Imports System.Security.Cryptography.X509Certificates
Imports System.IO

And now define your variables:

        Dim XMLAOPath As String
        Dim workflow As String

        Dim authGUID As String
        Dim recordID As String
        Dim targetID As String
        Dim action As String

        ServicePointManager.ServerCertificateValidationCallback = New RemoteCertificateValidationCallback(AddressOf ValidateServerCertificate)

        XMLAOPath = CType(Me.ReadOnlyVariables("XMLAOPath").Value, String)

        recordID = Row.RecordID.ToString()
        authGUID = Row.AuthGUID.ToString()
        targetID =
        action =
        workflow =

Add this line of code to actually call the workflow:

            spmlRequest = "<addRequest%20RequestId=""" & authGUID & """><identifier%20type=""RecordID""><id>" & recordID & "</id></identifier></addRequest>"

            request = "ACWorkflow=" & workflow & "&XmlInputDoc=" & spmlRequest
            MsgBox(XMLAOPath & " " & request)

Make sure your inputs and outputs match:

Posted May 14, 2011 by mmdmurphy in Courion Tip, SQL

Tagged with ,