Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Tim Mitchell

Tim Mitchell is a business intelligence consultant, author, trainer, and SQL Server MVP with over a decade of experience. Tim is the principal of Tyleris Data Solutions and is a Linchpin People teammate. Tim has spoken at international, regional, and local venues including the SQL PASS Summit, SQLBits, SQL Connections, SQL Saturday events, and various user groups and webcasts. He is a board member at the North Texas SQL Server User Group in the Dallas area. Tim 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 twitter.com/Tim_Mitchell.

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]

Comments

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.