SSIS Server+Database For Each Loop

Question

I have a source table to collect server and database names, having many databases for any given server.  I've figured out how to pass the server name into a result set & variable in order to create a dynamic connection string to connect to each of these servers.  But after that I also need to retrieve information from each of the databases gathered from the same source table and I'm not sure how to pass the database name into a variable and subsequently into a parameter for a SQL statement in order to collect the data.  Unless there's a better way, SSIS seems to be getting stuck on reading the parameter because I need to use dynamic SQL for the query, i.e. SET @sql = 'SELECT * FROM '+QUOTENAME(?).dbo.mytable' EXEC (@SQL)

Answer 1

You can populate a variable with the database and then build your source statement using an expression to set the SQLSourceStatement in the Execute SQL task.

I'm not sure i understand the issue with databases? Do you need a for each loop container to iterate through a set of databases?

---

every day is a school day

Answer 2

You have fetched the server name into a variable. (Using execute sql tas and result set is captured in an object variable say obj_servername)
Then take a foreach loop:
Select the foreachado enumerator and select the variable (obj_servername) from the drop down box.
In the result set, select a variable name (servername) and set the index as 0
Then inside the foreach loop, take a execute sql task and write the select statement: select databasename from the tablename where servername=?. Then do the parameter mapping.
Capture the full result set in a variable (obj_databasename).
Now take one more foreach loop inside the original foreach loop.
Configure this foreach adoenumerator and select the variable obj_databasename from drop down box.
In the resultset of foreach loop, select a variable (databasename) and set the index as 0.

Now, the variable servername has the name of the server and the variable database name has all the database names.
To create the sql query, you can use a variable SQL with expression:
"Select * from " + @[User::databasename] + ".dbo.mytable"

Now use this variable (SQL) inside your DFT.

---

Nitesh Rai- Please mark the post as answered if it answers your question

Answer 3

Thanks for the assistance.  I follow your approach up to the last point, i.e. using the @User::DatabaseName variable.  To clarify, are you saying that I should put the entire dynamic SQL statement with the @User::DatabaseName variable into an Expression and then place that in another variable, to use as a "SQL command from a variable" in the Data Access Mode in the OLE DB source?  Or do you mean I would just use the @User::DatabaseName variable inside the dynamic SQL statement in a "SQL command"..in which case it's wanting the variable to be declared...

Thx - Shane.

Answer 4

I wnat to use DatabaseName variable and create an expression for a variable (SQL).
Then use SQL variable as datasource inside DFT. (Data Access Mode as SQLCommand from variable)

---

Nitesh Rai- Please mark the post as answered if it answers your question

Answer 5

OK, I think I follow but looks like it still doesn't work.  Let me know if I'm doing something wrong here.

I created a new string variable called sDBQuery.  In its properties window, I changed the EvaluateAsExpression to True and put the following test query in the Expression box:  "DECLARE @sql nvarchar(2000) SET @sql = 'SELECT * FROM '+QUOTENAME(@[User::sDatabaseName]+'.dbo.testTable' EXEC (@SQL).  I try to use this new sDBQuery variable in the "SQL Command from variable" in my OLE DB Data Source but I get an error:

"...Statement could not be prepared.".  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "Must declare the scalar variable"@".".

So - it looks like it wants to try to process or validate the the databasename variable as a SQL variable vs. an SSIS variable when I click OK.

Thx,

Shane

Answer 6

 Lets split your expression:
DECLARE @sql nvarchar(2000)  - (Create a variable with name as SQL and type as string)

For setting the value, you change the evaluateasexpression property of SQL variable as TRUE and write following expression:
"SELECT * FROM " + "QUOTENAME(" + @[User::sDatabaseName]+ ").dbo.testTable"

EXEC (@SQL) - (Can be implemented by selecting data access mode as SQL Command from varibale inside DFT)
So the dynamic sql is ready in the variable SQL.
What is QUOTENAME?

---

Nitesh Rai- Please mark the post as answered if it answers your question

Answer 7

OK, I think part of the issue was with " in SSIS Expression box vs. ' in SQL Server.  So, I see that it now actually evaluates the @[User::sDatabaseName] variable, but it returns a blank because it hasn't been populated yet with the values from the EXEC SQL Task into the obj recordset.  

Now it evaluates the statement to - SELECT * FROM QuoteName().dbo.testTable - which the DFT doesn't like because it can't parse the SQL without a value in the ()  

How can I get around this?

BTW - QUOTENAME puts brackets around the database name in case there are spaces or goofy characters in the name, i.e. [My Database] or [My-Database]

Thx,

Shane

Answer 8

(1) I've got a DatabaseName variable = sDatabaseName, i.e. User::sDatabaseName (string)

