SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Don’t Use USE (in SSIS, at least)

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]

Tim Mitchell

Tim Mitchell is a business intelligence consultant, author, trainer, and Microsoft Data Platform MVP with over thirteen years of data management experience. He is the founder and principal of Tyleris Data Solutions.

Tim has spoken at international and local events including the SQL PASS Summit, SQLBits, SQL Connections, along with dozens of tech fests, code camps, and SQL Saturday events. He is coauthor of the book SSIS Design Patterns, and is a contributing author on MVP Deep Dives 2.

You can visit his website and blog at TimMitchell.net or follow him on Twitter at @Tim_Mitchell.


Posted by ghoyt on 20 August 2009

great post thanks for the tip.

Posted by Chintak Chhapia on 23 August 2009

Really a great tip

Leave a Comment

Please register or log in to leave a comment.