DATABASE ADMINISTRATION
T-SQL in SSIS: The power and the weaknesses
By Serdar Yegulalp
02.23.2007
Rating: -3.33- (out of 5)
|
SQL Server Integration Services, or SSIS, provides a number of different mechanisms to create and pull data from a data source. One is the ExecuteSQL task, which lets you use a T-SQL statement, much as you might pass a bit of T-SQL from a front-end application to SQL Server on the backend. The T-SQL in question can be a full statement or a reference to an existing stored procedure.
T-SQL, however, is not the only way to get data into SSIS. There's also the Data Flow task, which leads people to ask: Why use T-SQL in SSIS? Why not just use data flows? What's the advantage to T-SQL, if any? I went searching for a response to those questions, and found a number of very well explained answers at Jamie Thomson's blog at Conchango. I'll rephrase a few of them here, along with some of my own commentary gleaned from personal experience.
Using an existing piece of T-SQL or stored procedure is a timesaver, rather than recreating the whole thing in a data flow. If you have an existing stored procedure that took a good deal of sweat and concentration to build, there's no point in tearing it down and rebuilding it; you can simply use it as-is. There are some slight differences in the syntax for evoking a stored procedure (mostly the way parameters are passed), but very little that would prevent existing stored procedures from being reused in ExecuteSQL. Later, I will go into a couple of things that might cause problems.
Your SQL Server programmers don't have to learn much of anything new to make their code work in SSIS. They can write T-SQL code, and it can simply be dropped into SSIS workflows, with relatively little modification. If you're taking over someone else's work and migrating it progressively into SSIS, you may be more inclined to reuse the existing work instead of trying to re-engineer something from scratch.
The majority of work is done wherever your database is, instead of where SSIS itself is running (which ma
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
y not be the same machine). This may not be an advantage, but it is a behavior worth noting. It can work for you or against you.
T-SQL uses transactions within the database itself instead of across MSDTC (as SSIS does).
Now, here are some reasons why T-SQL in an SSIS data flow may not be a good idea:
Some stored procedures do not expose data in a preview. The simple reason is that it's not always possible to predict what columns a given stored procedure will produce when it's run. That said, some stored procedures can't be used in place of a data flow if you need a predictable "output contract" for that data flow. However, you can use a view to expose a data flow preview. The view is bound tightly to the schema(s) it works with and doesn't change, unless you explicitly recast it (i.e., you edit it). So if you have an existing view that you want to use as a data source in SSIS, you'll have previewing as a bonus feature.
SSIS data flows are better for more complex, multi-step operations. These operations include jobs that require:
These are things that are not always done elegantly or efficiently in the context of T-SQL.
SSIS data flows are self-documenting, which is another possible advantage of SSIS data flows over T-SQL. But, it's true only to a point. If you don't have any understanding of the schema being accessed, you're just as likely to be in the dark. On the other hand, stored procedures and T-SQL in general are not self-documenting at all -- unless whoever wrote them took the time to document each in detail (and how often does that happen?).
From all of this, it's possible to derive two simple rules about using T-SQL versus data flow:
| ABOUT THE AUTHOR: |
|
Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!
Copyright 2007 TechTarget
|
|
|
|
|
|
|
|
| TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
|
