SSIS: Waiting for a file

With longer running processes, it is a common practice to create a 0-byte file as a signal to a dependent process. An example would be the data transfer via FTP of several files to a staging directory. This post shows how to wait for the 0-byte file.

Control Flow

When complete, the control flow for this package will have the following items defined:

• For Loop Container – this makes the process continue in a loop until the 0byte file is found
• Check File script task – this checks for the presence of a file and sets a Boolean variable to True or False
• Sleep script task – this will create a time delay so that the process isn’t checking every nano-second
• Move CheckFile script task – this moves the 0byte file to avoid conflicts with subsequent data runs

The control flow will appear as follows:

Variables

The following variables need to be defined:

Variable	DataType	Purpose
CheckFile	String	Filename of the 0-byte file to look for
CheckFileExists	Boolean	Boolean flag that is set to true upon detection of the 0-byte file
FFDataPath	String	Staging directory where the 0-byte file is expected to be placed
SleepMinutes	Int32	Number of minutes to sleep between checks for the 0-byte file

Definition: For Loop Container

Configured to loop while User variable User::CheckFileExists is equal to ‘False’.

InitExpression
EvalExpression	@[User::CheckFileExists]==False
AssignExpression

Definition: CheckFile Script Task

Configured to manipulate the value of the User variable User::CheckFileExists to ‘True’ or ‘False’.

ReadOnlyVariables	User::CheckFile, User::FFDataPath
ReadWriteVariables	User::CheckFileExists

The script task will then execute the following code:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO

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()
        '
        ' Created by: Zack Bethem @ AmberLeaf
        '
        Dim fileLoc, fileName As String
        If Dts.Variables.Contains("User::FFDataPath") = True AndAlso _
        Dts.Variables.Contains("User::CheckFile") = True Then
            fileLoc = CStr(Dts.Variables("User::FFDataPath").Value)
            fileName = CStr(Dts.Variables.Item("User::CheckFile").Value)
            'debug System.Windows.Forms.MessageBox.Show("FileDir:" + fileLoc + "FileName:" + fileName)
            If File.Exists(fileLoc + fileName) Then
                Dts.Variables.Item("User::CheckFileExists").Value = True
                'debug System.Windows.Forms.MessageBox.Show("File exists")
            Else
                Dts.Variables.Item("User::CheckFileExists").Value = False
                'debug System.Windows.Forms.MessageBox.Show("File not exists")
            End If
            Dts.TaskResult = Dts.Results.Success
        Else
            Dts.TaskResult = Dts.Results.Failure
        End If
    End Sub

End Class

The script task is configured to execute the Sleep task based on the precedence constraint. This is defined by double clicking on the connector and setting the following:

Definition: Sleep Script Task

Configured to sleep or pause the process.

ReadOnlyVariables	User::SleepMinutes
ReadWriteVariables

The script task will then execute the following code:

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

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()
        '
        ' Zack Bethem @ AmberLeaf
        ' Based on: http://blogs.pragmaticworks.com/mike_davis/2009/06/make-an-ssis-package-delay-or-wait-for-data.html
        '
        If Dts.Variables.Contains("User::SleepMinutes") = True Then
            Dim min As Double = Convert.ToDouble(Dts.Variables("SleepMinutes").Value) * 60
            Dim ms As Int32 = Convert.ToInt32(min * 1000)
            System.Threading.Thread.Sleep(ms)

            Dts.TaskResult = Dts.Results.Success
        Else
            Dts.TaskResult = Dts.Results.Failure
        End If

    End Sub

End Class

The For Loop container is configured to execute the Move CheckFile task based on the precedence constraint. This is defined by double clicking on the connector and setting the following:

Definition: Move CheckFile Script Task

Configured to move the 0-byte handoff file to an Archive location with the appended suffix of the date in yyyyMMddHH format.

ReadOnlyVariables	User::CheckFile,User::FFDataPath,User::FFArchivePath
ReadWriteVariables

The script task will then execute the following code:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO

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()
        '
        'check if vars exist
        If Dts.Variables.Contains("User::CheckFile") = True AndAlso _
        Dts.Variables.Contains("User::FFDataPath") = True AndAlso _
        Dts.Variables.Contains("User::FFArchivePath") = True Then

            Dim srcFile, tgtFile, tgtFileName, searchStr As String

            srcFile = CStr(Dts.Variables("User::FFDataPath").Value) + _
            CStr(Dts.Variables("User::CheckFile").Value)

            tgtFileName = CStr(Dts.Variables("User::CheckFile").Value)

            'add a datastamp on handoff file
            tgtFileName = Left$(tgtFileName, InStrRev(tgtFileName, ".") - 1) + _
            "_" + CStr(Format(Now, "yyyyMMddHH")) + _
            Mid(tgtFileName, InStrRev(tgtFileName, "."))

            'System.Windows.Forms.MessageBox.Show(tgtFileName)

            tgtFile = CStr(Dts.Variables("User::FFArchivePath").Value) + tgtFileName

            'System.Windows.Forms.MessageBox.Show(tgtFile)

            Try
                File.Move(srcFile, tgtFile)
                Dts.Events.FireInformation(0, "", "File moved to: " + tgtFile, "", 0, True)
                Dts.TaskResult = Dts.Results.Success
            Catch ex As Exception
                Dts.Events.FireInformation(1, "", "File move failure. Tried to move to: " + tgtFile, "", 0, False)
                Dts.TaskResult = Dts.Results.Failure
            End Try

        End If

    End Sub

End Class

Using XML datatype to parse comma-delimited columns

This was the coolest trick I’ve seen in a while.
Problem: need to parse a comma delimited file


