May 14, 2012 at 11:00 am
Hello,
I had to write a validation stored procedure for a new IT process. The process runs over 80 databases and involves 7 validation steps.
Each step has a its own validation table. Each validation table has specific columns. Each step requires me to run a query over the 80 databases, find any records and then dump the resultset into the appropriate validation table.
Some of the queries are quite lengthy. I am using dynamic SQL to
1) Create the SQL for the appropriate step
2) Run the SQL against each database
I'm trying to translate the stored procedure to an SSIS package. It is not quite translating.
In a stored procedure I create a table variable and use it to hold temporary data, process it and then dump results into results table.
insert @dblist(db)select name from master..sysdatabases where name not in ('master','model','tempdb','msdb')
select @dbname=min(db) from @dblist
1) How do you break out of a foreach loop container?
while @dbname is not null --outer loop
BEGIN
--Testing for one database
--************************
if(@MyDebug) = 2
BEGIN
if @dbname = 'SampleDB'
BEGIN
BREAK
END
select @dbname=min([db]) from @dblist where [db] > @dbname
END --While @dbname
In my stored procedure, I use a While loop to traverse all databases? I have a variable that I use to debug and only look at one database. I check the variable and if it has a certain value, I break out of the loop. I am using an ADO recordset for the foreach loop?
How do you break out of a foreach loop container?
================================
2. How do you map variables to use data flows?
How do I map the variables to use in "USE thisDB", etc. For example:
dynamic SQL
***********
SET @BigSQL = 'USE ' + @dbname + '; EXEC sp_executesql N''' + @sql + '''';
PRINT(@BigSQL);
In a dataflow, how do you initialize the "?" with the current database name from the for each container?
========================================================
"Use ?" doesn't seem to work when trying to map ? to the current database name variable from the foreach container.
Hopefully my questions are written in a coherent fashion. Due to stress level, they may not. If so, I'll try to clarify what I am doing and what I am asking.
Thanks.
Tony
Things will work out. Get back up, change some parameters and recode.
May 14, 2012 at 12:56 pm
WebTechie38 (5/14/2012)
1) How do you break out of a foreach loop container?
The Foreach Loop Container handles that for us. It breaks when you reach the end of whatever collection you instructed it to iterate over. I think you may want a For Loop, not a Foreach Loop. In the For Loop you set the condition to evaluate in each loop. You can create a variable to control and have the condition set to something like @User::VariableName == 1. Have the variable initialized to 1 when the package starts and when you're finished looping programmatically set the variable to something other than 1 using a Script Task to force the For Loop break.
2. How do you map variables to use data flows?
Try fully-qualifying the proc call, like this:
SET @BigSQL = 'EXEC ' + QUOTENAME(@dbname) + '.sys.sp_executesql N''' + @sql + '''';
PRINT (@BigSQL);
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 15, 2012 at 1:14 pm
Hello,
1) Yes, I am using a foreach container.
I have over 80 databases. For debug purposes, I need to stop when the name of the database gets to a certain database.
Database1
Database2
Database3
Database4 - breakout
In a stored procedure, I would do this:
--Outer Loop for database name
--****************************
while @dbname is not null
begin
set @DB = @DBNAME
--Testing for one database4
--************************
if @dbname = 'Database4'
BEGIN
BREAK
END
How would I do this with a foreach loop container?
2) In the foreach loop container, I have an executeSQL:
User ?
select * from GL00100 where Status = 'A'
I want to map ? to @dbname. This way I can loop through the 80 databases, run the SQL and then place the result into a master table that contains the results from all 80 databases.
I am getting an error message:
failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_I4)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Thanks for the help.
Things will work out. Get back up, change some parameters and recode.
May 15, 2012 at 2:24 pm
WebTechie38 (5/15/2012)
Hello,1) Yes, I am using a foreach container.
I have over 80 databases. For debug purposes, I need to stop when the name of the database gets to a certain database.
Database1
Database2
Database3
Database4 - breakout
In a stored procedure, I would do this:
--Outer Loop for database name
--****************************
while @dbname is not null
begin
set @DB = @DBNAME
--Testing for one database4
--************************
if @dbname = 'Database4'
BEGIN
BREAK
END
How would I do this with a foreach loop container?
It is not technically supported. Can you change how you're filling the collection of databases to iterate over?
SELECT *
FROM sys.databases
WHERE name <= 'Database4'
ORDER BY name;
Another option is to implement a Script Task within your Foreach Loop Container (FELC) and have it manage whether to proceed to the rest of the Tasks in the FELC based on the name and a precedence constraint.
2) In the foreach loop container, I have an executeSQL:
User ?
select * from GL00100 where Status = 'A'
I want to map ? to @dbname. This way I can loop through the 80 databases, run the SQL and then place the result into a master table that contains the results from all 80 databases.
I am getting an error message:
failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_I4)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
DBTYPE_I4 is an SSIS Integer. Check to make sure your variable is defined as a String.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply