THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

SSIS code smell – Unused columns in the dataflow

A code smell is defined on Wikipedia as being a “symptom in the source code of a program that possibly indicates a deeper problem”. It’s a term commonly used by our code-writing brethren to describe sub-optimal code but I think the term can be applied equally well to SSIS packages too as I shall now explain

One of my pet hates about SSIS development is packages that throw warnings of the form:

The output column "ColumnName" (1358) on output "OLE DB Source Output" (1289) and component "OLE_SRC Name" (1279) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance. 

The warning is fairly self-explanatory – any column that appears in the data flow but doesn’t get used will throw this warning when the data flow is executed. Its not the negligible performance degradation that they cause that bothers me though, it’s the clutter that they cause in your log file/table. Take a look at the following screenshot if you don’t believe me:

data flow task column warning

There are 231409 such warnings in the system that I took this screenshot from, that is 231409 log records that should not be there. The most infuriating thing about this warning is that it is so easily avoidable; eliminating such columns is a very quick and easy thing to do in the SSIS Designer. The only problem I see is that the warnings don’t occur until you execute the package – it would be preferable for the designer to have an unobtrusive way of informing you of them as well. Anyway, I digress…

I consider such warnings to be a code smell because, to me, they’re symptomatic of a lack of due care and attention; a lack of developer discipline if you will. What other code smells can you think of when building SSIS packages? If I get a good list in the comments maybe I’ll compile them into a later blog post.

@Jamiet

Published Wednesday, April 21, 2010 9:26 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Mike H said:

As a rule, I don't consider my work done if I run a package and find warnings. Mostly because it can lead me down a blind ally if I have trouble to analyze later.

I don't know if this counts as a code smell, but hard coding something that could and should go in the package configurations (like db connections) might be a candidate.

April 21, 2010 4:40 PM
 

Koen said:

Nice post.

Code smell in SSIS packages?

Like Mike H said, any warning or error that is left (they also annoyingly pop up when you open the package, so it's best to get rid of them as soon as possible).

Also, leaving NULLs unattended; not paying attention to error output of lookup components (the package can work fine during design, but it can crash later on); using select * or the dropdown box; and for me personally, not paying any attention to the layout of your control or data flow. If it is a mess, it's real hard for someone else to get to know the package quickly.

April 22, 2010 2:47 AM
 

Todd McDermid said:

If you agree with Jamie that this smell "stinks" - go vote for this Connect suggestion from Mark Stacey (@MarkGStacey): http://bit.ly/acyKHe

April 22, 2010 3:03 AM
 

Andy said:

My "bad smells" would be

Multiple sorts in the same data flow

Inconsistent layout, mix of horizontal and vertical flows

Unused connection managers

Using event handlers to affect control flows

Lots of log files generated from same data flow

Multiple flows to the same table with no control flow (potential locking issue, results could change dependant on parallel processing)

April 22, 2010 5:39 AM
 

Paul said:

I must apologize to those who have to follow my SSIS packages.  I have lots of packages that have this warning.  I have found not found this "very quick and easy thing to do in SSIS Designer".   For example, if I add a new derived column because I need to change to a different data type, they only way I see to get rid of the now-useless original column is to ADD a union or a sort transform which allows me to quickly select the output columns.   What "very quick and easy thing" am I missing?

April 22, 2010 11:24 PM
 

jamiet said:

Hi Paul,

That's a fair question. Any column that produces this warning is not being used in the dataflow; the scenario that you mention (producing a ne column from an existing one) would *not* cause this warning to occur because the column IS being used.

The warning itself tells you from which component the column originated from so you would have to visit that component and alter it so that the unused columns is not being "passed out" for want if a better term).

Hope that helps.

-Jamie

April 23, 2010 3:05 AM
 

Andy said:

"Any column that produces this warning is not being used in the dataflow; the scenario that you mention (producing a new column from an existing one) would *not* cause this warning to occur because the column IS being used."

This error would occur if you then passed both columns through a union later in the data flow. I got this warning today and realised that the reason I'd not dropped the column was to allow debugging to be done even though I never passed the "input" column through to a output.

April 23, 2010 8:52 AM
 

jamiet said:

Hi Andy,

Yes, if you put them through a Union All component then you would get the warning if you didn't do anything with these columns downstream of the Union All.

The reason for this is that Union All is aynchronous, in turn what this means is that even though those columns in the Union All output have got the same names, they're not actually the same column. You can prove this by comparing the IDs of the columns on the input to and output from the Union All.

-Jamie

April 23, 2010 9:25 AM
 

Ashish said:

warnings does have a potential not only increasing performance but to point to a deeper problem at runtime which can be termed an rarely occuring

so warning should be though of and removed before moving package to production

April 26, 2010 7:29 AM
 

Nicolas said:

Hi,

have you ever had the problem that SSIS threw the warning, but it was a false alarm? I am passing in a column as a parameter to a stored proc that is used in OLE DB task in the data flow. SSIS tells me this column is never used upstream of the OLE DB task, so I removed it. As it turned out, I needed that column as a parameter for the SP.

Any hints why SSIS gave me the warning, although the column was used?

October 19, 2010 10:56 AM
 

jamiet said:

Hi Nicolas,

No, I've never seen false warnings.

I presume you are talking about the OLE DB Command component, yes? Can you provide a simple repro? Feel free to hit the "Email" link at the top of this page if so - I'd be interested to see what's going on.

-Jamie

October 19, 2010 11:09 AM
 

DLW said:

SSIS 2005 - UNION ALL of ODB insert and ODB update error output of which the columns are used upstream, gives you no way I can see to remove the columns from the UNION ALL output - thus I cannot get rid of the warnings. What am I missing?

March 30, 2011 1:03 PM
 

jamiet said:

Hello,

What does ODB stand for?

March 30, 2011 1:35 PM
 

DLW said:

OLE DB data flow destinations, on of an insert and one of an update.

March 30, 2011 1:48 PM
 

DLW said:

I am capturing key information which is then logged so that data errors can be investigated by the users of the system.

March 30, 2011 1:50 PM
 

jamiet said:

From memory - You can remove columns from a Union All component by right-clicking ona  column in the UI and selecting delete.

March 30, 2011 3:57 PM
 

DLW said:

Ah yes that works

Thanks

March 30, 2011 5:32 PM
 

rean said:

is there a resolution for such an error, my ssis package consists of data flow task comprising an oledb data source and a scriptcomponent (as a destination), the need is to process data from the oledb source to a flat file, i did not have an output column but still get this warning message..any suggestion to resolve this will be appreciated.

October 9, 2011 8:45 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement