Sometimes in SSIS, You Just Need to Wait

In one SQL Server Integration Services (SSIS) package Data Flow, I have two OLE DB Commands that have to run one after the other.

image

The problem is that I am updating the same table in a Microsoft Access 2000 Database in both steps.  So, when there are many records, the second OLE DB Command throws an error complaining about the table being locked.  Of course, the table is locked by the first OLE DB Command that needs just a few more seconds to finish what it was doing. 

So how can I give enough time for the first OLE DB Command to finish before invoking the second one?  Well, in this particular case, I am blessed with the fact that I am pretty much guaranteed that there will most likely never be more than 40 records to go through this data flow when it runs (this information is from a single provider doctor office and 40 is the most patients he can see in one day).  So, all I need is a stop gap. 

The solution is to add a Script Component between the two OLE DB Commands.  Once there, edit the Script Component and add the following two lines:

image

What we are doing is calling the Sleep function in C# and telling it to count to 10 (the argument is in milliseconds).  This way, the first OLE DB Command has 10 seconds to finish; thereby unlocking the table, before the next one begins – and with 40 records that should be plenty of time.

I do not claim this solution to be elegant; however, it does work as intended and it solves the problem.  I welcome more elegant solutions in the comments.

For more information about the System.Threading.Thread.Sleep() function, see this MSDN web page.

James

If Connecting to Someone Else’s FTP/SFTP/FTPS Server Using SSIS, Be Kind

In SQL Server Integration Services, there is a FTP Task you can use to upload and download files to or from, respectively, a FTP site.  If you need to upload or download from a SFTP or FTPS site, you’ll need Ivan Peev’s SFTP Task from CozyRoc (www.cozyroc.com).  I’ll have a lot more to say about Ivan and his superb suite of tools for SSIS later.  The issue we are talking about today is showing some common courtesy to the owner of the FTP site via a way you probably haven’t thought of.

A few months ago, I got a call from Joel Hoover who maintains the FTP site for RealMed, our clearinghouse we use to send electronic claims through (www.realmed.com).  Joel informed me that, according to his logs, when we downloaded or uploaded files to his site, we were making upwards of almost 500 connections per day.  After doing some quick math, I realized that we should, at most, be making about 6 connections per day since we only download or upload files from their site 6 times a day, so where did he get 500 from?  I informed him that this must be some sort of mistake but then he emailed me a screenshot of the logs with concrete evidence that, yes, we were connecting to this site around 500 times per day – sometimes more.  I did some digging and found the culprit. 

In SQL Server Integration Services, each Task that executes something against a data source must have a Connection Manager.  The FTP Task is no exception.  It has a FTP Connection Manager (or SSH if you are using Ivan’s tool).  And, like most of the other Connection Managers, it has configurable properties – and I’m betting some of you know where I’m going with this already.  Here is a screenshot:

image

Notice the properly value RetainSameConnection.  By default this is typically set to FALSE.  You have to remember to set it to TRUE.  So, what was happening is that as I looped through all the files on Joel’s FTP site in my SSIS package, I was constantly:

Open connection
perform some operation
Drop connection
open connection
perform some operation
drop connection
open connection
perform some operation
drop connection

You can probably see how the FTP owner, Joel, was freaking out over this.  By changing that one value, I was able to reduce our number of connections from well over 500 down to about half a dozen for the entire day.  By setting the value of RetainSameConnection to TRUE, the first connection made to the FTP site is retained for all future uses of that Connection Manager throughout the life of the SSIS package.

In fact, I would say that, unless you are changing your connection string in your connection manager (i.e. looping through csv files, for example), I would recommend always changing the value of RetainSameConnection to TRUE.  This will reduce the overall overhead of your SSIS package and not hammer your data source so much.

James

Accessing a remote file share in SQL Server Integration Services

Imagine the following scenario or something close to it:

  • You have an IPSEC VPN to another network from yours.
  • There are some CSV (or other) files on a file share that you need to access.
  • The computer owning the file share is not a member of your Windows domain.
  • For some reason (just pick one) the idea of installing a FTP service or other thing on the remote computer is not an option.

This presents a problem because, unless the person who owns the remote computer is willing to seriously compromise security (and you have the guts to ask him to do so), you aren’t getting to those files using SSIS – at least not in a completely automated fashion.  Or are you?

Using SQL Server Integration Services, you can access a remote file share with a username and password provided by the remote computer owner while SSIS is running as a scheduled task using the SQL Agent – completely automated.  Check out the following screen shot.

image

The rest of the SSIS package is cut off as it is irrelevant to our discussion.  We are interested in, specifically, the Execute Process Task.  Let’s double-click on that task to see what’s up.

image

In the execute process task we are doing something I am certain most of you have done before – use the command interpreter – cmd.exe – to map a network drive using the NET USE command.  For those that need a refresher on the NET USE command, visit this TechNet web page.

Let’s go through the fields to see what we have.

  • RequireFullFileName – False

Indicates whether the task fails if the executable is not found in the specified path. We aren’t worried about this since we know CMD.EXE is in %windir%\system32. 

  • Executable – c:\windows\system32\cmd.exe

The fully qualified path to the executable you want to run.  I assume you have installed Windows to the default directory. Use %windir% if you are not sure or have Windows installed some place else.  So that would be %windir%\system32\cmd.exe.

  • Arguments – /C net use y: machineIP\sharename /USER:username password

Specifies the arguments for the executable. In this case, the NET USE command and its arguments.  Also, notice that we are throwing the “/C’” switch to cmd.exe. That means to carry out the command specified by string and then terminate.  We don’t want a command prompt lingering around after we are done.  Basically, do your job and leave is what we are saying.

  • WorkingDirectory – c:\windows\system32

Specifies the working directory of the executable. Set to the path that cmd.exe is found in. Use %windir% if you are not sure or have installed Windows elsewhere.

  • FailTaskIfReturnCodeIsNotSuccessValue – True

If we get a failure code for the return value of the command, then fail the task.  You, in most cases I’m sure, want this to be true.

  • SuccessValue – 0

Obviously, we must spell out the success value to look out for.  CMD.EXE usually returns a value of 0 if a command is successful.

  • TimeOut – 0

It should take no time at all to map a drive.  The default value is fine.

  • WindowsStyle – Hidden

Remember, we are running this fully automated.  You probably will not even be logged on the computer when this SSIS package runs.  Therefore, you do not want a Window.

Once this task runs in SSIS, you should then be able to access any files in the remote file share.  Do keep in mind that what you can do with those files is up to the remote administrator.  If you cannot write to the files and need to, you may need to ask the remote administrator for write privileges.  Also, accessing this drive will work as long as the SSIS package is executing.  Once execution of the SSIS package is complete, the file share access goes away as it is not persistent. 

I’d be interested to know if this helps anyone.

James