(2) And then let's say I create another string variable called SQL, i.e. User::SQL.

(3) If I understand correctly, you want me to put the query in the expression box of the SQL variable from step (2)

(4) And then you want to use that SQL variable in the drop down for "SQL command from variable"

The trouble is that I can't click OK and close the OLE DB source because it can't evaluate the expression - the User::sDatabaseName variable is populated with nothing at this point.

Sorry if I'm getting it confused.

Thx,

shane

Answer 9

I can also use a temp table via some trickery with an EXECUTE SQL Task statement, but my destination is ultimately an Oracle table and I don't think Bulk Insert works with that as a destination...unless there's some other way to do that with Oracle as the destination and EXEC SQL Task as the source.

Yes - I need to iterate through a set of databases to pull data from tables in them.

thx,

shane

Answer 10

The trouble is that I can't click OK and close the OLE DB source because it can't evaluate the expression - the User::sDatabaseName variable is populated with nothing at this point.

Sorry if I'm getting it confused.

Thx,

shane

Give a valid database name to the variable User::sDatabaseName so that yuo can do the mappings inside DFT.

Also , this approach will work only when the number of columns, name of the columns and datatype of the columns returned by the dynamic query (SQL variable in this case on each iteration of the for each loop) are same.

---

Nitesh Rai- Please mark the post as answered if it answers your question

Answer 11

OK - how do I set the @[User::sDatabaseName] with an initial value?  

thx,

Shane

Answer 12

Go to control flow.
Rt click and select variables.
Then go to the datbasename variable and assign the value in the last column.

---

Nitesh Rai- Please mark the post as answered if it answers your question

Answer 13

Thx - I think this will work - I just need to test it now.  

However, I don't think SSIS liked the QUOTENAME function, but maybe I just need to get the syntax right for the SELECT statement inside the expression box.

Thx,

Shane

Answer 14

Excellent - this helped a lot.  Everything is executing fine - except - the DFT that takes the data from the source to the destination isn't firing.  Am I missing something somewhere on the DFT to capture the servername or databasename variable somewhere?

Thx,
Shane

Answer 15

So you mean to say that when you are using SQL Command from variable as data access mode and trying to do the mappings then you are not getting the proper column names..corect?
Again, you need to do a bit of exercise:
Create a connection manager for a databse. (Make it for the database which is being used as the initial value of variable databasename)
Then inside DFT, take the OLEDB Dource and set data access mode as SQL Command.
Write the command as Select * from dbname.dbo.tablename and then complete the data flow task.
Once the DFT is finished, select the OLEDB Source and go to its priperties.
In Custom Properties, set the Access Mode as Sql Command from variable.
Select the variable name in "SQL Command variable" property.
Then set the "Validate External Metadata" property to Fasle.

---

Nitesh Rai- Please mark the post as answered if it answers your question

Answer 16

I can retrieve the columns and can map them between source and destination correctly too. Everything executes fine- exec SQL task to get list of servers, the for each loop to cycle through servers, the exec SQL task to get the list of databases, and the for each database loop. But the data flow task isn't executed inside the for each database loop container. Would it be the validateexternalmetadata property=false for the DFT?

Answer 17

I can retrieve the columns and can map them between source and destination correctly too. Everything executes fine- exec SQL task to get list of servers, the for each loop to cycle through servers, the exec SQL task to get the list of databases, and the for each database loop. But the data flow task isn't executed inside the for each database loop container. Would it be the validateexternalmetadata property=false for the DFT?

Is the DFT failing? What is the error message?

---

Nitesh Rai- Please mark the post as answered if it answers your question

Answer 18

The package doesn't fail.  The DFT just doesn't run.  Everything else runs and turns green.

Answer 19

It looks like the initial value assigned to the User::sDatabaseName variable isn't getting changed from my EXEC SQL Task that's supposed to feed it with the iteration of database names for each server and I'm not sure why it isn't passing in the value - rather, it's just keeping the same initial value for each server I connect to.

Thx,
Shane

Answer 20

Actually, I think I found the problem.  Using the BreakPoint, it looks like I'll just need to change the way I'm building the connection string to each server.  I was errantly assigning the whole string to the User::sInstanceName variable instead of just the server name and then building the string after that in the script task.

Answer 21

Actually, I think I found the problem.  Using the BreakPoint, it looks like I'll just need to change the way I'm building the connection string to each server.  I was errantly assigning the whole string to the User::sInstanceName variable instead of just the server name and then building the string after that in the script task.

Yes,
You need to write an expression for connection string using instancename and databasename.

---

Nitesh Rai- Please mark the post as answered if it answers your question