Recently we were setting up ETL routines to read data from AS400 output flat files. That wasn’t in itself a problem however we did need to check the date of the data within the files before we ran the ETLs. If the date was not what was expected, the ETLs should not run.
Trouble is, the dates themselves were on line 4 of each of the files from char 2 to 9 in YYYYMMDD string format…. (see pic below)
Question, how to get these as proper dates into SSIS package variables so they could be used for precedence constraints.
Well I am sure there are a few ways we could have done this, but it does present a nice example of how we can use the SSIS Script component to populate variables and read files such as these.
The first thing I did was to set up some package variables as follows
- CDFile – String
- DealFile – String
- FileLocation – String
- FilesExist – Boolean
- Load_Date_CD – Datetime
- Load_Date_Loan – Datetime
To be able to read & write variables in the Script Task, you need to specify them in the script editor
Notice that we read from three of the variables, but can write to the boolean FilesExist variable (which we need to do as we use this as a constraint later on). We then use the following script to check for the file existence.
Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' Created: Will Riley
Public Sub Main()
Dim fileLoc As String, fileName_deal As String, filename_cd As String
If Dts.Variables.Contains("User::FileLocation") = True AndAlso _
Dts.Variables.Contains("User::DealFile") = True AndAlso _
Dts.Variables.Contains("User::CDFile") = True Then
fileLoc = CStr(Dts.Variables("User::FileLocation").Value)
fileName_deal = CStr(Dts.Variables.Item("User::DealFile").Value)
filename_cd = CStr(Dts.Variables.Item("User::CDFile").Value)
System.Windows.Forms.MessageBox.Show("FileDir:" + fileLoc + "FileName:" + fileName_deal)
System.Windows.Forms.MessageBox.Show("FileDir:" + fileLoc + "FileName:" + filename_cd)
If File.Exists(fileLoc + fileName_deal) Then
If File.Exists(fileLoc + filename_cd) Then
Dts.Variables.Item("User::FilesExist").Value = True
System.Windows.Forms.MessageBox.Show("Files exist")
Else
Dts.Variables.Item("User::FilesExist").Value = False
System.Windows.Forms.MessageBox.Show("Files do not exist")
End If
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
End If
End Sub
End Class
We can now use a precedence constraint so that the next step is only carried out if the FileExists variable has been set to TRUE
So, now we can extract the dates and wite them to the two variables…. using another Script component.
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
'
Dim file_cd As String, fileloc As String, file_loan As String
Dim strReadLine As String, filedate As Date
fileloc = CStr(Dts.Variables("User::FileLocation").Value)
file_cd = CStr(Dts.Variables.Item("User::DealFile").Value)
file_loan = CStr(Dts.Variables.Item("User::CDFile").Value)
'get the CD filedate variable
Dim oStream As New IO.StreamReader(fileloc + file_cd)
'convert the fourth line of the flat file to a date format
'line 4 always starts with capital A char so we can look for it that way
While oStream.Peek <> -1
strReadLine = oStream.ReadLine()
If Left(strReadLine, 1) = "A" Then
filedate = New DateTime( _
CInt(Strings.Mid(strReadLine, 2, 4)), _
CInt(Strings.Mid(strReadLine, 6, 2)), _
CInt(Strings.Mid(strReadLine, 8, 2)))
Exit While 'leave loop here if A found
End If
End While
'write filedate to package variable
Dts.Variables.Item("User::Load_Date_CD").Value = filedate
System.Windows.Forms.MessageBox.Show(CStr(Dts.Variables.Item("User::Load_Date_CD").Value))
'get the loan file date variable
Dim oStream1 As New IO.StreamReader(fileloc + file_loan)
'convert the fourth line of the flat file to a date format
'line 4 always starts with capital A char so we can look for it that way
While oStream1.Peek <> -1
strReadLine = oStream1.ReadLine()
If Left(strReadLine, 1) = "A" Then
filedate = New DateTime( _
CInt(Strings.Mid(strReadLine, 2, 4)), _
CInt(Strings.Mid(strReadLine, 6, 2)), _
CInt(Strings.Mid(strReadLine, 8, 2)))
Exit While 'leave loop here if A found
End If
End While
'write filedate to package variable
Dts.Variables.Item("User::Load_Date_Loan").Value = filedate
System.Windows.Forms.MessageBox.Show(CStr(Dts.Variables.Item("User::Load_Date_Loan").Value))
'
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
We can then use these dates within the variables to perform other checks before running the main ETL jobs. In this way we can ensure that we do not run the same data into the warehouse database twice without the need for any manual checks. We can also ensure using various constraints that the data is loaded without missing out any dates.
