October 2008 - Posts

• Detecting if a File is in Use in a SSIS Script Task

  When mainframes or partners are uploading a file, you don't want to process the file until you're certain the file is fully uploaded. One way to do this is with a Script Task in SSIS. There is no magic property to determine in the operating system to see if the file is in use though so you'll have to essentially try to trigger an error and then gracefully trap it and then set a variable that can read.  In an SSIS Script Task, you can use similar VB.NET code as what you see below to do this:     Public Sub Main()         '         Try             Dim path As String = Dts.Variables("sTraceFileName").Value             'If you want to detect first if the file exists, uncomment these lines. 'If File.Exists(path) = False Then              '    File.Create(path)             'End If             File.SetLastAccessTime(path, New DateTime(1985, 5, 4)) Dts.Variables("bSafeToProcessTrace").Value = True         Catch e As Exception             Dts.Variables("bSafeToProcessTrace").Value = False         End Try         Dts.TaskResult = ScriptResults.Success     End Sub   As you can see in the above code, I essentially tried to set the LastAccessedTime property via the method. If the file is in use, it will fail and trigger the bSafeToProcessTrace variable to be set to false. To use that variable, you can wire your next task to the Script Task as shown below. Then double-click on the green line and set an expression on the precedence constraint as shown below: @bSafeToProcessTrace == True     Now, the data flow will only execute if the file is not in use. If you don't like to code, you can also download the File Properties Task from PragmaticWorks.com, which is a free task. Posted Oct 15 2008, 09:26 AM by Brian Knight with no comments Filed under: SSIS, Expressions, File Properties Task, scripting

• Pre-Conference Session at PASS

  Only an  few weeks until PASS and I'm very excited about meeting everyone again. I missed last year due to the birth of my new baby girl so I missed my fix. I'll be doing 4 sessions at PASS this year. My first will be an all day pre-conference SSIS boot camp. In this day-long session, you will spend the first hour catching up on what you may have been missing with SSIS in SQL Server 2005 and 2008. Then, the seminar rapidly progresses into much more advanced topics like how to load a data warehouse and advanced ways to log errors and handle events. I also cover best practices for the enterprise in package configuration, deployment and administration. The content moves quickly through the SSIS concepts and depicts common scenarios that new and experienced SSIS developers run into. You should walk away confident in how to achieve success with SSIS. I hope you can make it and I'm essentially condensing my four days of class material down to a day seminar so it should be pretty fast. I'll also be handing out files so you can follow along with all the examples on your laptop (if you have enough power to sustain it for the day that is :)). It should be a very interactive, real-world session where we will go over all the common enterprise situations I see in the field. You'll also walk out with a number of packages that will help you monitor and perform common scenarios easily. For more information and to register, visit: http://summit2008.sqlpass.org/precon-brian-knight.html Posted Oct 14 2008, 07:38 PM by Brian Knight with no comments Filed under: speaking engagements