Opening DTS packages on a 2008 SQL Server requires several steps to be able to view the packages in a DTS designer.  Here’s a link to these steps on a 32-bit machine or on a 64-bit machine.  After following these step you will be able to open your DTS packages in the DTS Designer.

These steps are great for opening DTS packages but I ran into a problem recently where I was not able to import packages on my server which shouldn’t require as many prerequisites like opening packages does.

When I attempted to import a package I received the following error:

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. (Microsoft OLE DB Provider for SQL Server)

After doing some research I found the problem in a blog by Xinwei Hong.  He has several possible problems listed, which luckily was solved by his first solution.  It is was a simple fix and here are the steps I followed to fix my problem:

1. Open the SQL Server Configuration Manager

2.  Expand on SQL Server Network Configuration and select Protocols for MSSQLSERVER

3.  Enable TCP/IP.  Not sure why this was disabled to begin with but that was my problem

A very common need in SSIS is to check to see if a file exist before you run what could be a very long process in your package.  There are no native tasks inside SSIS that can do this check but you can accomplish this using a Script Task.  Here are the steps to check to see if a file exist.  To view this blog with screenshots visit my regular blog at http://blogs.pragmaticworks.com/devin_knight/.

1.  Setup two variables.  The variable named strFileLocation has a string data type with the value being the location of the file I want to check for.  The variable bolFileExists has a boolean data type with the value changing based on whether the file exist or not.  If the file is found the value will be changed to True otherwise it stays False.

2.  Use a Script Task in the Control Flow and set the ReadOnlyVariables to use the strFileLocation variable and the ReadWriteVariables to use the bolFileExists variable then select Edit Script.

3.  There are two methods for writing this script.  The first method you must first add the namespace System.IO.  The second method does not require this.

4.  Scroll down the editor until you find the green commented out text that says Add your code here.  Replace that with the following code then save and close the editor :

Dts.Variables("bolFileExists").Value = File.Exists(Dts.Variables("strFileLocation").Value)

The second option for the script is (Remember this option is not using the Imports System.IO step) :

Dts.Variables("bolFileExists").Value = My.Computer.FileSystem.FileExists(Dts.Variables("strFileLocation").Value) 

5.  Now that you have the script done you can use things more familiar to you in SSIS like precedence constraints with expressions to get the desired results.  Connect the completed Script Task to a path that you want the package to move if the file does exist.  Open the precedence constraints editor by double clicking on the line. change the Evaluation operation to Expression and Constraint and add the expression @bolFileExists==True.  Remember the double equals is a comparison.  If it was a single equal sign it would be trying to set the value of the variable to True.  Click OK once complete.  If you want a path for if the file is not found then follow the same steps with the precedence constraint but the expression should be @bolFileExists==False.

I’m using script tasks just as placeholders here for how the rest of the package may look.  If my file did exist it would go down the left path.  If it did not exist my package flow would go right.

While in the process of developing SSIS packages it’s likely you will deal with errors occasionally unless you’re the Superstar developer that never makes mistakes.  Of course your first steps to deciphering the problem is to look at either the progress/execution results tab in BIDS.  The problem with viewing error messages here is that more than likely the error you receive does not fit on the single line and goes off the screen.  So what do you do?  Right-click select Copy Message Text and paste it in something like the notepad is what most people do.  This is an quick way to view a full error message, but I’m going to share with you another option that can be used during development to view your full error message.  To read this blog post with screenshots visit my regular blog at http://blogs.pragmaticworks.com/devin_knight/.

This method involves using a little bit of scripting and taking advantage of Event Handlers.

1.  Create an event handler on the package level that will kick off a Script Task when an OnError event occurs.

2.  Drag a Script Task from the Toolbox onto the Event Handler design surface then double-click to open it’s editor.

3.  Select the System::ErrorDescription Variable From the ReadOnlyVariables list.  (This variable is only available in the Event Handler tab)  Then click Edit Script.

4.  Once the Editor opens scroll down to where it says Add your code here and replace that line with the following code:

MsgBox(Dts.Variables("ErrorDescription").Value)

This code is looking waiting for this system variable ErrorDescription to be populated and when it is a message box will appear with it’s content.

Now when your package is run if it encounters an error that causes the package to fail you will receive a message box with the same error you would find in the progress/execution results tab.  If you have multiple errors you will receive multiple message boxes.  This message can easily be copied from the message box by using the CTRL+C if you will wish to copy this to a notepad.

Saturday I had the great opportunity to speak at SQL Saturday in South Florida.  The event was great.  It was very well organized and the facility was perfect for the amount of people.  There was a total of about 350 people who made it.   

I spoke on Performance Tuning SSIS, which is one of my favorite topics.  Due to an absent speaker I also took over another session after lunch which was an Introduction to SSIS.  I wasn’t really prepared to do this session but I think it still went well.  If you were in my Performance Tuning session here is a link to download my slide deck and packages used in demos.

http://www.pragmaticworks.com/DevinKnight/PerformanceTuning.zip