January 6, 2010 at 2:56 pm
I have the following to be done.Read list of servers form table A, get corresponding list of database from table B for servers in table A and execute some queries to get information of each db and insert the results to my local server/ws.
What I have done so far is,
1) Create execute sql task with result set as return, 'select srvname from table A' and store results in obj srvlist
2) Create For Each Loop and connect to 1).ADO enumerator.ADO obj source var= srvlist.parameter mapping = servername [var of type string]
3) Within 2) create execute SQL Task, with following
'select dbname from B where srvname= '?' and report=1'
parameter mapping = servername ,result set = obj server_name
4)Create a for each loop and connect 3) to it. ADO enumerator.ADO obj source var = server_name, parameter mapping = dbname [var of type string]
To test valus being passed.
5) Include a script task to pop up a Msg Box, that prints results of dbname within For Each Loop in 4)
My problem is, i can see value changing in the watch window for step 2).But as soon as it hits the 4) step it does not execute the script task within and ends without errors.
There are no errors.Do advize me, as I have been trying to reconfigure the for loop many many times.
package.bmp - current package
package1.bmp - during execution
January 7, 2010 at 7:16 am
Hi,
I think I understand what you are trying to do, but I think you might be a bit of target.
I can't see where you are dynamically creating the connection string for the database you want to connect to, so I think that's what you are missing. Take a look at my article - it's focused towards WMI queries, but I think the same principle apply. You would simply need to dynamically create a database connection instead of the wmi_conn that I define in the document (step 8).
here's my article: http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/67428/
let me know how you get on.
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
January 7, 2010 at 8:36 am
Hi dave,
Excellent documentation, Im trying to follow your exact package to get a better understanding on how I can implement it in my situation.
I ran into one problem though.While excuting I receive error mssg
TITLE: Microsoft Visual Studio
------------------------------
Failed to connect to the specified server with the following error: "Invalid parameter ". The server name may be invalid.
------------------------------
BUTTONS:
OK
------------------------------
In the first Data Flow Task,Data Source Im passing the below instead
select RTRIM(LTRIM(ip)) as ip from dbo.srv
Does this work with server ipaddress as well ?
In the wmi conn the connection string expression refers too
"ServerName=\\\\" + @[User::ServerConnString] +
";Namespace=\\root\\cimv2;UseNtAuth=False;UserName=user;PassWord=12312345"
January 7, 2010 at 9:22 am
i haven't tried it with IP addresses to be honest - but I would expect it to
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
January 7, 2010 at 9:25 am
Any idea why Im geeting the error mssg?
January 7, 2010 at 9:26 am
PS don't forget you'll need to add in your instance name if your connecting to an instance
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
January 7, 2010 at 9:27 am
There are no instances, only default instances
January 7, 2010 at 10:03 am
if your following my notes, then you'll need to make sure that the server list table exists and that it is returning the details in turn in the watch window
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply