October 31, 2011 at 10:05 am
Hi,
I am a bit of a novice when it comes to designing SSIS solutions so forgive me if this is a simple problem to solve but i can't seem to work it out.
I've got a dynamic connection manager that pulls its connection strings from a source table (6 connection strings). If i use an Execute SQL Task i can connect to the 6 different servers, select the @@SERVERNAME, store it in a variable and then use a 2nd Execute SQL Task to save the result stored in that variable into a table on my local server. Those two execute sql tasks are in a foreach loop container and it works great. I get the correct 6 SERVER\INSTANCE results that i am expecting in the table on my local machine.
Now... the problem i have is that i have a query that looks like this:
;WITH dbinfo AS (
SELECT
@@SERVERNAME 'Server Name',
database_id 'Database ID',
DB_NAME(database_id)'Database Name',
FILE_ID 'File ID',
CASE type_desc
WHEN 'ROWS' THEN 'Data File'
WHEN 'LOG' THEN 'Log File'
ELSE 'Other'
END 'File Type',
name 'Logical File Name',
physical_name 'Path',
DatabasePropertyEx(DB_NAME(database_id),'Recovery') 'Recovery Model',
CONVERT(DECIMAL(10,2),((*8.00)/1024)) 'Size(MB)',
state_desc,
CASE is_percent_growth
WHEN 1 THEN CONVERT(VARCHAR(50),growth) + '%'
ELSE CONVERT(VARCHAR(50),CONVERT(DECIMAL(10,2),((growth * 8.00)/1024))) + ' MB'
END 'Set to Grow by',
CASE is_percent_growth
WHEN 1 THEN 'Percent Growth'
ELSE 'MB Growth'
END 'Growth Type'
FROM
sys.master_files
)
SELECT
*
FROM
dbinfo
ORDER BY
[Database ID],
[File ID]
And what i'm looking to do is use the data flow task inside the foreach loop container to gather the information from all 6 servers and then store the results in a table on my own local database. The problem is that the Source OLE DB connection errors when i tell it to use the Dynamic Connection Manager that i've created. What's frustrating is that really this should all just work because in my mind it's doing exactly the same thing (connection wise) as the execute SQL Tasks (it's using the same dynamic connection!!).
The error i'm getting is attached. Is anyone able to shed any light on why this might be happening?
Thanks,
Simon
November 1, 2011 at 6:47 am
Without going too much into your problem, if you're already able to use your dynamic connection manager settings to work with the Execute SQL task, you could just use another Execute SQL task to execute the statement you want, and have the output be a full result set.
Then, you can loop through that result set using a FOREACH loop, and store the results accordingly.
November 1, 2011 at 6:54 am
In the properties of any connection in SSIS, you can use an Expression for the Connection String, and can control the connection dynamically that way.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 1, 2011 at 7:35 am
GSquared (11/1/2011)
In the properties of any connection in SSIS, you can use an Expression for the Connection String, and can control the connection dynamically that way.
That's what i've done so far and it works with the execute SQL Task.
The problem arises when i use the same connection, that worked with Execute SQL task, for the connection to the OLE DB source. As part of the OLE DB source i'm specifying that it uses the dynamic connection manager, and then specify the data access mode as SQL Command (i've also tried SQL Command from Variable) but neither seem to work. I can't seem to click OK on the OLE DB source editor dialog box because i get the error as on the original post.
November 1, 2011 at 7:37 am
kramaswamy (11/1/2011)
Without going too much into your problem, if you're already able to use your dynamic connection manager settings to work with the Execute SQL task, you could just use another Execute SQL task to execute the statement you want, and have the output be a full result set.Then, you can loop through that result set using a FOREACH loop, and store the results accordingly.
I considered doing this but felt that it shouldn't be necessary seen as i'm really trying to do what the data flow task is meant for. I might well plump for this solution if we can't get this solved.
November 1, 2011 at 7:48 am
For the error, try using the Advanced Editor instead of the basic one. In there, specify the connection string as a static one, and then the SQL Command from Variable. Then, after you close it successfully, you should be able to change the connection string through the Expressions in the Properties window.
November 1, 2011 at 8:09 am
Thanks! That helped me to get it working.
I set the dynamic connection to have a static connection string first and removed the expression. Once i'd managed to OK that dialog i then used the properties pane to remove the static connection string, and re-add the expression.
This did cause an error at runtime because of a validation error. I turned the validation off and hey presto! It works. How annoying...
I wonder if this sort of error is fixed in SSIS 2008/R2/2012!!
Thanks for all your help guys! 🙂
November 1, 2011 at 8:23 am
The problem with the validations is that the expressions are only evaluated at run-time. So, as far as the engine is concerned, when it is trying to validate the package, its failing, because the connection manager is invalid.
There isn't really any way of being able to fix that, short of just circumventing the validation
November 1, 2011 at 9:05 am
That's precisely what Delayed Validation is for.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 1, 2011 at 9:09 am
Yeah - though not available for just the data source, need to set it at the Data Flow Task level
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply