Salvo(z)

SSIS does not include an ‘out-of-the-box’ isNumeric( ) function.  Fortunately, this functionality can be implemented using the script component in the data flow.

I am going to make the assumption that readers are familiar with the SSIS script component.  If not, please feel free to read my prior blog post on the subject: SSIS Dataflow Script Component

To implement isNumeric( ) behavior, I used the C# Int32.TryParse method.

TryParse(String, Int32):  Converts the string representation of a number to its 32-bit signed integer equivalent. A return value indicates whether the conversion succeeded.

Here’s a snippet of the code in C#:

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    int BatteryQuantity1Int;

    if (! int.TryParse(Row.BatteryQuantity1Input, out BatteryQuantity1Int))
       BatteryQuantity1Int = 0;   

    //Add an output row and set the column values
    Output0Buffer.AddRow();
    Output0Buffer.ModelNumber = Row.ModelNumberInput;
    Output0Buffer.ModelYear = Row.ModelYearInput;
    Output0Buffer.ModelName = Row.ModelNameInput;

I also came across a blog post by Dustin Ryan where he implements IsNumeric( ) functionality using the Derived Column Transform.  The post is found here.

SSIS logging is configured on a package-by-package basis.  SSIS provides a flexible event logging environment that allows developers to configure: the container or scope to log (generally the entire package), the type of events to log, and the log provider type (SQL Server, Windows Event Log, Text Files, SQL Server Profiler or XML Files).

Whenever I set up logging in an SSIS package, I always log events at the package level using the SQL Server log provider.  The type of events I log are: On Error, On Post Execute, On Pre Execute, On Task Failed, and On Warning.

The following is a list of steps to configure package logging as I described above:

Right click on the control flow surface and select ‘Logging’ (or select ‘Logging’ in the SSIS menu)

In the ‘Containers’ section, you select the container or scope for logging.  I always select the package level (to log events in the entire package) by checking the box next to the package name. 

In the ‘Add a new log’ section, select ‘SSIS log provider for SQL Server’ then click ‘Add’.

In the ‘Configuration’ field, select the SQL Server database where you want to store logged events.

Next, click the ‘Details’ tab.  On the details tab you specify the types of events you want to log.  I always select the following: On Error, On Post Execute, On Pre Execute, On Task Failed, and On Warning.

• On Error – Writes a log entry when an error occurs.
• On Post Execute – Writes a log entry immediately after the executable has finished running.
• On Pre Execute  – Writes a log entry immediately before the executable runs.
• On Task Failed – Writes a log entry when a task fails.
• On Warning – Writes a log entry when a warning occurs.

When finished, click ‘OK’. 

You should also create an event handler for each type of event you’re logging.  To create an event handler, click the ‘Event Handlers’ tab.

In the ‘Event Handler’ drop-down, select the appropriate event type.  Then click the link to create an event handler for that specific event.  You need to repeat this process for each type of event (On Error, On Post Execute, On Pre Execute, On Task Failed, and On Warning)

When you’re finished, the list in the ‘Event handler’ drop-down should look like this:

Logged events are stored in the dbo.sysssislog system table in the database you specified while configuring package logging.

I discovered some undesirable behavior with the SSIS  Excel Data Source.  Although this issue has been well-documented for a while, I thought it is worth mentioning again.

I had one column in my spreadsheet that was sparsely populated, nearly all blank values.  When I ran my SSIS package to extract the data from the spreadsheet, every row in that column was set to NULL including those that had a valid value in the spreadsheet.

Upon further investigation, I discovered the issue stems from the Jet driver.  Apparently, by default, only the first 8 rows of data are scanned to determine the data type for each column.  If the first 8 rows of data in a column are blank, then any valid data in the additional rows is ignored and treated as NULL.  It is possible to change the value in the registry so more than 8 rows are scanned.

In addition, I also discovered that if you use the Excel data source and want to run your package from BIDS, then ‘Run64BitRuntime’ in the solution properties must be set to False.  You can configure this as follows:

Right click on the solution in BIDS and select ‘Properties’.

Set Run64BitRuntime to False.