SSIS Logging with Custom Messages

SSIS Logging is a great thing. I use it in every single SSIS Package I create. I normally use the SSIS log provider for SQL Server. I am not going to explain here how to set up logging for SSIS. There are plenty of other blog posts or BOL articles about that. Here all I was going to discuss how to add your own custom messages to log.

Drag the Script task control flow item on the Control Flow area and connect it to the desired component or components. Name the script task component wisely. The name of the script task will show up in the log's source column.

Double-click the script task and specify the ReadOnlyVariables if you want to display their values as your custom message. In this example I use Microsoft Visual Basic 2008 as my script language as that is my preferred coding choice because I just can't stand the curly braces of C#. :)

Click Edit Script which will bring up the script editor. Now look at the big block of comments. The information I am providing in this log is right there!

' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)

This is what I used in an existing SSIS package I created.



Public Sub Main()
        Dts.Events.FireInformation(1, "Filename", Dts.Variables("User::SchemaRawfileLocation").Value, "", 0, False)
        Dts.TaskResult = ScriptResults.Success
 End Sub

So you can see that I created an Information event and my message is the value of the SchemaRawfileLocation variable.

On the msdn site you can look at the parameters to pass into the method. Not 100% sure on all the choices but my above example has been working.

These are the parameters:

FireInformation(informationCode as Integer, subComponent as String, description as String, helpFile as String, helpContext as Integer, byRef fireAgain as Boolean)

In my example:

informationCode: 1

subComponent: "FileName" (this does not seem to display in the SQL log though)

description: Dts.Variables("User::SchemaRawfileLocation").Value (the value of the variable, it's displayed in the message column)

helpFile: ""

helpContext: 0

fireAgain: False (no I do not want to fire this event again)

And there it is. Happy coding!

Unexpected error from external database driver

I'm working on an SSIS package today. Nothing really special. Just looping through some Excel files and processing the content. I was unable to set up the Excel Source component because whenever I wanted to select the sheet name (with or without using a variable) I received the following error: Unexpected error from external database driver (22)

Nice! What is causing this? It turned out that the name of the sheet was 31 characters long. I removed the last character, saved the spread sheet and voila! the name of the sheet is in the drop down box. So it seems that while the sheet name limit in Excel is 31 characters when dealing with it in SSIS the name of the sheet cannot be longer than 30 characters because the 31 characters need to be able to include the $ sign which indicates sheet name and not a range name.

Update: this may not have been the problem. I have "fixed" the error by saving the Excel file without changing the name of the sheet. So I am back to drawing board with this error.

Now if I could just figure out the "Value does not fall within the expected range" error that I get when I am trying to view the columns on this same component.

Update: I resolved this last error by deleting my Excel Source component and recreating it.

SSIS - Office 2010 Woes

Good news: I got a new laptop at work that's much faster than my previous one.
Bad news: new laptop = setup issues.

My previous laptop was 32-bit and this one is 64-bit which came with its own challenges. I have the 64-bit Office 2010 installed on it. If you have ever worked with SSIS in a 64-bit environment then you know where this post is going.

I am working on an SSIS package that import data from Excel 2007 to SQL Server. I had created the package on my previous 32-bit laptop and I have to tweak it. Now I get this error: "
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine."

I search for a solution and I come across a few things. One is to set my project to use the 32-bit runtime. Another one is to download the Microsoft Access Database Engine 2010 Redistributable which has backwards connectivity components. Another thing suggested changing the connection string to Microsoft.ACE.OLEDB.14.0.

Neither of the above solutions have worked. What eventually worked is downloading and installing the 2007 Office System Driver: Data Connectivity Components. Just for reference this is my connection string: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=filename.xlsx;Extended Properties="EXCEL 12.0;HDR=YES;IMEX=2";

I hope this post will help someone facing the same problems. Merry Christmas!

Just a note: in order to install the Access Database Engine 2010 32-bit version I had to uninstall any 4-bit versions of Office.

32-bit vs 64-bit in SSIS

My recent project involved using a DSN as the connection string in SSIS. The challenge of the project was that I only had the 32-bit version of the driver to create the system DSN but the server was a 64-bit server. In Administrative tool there IS a 32-bit version of the ODBC editor so that was not a problem.

However, in order to make everything work in SSIS I had to do 2 things.

1) To debug in BIDS I had to change one of the solution properties. Under Debug set the Run64bitRuntime option to False.

2) In order to use the 32-bit runtime once the package was set to run as a SQL job in SSMS I had to check that option on the Step setup on the Execution Options tab.

After this everything was running smoothly.

Cannot fetch a row from OLE DB provider "BULK" for linked server

Today I come across the error "Cannot fetch a row from OLE DB provider BULK for linked server" in one of my SSIS packages. So I research this error and I find that this seems to be a catch-all error for SQL Server Destination. Therefore it's not very useful as you don't know exactly what causes it. The fix for me is changing the MaxInsertCommitSize from 0 to 99. You can find this property in the Advanced Editor for SQL Server Destination on the Component Properties tab.

Update 2-21-2011: I found another set of condition that may be causing this error. I used a SQL Server Destination but the connection manager was OLEDB. Once I changed the destination to be OLE DB Destination the error went away. The odd thing is this combo worked until I added a Lookup to my Data Flow. [insert rolling eyes here]

Mind-boggling Problem!

I created an SSIS package that reads a comma delimited (CVS) file from a network share, loads it into a SQL Server table and then moves the file to a folder called Completed. Simple, right? After fighting my battle with the "truncate" error that you could see in my previous post the package runs fine. So it's time to set it up as a SQL job.

I set up the job, run it and it says success but there is no data in the database and the file has not been moved to the Completed folder.

I added logging to the package so that everything will be logged onError, onInformation, and onWarning to the Windows Event log. (You can do that by going to the SSIS>Logging menu item.)

I see that I have a Warning in the event log that the job could not find any files. I check on the permissions and I see that everything is set up right. Both the share permissions and the Security tab shows Modify permissions for the group that the SQL Agent user is in.

I spend all morning with a co-worker of mine (thanks Dennis for your time!) trying all kinds of crazy workarounds and solutions. And what we found that worked is mind-boggling!

If we change the permissions on the share to give Modify rights to the SQL Agent user itself (not just the group it's in) everything works!!!!

Can someone please explain to me why?????

SSIS - Annoying Truncation Error

Several of my SSIS projects involve importing data from CSV files. Most of the time if the CSV file contains any text the import will result in the following error in SSIS: "Text was truncated or one or more characters had no match in the target code." I have used a few workarounds to avoid this message. Most of the solutions were posted in a thread on Technet. I will post here the couple of workarounds that has worked for me.



Workaround #1
The Jet engine determines the column types and lengths by the first 8 rows. If after the first 8 rows there are rows that contain text data longer than what is in the first 8 rows, you will get that error. So you can put in a fake row in the very first row with long strings of text. Or move one of the existing real data row with long strings to the first row. It's a bit clumsy but works.


Workaround #2
Convert the CSV file to an Excel workbook. Works like charm!


One other solution has included messing with the registry to tell the JET engine how many rows it should base its guess on the data length. Check out the previous page I posted on the details. I have not tried that but I may have to do it in the future.

SSIS - A rowset based on the SQL command was not returned by the OLE DB provider.

I have had a dataflow set up to transfer some snapshot data from one database to another that has been working fine. However, I got a task of adding additional data to the dataset as well as back-filling my stored snapshot data as well. Obviously I warned my managers this was not a good idea as the backfilled snapshot data will not be correct. Alas, I still had to do this.

To make this task quick I figured I would just reuse my already existing dataflow and I would just modify the stored procedure that returns the recordset temporarily to get the old data.

My original query in the procedure was just simple select statement but my temporary query had to use a table variable. I had everything set up and I was ready to run my dataflow. However, I got the "A rowset based on the SQL command was not returned by the OLE DB provider" error. First I thought that perhaps SSIS has a problem with my columns not having exactly the same datatypes as the original query. So I made sure that the temporary table uses the exact same datatypes as the original query. I still got the same error.

I did a little searching on the Internet and I found the very simple solution: put SET NOCOUNT ON at the beginning of my stored procedure. Sure enough everything was fine after that.

I did a couple of tests and it seems like that while the SET NOCOUNT ON statement in the beginning of stored procedures is always a good idea, in SSIS if you use a table variable you MUST have it.