THE SQL Server Blog Spot on the Web

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

Andy Leonard

Andy Leonard is an author and engineer who enjoys building and automating data integration solutions. Andy is co-host of the Data Driven podcast. Andy is no longer updating this blog. His current blog is

SSIS Design Pattern - Collect Enterprise SQL Server Database Metadata With SSIS

This blog has moved! You can find this content at the following new location:

Published Thursday, August 28, 2008 10:42 AM by andyleonard

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



alphatross said:

Great article! But a lot of the screenshots (not all though) appear 'blacked out'.  Is this just me?

August 28, 2008 6:29 PM

Olivier said:

Not just you :-(

September 2, 2008 9:43 AM

andyleonard said:

Hi alphatross and Olivier,

  I'm not sure what's happening with the images. I can see them on all my machines (and I have a few).

  If anyone has any ideas on what's hindering the display of these images and how to fix it, please let me know.



September 2, 2008 10:22 AM

Keith Mescha said:

I can see the pics. Must be blocked on your machine for some reason.

Hey Andy been a while hope all is well.

September 4, 2008 1:51 PM

John said:

Great Article.  I wonder if it would be possible to retrieve the Server Name and Instance Name as 2 separate columns in the "Enumerate SQL Server Instances" script task.  I would like to pass Server Name and Instance Name to the For Each Loop.  Right now, it only passes in the SERVER\Instance as 1 column.

Thanks again for the article.

February 3, 2009 5:40 PM

Lisa said:

I downloaded the package and tested it and I get an error

Error: ForEach Variable Mapping number 1 to variable "User::SQLServerInstanceName" cannot be applied.


Error: The type of the value being assigned to variable "User::SQLServerInstanceName" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Anyone else run into this?

February 20, 2009 3:04 PM

Zehra Nasif said:

Andy, thanks for the great post! I was exactly looking for this information and was happy to see that this was your post.

June 8, 2009 2:53 PM

Chris said:

Thanks - really useful, I've read a small portion of what you've done but solved my problem.

August 13, 2009 9:45 AM

Alan said:

This is awesome!  Is there an easy way that I can use this example and add IP Address to what is collected from the server when it gets the instance name?

September 8, 2009 1:51 PM

BrewMaster said:

Hi guys.. Do you know what causes this exception when using Nested For Each Loop..

Error: Variable "User::xNPU_NM_Files" does not contain a valid data object

April 12, 2010 10:37 PM

moeenk said:

Great post, but my question is why not simple use information schema to retrieve all this information, I mean why reinvent the wheel.


Moeen K.

February 14, 2011 5:05 PM

Chad said:

Andy, this is great article.  I am assumming that the reason that you wouldn't want to go the information schema route is due to the flexibility of encompassing this code into a package. If you write t-sql and place in stored procedures it will not be very flexible.

July 5, 2011 2:17 PM

Chris said:

Hi, Andy.  I know that it has been a while since anyone has commented on this article, but I had a quick question.

Any reason that you are setting the Dts variables to local variables, before using them later in the code?  It is possible to just use the Dts variables when defining the string to be displayed in the message box.  I was just curious as to the reason that you are doing it the way that you are.


October 22, 2012 1:26 PM

Melodie G. said:

Hi Andy.  Thanks for this.  I was trying to nest a foreach - the outer to loop over like tables, and the inner to loop over distinct report dates within the table.  I had that part, but was having difficulty using the inner loop variable in my query within the inner loop.  Don't know what it was, but reading through your article a light bulb went off, and now it works.  Thanks.

May 8, 2013 3:59 PM

Blake C said:

Hi Andy, Great post. I have a question about using the smo to iterate through the domain to identify SQL Servers. Will the SMOapplication iterate across multiple domains in an enterprise or must it be run in each domain. Thanks,  Blake

July 19, 2013 7:59 AM

Leave a Comment


This Blog



My Latest Book:

Community Awards

Friend of Red Gate

Contact Me


Privacy Statement