select email
, Split.a.value('.','Varchar(50)') as kid_age
, row_number() over (partition by email order by email) as row_cnt
from (
select email
, cast('' + REPLACE(kid_age,',','') + '' as xml) as kid_age
from stg_profile_data ) x
cross apply kid_age.nodes('/M') split(a)


Mucho thanks to where Google found it

I Heart Unix

Use SED to get rid of those nasty empty lines in a text file:

sed '/^$/ d' someFile > someNewFile
mv someNewFile someFile


MySQL’s version of SQL*Loader

Just found the nice command built into MySQL to load data files. It couldn’t have been simpler to use. Just be sure to through the right newline. Using ‘\r\n’ worked for a file generated in SSIS in my case.
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number LINES]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]


See more magic here from the MySQL manual.

Dynamicallly build URLs in RightNow

I always forget this, despite it being one of the most important features.

If you want to build a tab, that can pass a value from a custom field in RightNow as part of the parameter you can do it as follows:

• $p_ccf_ — use with contact custom fields
• $p_orgcf_ — use with organization custom fields
• $p_icf_ — use with incident custom fields
• $p_acf_ — use with answer custom fields
• $p_spcf_ — use with sales product custom fields
• $p_ocf_ — use with opportunity custom fields

Why would the “H” would one do this? Think of it this way. Client ABC has an existing portal that uses friendly URLs (like Ruby on Rails). So if I had an account-id custom field on an organization, I could pass that value and the web page shown in the control would present information pertinent to that account-id (aka. organization). HUGE savings.

Turn a WSDL to a referenceable DLL

Found this will surfing the intertubes. I’m using it to build my first Webservice integration into RightNow using their desktop integration API.

Here were my steps in creating the dll.

1. Turn the WSDL into buildable, C-Sharp code

"C:\Program Files\Microsoft SDKs\Windows\v6.0a\bin\wsdl.exe" /l:cs /protocol:SOAP https://www.penproplus.com/pppwebservice/pppwebservice.asmx?WSDL

2. Compile the C-Sharp code into a referenceable DLL

c:\windows\microsoft.net\Framework\v3.5\csc /t:library /r:System.Web.Services.dll /r:System.Xml.dll PPPWebServices.cs

I had a couple of wonky path issues when running these commands. I had to make sure I was running the CSC compiler from the .Net Framework directory. Otherwise, I get some complaints about missing dlls and other grumpy messages

RightNow Desktop Integration .. it begins

I’ve started exploring the new capabilities of RightNow desktop integration. It looks to be quite powerful, assuming I can get my hacking skills around it.

Unit testing code is a pain with the integration API. Each change requires a restart of RightNow. That can take a while, even with a fast connection & computer.

One recommendation, however, is to add the following in the project’s Post-build event. This will automatically deploy the new compiled dll and move it to the proper directory for development testing. It should be part of the default set of templates, but it isn’t.

mkdir "%USERPROFILE%\RightNowDev"
mkdir "%USERPROFILE%\RightNowDev\AddIns"
mkdir "%USERPROFILE%\RightNowDev\AddIns\$(ProjectName)"
copy /Y "$(TargetDir)$(TargetName).*" "%USERPROFILE%\RightNowDev\AddIns\$(ProjectName)\"

Great stuff.

RNOWOrganization – how to add a parent?

I’m still working with hacking my way through C#. The latest stumbling block is adding an organization with an associated parent organization. The end result would look like the following within RightNow:

To date, I have the following code using the RNOW Data Connection API

// set parent org
if (Convert.ToString(row[38]) != "")
{
int z = getXrefId(Convert.ToString(row[38]), tblXrefIds);
if (z != 0)
{
RNOWOrganization parentOrg = new RNOWOrganization(z);
List parentOrgs = new List();
parentOrgs.Add(parentOrg.ID);
myOrg.Parent = parentOrgs;
}
else
{
//log error
}
}

The current question? The code syntax 'myOrg.Parent = parentOrgs;' doesn’t work. Instead, I get the error:
Cannot implicitly convert type ‘System.Collections.Generic.List<int>’ to ‘System.Collections.Generic.List<int?>’

Seems same to me.

Argh!!! Why can it just be a single Integer representing the ID of the organization. A list to set a single parent? What for? Make it simple stupid. The head scratching continues.

Update

Turns out C# provides a way to define a nullable list of collections. This is defined by the ‘?’ notation. So, parentOrgs in the code snippet above had to be defined by:

List<int?> parentOrgs = new List<int?>();
Defining the hierarchy is still not working 100%, but this hurdle is hopped over.

RightNow Connect DLL

I’ve found myself doing some .Net coding these days. A rest from Ruby on Rails (which is friggin’ awesome).

When using the RightNow Connect DLL I always would get the most common error of all..

“Invalid Server Version. Server ‘ ‘. Connect ‘8.5.0.127′.”

Scratching my head. I closed the new solution in Visual Studio, which caused the solution’s folder structure to be created. After reopening it and re-pointing the reference to the RightNow Connect DLL. Things seem to work. No problems with that A-N-N-O-Y-I-N-G “invalid server version” message. Viola! It works. Good enough.

And now for something completely different…mongrel

I’ve been switching gears lately. I’m not riding the ruby rails or trying to at least.

Thanks to this entry, I’m finally up and running. I’ll probably need this in the future, so I’m saving it for future reference. Thanks for the post.

Configure the Mongrel Cluster
mongrel_rails cluster::configure -e production -p 8000 -N 2 -c /home/railsuser/rails/appname -a 127.0.0.1
A note about this:
-N 2 : Two instances of Mongrel will be started up as part of this cluster
-p 8000: The Mongrel instances will listen at ports starting at 8000 (i.e. 8000 and 8001)
/home/railsuser/rails/appname : Path to the folder where your Rails app resides
-a 127.0.0.1 : This restricts access to localhost.