SSIS package executed without errors but wrote 0 rows

I have encountered and resolved an SSIS issue today where the package was executed successfully without errors, but close inspection of the data flow pipeline showed no row count. I think it is a good idea to write a post on this topic so other people can benefit from my observations.

I am going to reproduce the issue with a quick demo.

1. Create a new SSIS package with a data flow task. Use OLE DB as the source and Flat File as the destination.

image

2. Configure the source using a SQL command.

image

3. Before we go any further, let’s test the SQL command by

a) Clicking on “Parse Query”

image

b) Clicking on “Preview…”

image

c) Looking at “Columns”

image

All seems to be okay at this stage right? Wait and see.Smile

4. Configure the destination and since this is not an important aspect of the demo, I will skip this step. I am using a Flat File destination but I think it is okay to use any other destination types.

5. Execute the package and you should see something like this:

image

image

Notice there is no row count and the output showed the pipeline wrote 0 rows. Crying face

6. I have found two ways of working around the issue:

a) Remove the USE statement at the top of the SQL command.

image

b) Insert a SET NOCOUNT ON statement at the start of the SQL command.

image

7. Now execute the package again and you should see it working properly. Open-mouthed smile

image

image

Duplicate Checksum

I was using checksum() to perform a reconciliation task and found that for a decimal number, the positive and the negative value will produce the same checksum.

image

Even though I knew that checksum() is not perfect for checking differences, but I was still quite surprised to find how easy it is to produce duplicate checksums.

The following table lists some of my observations.

Expression Checksum
checksum(1)
checksum(-1)
1
-1
binary_checksum(1)
binary_checksum(-1)
1
-1
checksum(1.0)
checksum(-1.0)
-1374215283
-1374215283
binary_checksum(1.0)
binary_checksum(-1.0)
-1374215283
-1374215283
checksum(‘1’)
checksum(‘-1’)
65
65
binary_checksum(‘1’)
binary_checksum(‘-1’)
49
737
checksum(‘1.0’)
checksum(‘-1.0’)
-1043840145
-1043840145
binary_checksum(‘1.0’)
binary_checksum(‘-1.0’)
13264
189392

Looking at the table above, I think I will stop using checksum() in the future.