I ran into a situation this week that brought to light a subtle syntactical error I’d made in creating an SSIS package.  I’ve got a client that has given me access to their development server to create some complex extraction queries, which will eventually be rolled into SSIS packages.  Since I’m working with read-only access and cannot create stored procedures during the development phase, I’m running these queries in an ad-hoc manner.

So, the queries are built and returning a reasonable set of data.  I copy the entire text of the queries into a series of OleDB Data Sources in SSIS, and run my newly created package.  The execution takes only seconds, which, considering the volume of data I’m moving, tells me something has gone wrong.  The package had completed successfully, but the destination files were all empty.  I tested the queries in SSMS again and confirmed the results, but the same query returns no results in SSIS.

The cause of this was a simple but subtle oversight.  When I copied the query text into the command window in the OleDB Data Source, I had inadvertently also copied the USE [DATABASE_NAME] declaration included in each query.  The inclusion of the USE [DATABASE_NAME] statement caused each data source to fire without error, but returned no rows from the source.

It is important to note that this *should* be a rare problem, since stored procedures are preferable to maintaining complex queries outside the database.  If you have the appropriate permissions and organizational authority to wrap your logic into SPROCs, by all means do so.

So the takeaway is that if you find yourself copying an SQL statement directly into the query window of a data source, make sure you remove any USE [DATABASE_NAME] directives.  Failing to do so can create a bug in your package that is easily overlooked.

[Edited to add SPROC disclaimer 8/23]