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.
2. Configure the source using a SQL command.
3. Before we go any further, let’s test the SQL command by
a) Clicking on “Parse Query”
b) Clicking on “Preview…”
c) Looking at “Columns”
All seems to be okay at this stage right? Wait and see.
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:
Notice there is no row count and the output showed the pipeline wrote 0 rows.
6. I have found two ways of working around the issue:
a) Remove the USE statement at the top of the SQL command.
b) Insert a SET NOCOUNT ON statement at the start of the SQL command.
7. Now execute the package again and you should see it working properly.