Finding all views with a specific name, in all databases

Hi,

We are migrating to SQL Server 2005 from 2000 and we have a view created in many of our databases. That view must be changed after the migration because it uses system tables and does not return the correct information in SQL Server 2005.

To do that, I want to create an SSIS-package that loops through all the databases on a particular server. If that view exists in the database, I want to run a script that change the view.

What I try to do:

Set up a For Each Loop container with Foreach SMO Enumerator

Set the connection to my server

Set the Enumerate property to "SMOEnumObj[@.Name='Databases']/SMOEnumType[@.Name='Names']"

On the Variable Mapping page, place Index 0 in Variable User::dbName

In the For Each Loop, place a script task to msgbox the value of User::dbName

This all works good. The problem comes when I try to nest the For Each Loop

Inside the "For Each Database Loop", place a new For Each Loop container with Foreach SMO Enumerator

I connect the Msgbox Script Task with the new For each loop

Use the same connection

Set the EnumURN property in the Expression Builder to "Database[@.Name='" + @.[User::dbName] +"']/SMOEnumObj[@.Name='Views']/SMOEnumType[@.Name='Names']"

On the Variable Mapping page, place index 0 in Variable User::tabName

In the For Each Loop, place a script task to msgbox the value of User::tabName

When I try to run the package now, it does not at all.

In my Progress tab I see that Validation has started and completed for each task, but I see now errors. Nothing has run.

Test 1: I change the DelayValidation for the Inner Loop to True

Now the package runs, but I never get to the script task in the inner loop.

Question 1: What's the problem?

Question 2: Is there another way to do this?

Regards

Magnus

Have you tried using a second connection? It seems like the above should work for you, but maybe there's an issue with the SMO Enumerator that requires a different connection for the inner loop.|||

Hi Sean, and thanks for your response.

I tried to use a second connection, but I still get the same result.

/Magnus

|||Forget the SMO enumerator in your Foreach loop.

Do an Execute SQL Task and use something like:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME LIKE 'VIEWNAME%'

Then set the resultset to full result set and create an object type var to hold the results. Then loop thru the results using the ForEach ADO.

See this example. I found it very helpful.

http://www.cubido.at/Blog/tabid/176/EntryID/59/Default.aspx|||

Hi Tom, I tried your suggestion and it worked alright. Thanks!

Because I only expect a single row, I used a Single Row instead of Full ResultSet and used a SQL like

SELECT ISNULL(Max(TABLE_NAME), '') AS TableName FROM [databasename].INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'viewName'

I set the SQLStatement with an expression since I needed to change the databasename for each iteration. I placed the field TableName in a variable. Before I move on to the next step, where I want to alter the view, I use a Constraint and Expression constraint and make sure that the variable is not empty.

Then I create an Execute SQL task to alter the view. I use the same connection as in the above task and use an expression to create the SQLStatement to something like:

USE [databasename]
GO

ALTER VIEW [dbo].[viewName]
AS
SELECT *
FROM theTable

/Magnus

|||

I had the same problem except I wasn't using SMO Enumeration for the outter loop. You can't just use set the EnumURN property in the Expression Builder to...
"Database[@.Name='" + @.[User::dbName] +"']/SMOEnumObj[@.Name='Views']/SMOEnumType[@.Name='Names']"

Your missing two parts. You have to set the Expression to...
"RuntimeServer[@.Name='<the name of your SMO Connection in the Connection Manager>']/Server[@.Name='<the server name>']/Database[@.Name='" + @.[User::dbName] +"']/SMOEnumObj[@.Name='Views']/SMOEnumType[@.Name='Names']"

Hope this at least helps someone down the road. It took me 4 hours to figure it out! Either I'm retarded or it's not explained well.