SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamic Connection Manager


Dynamic Connection Manager

Author
Message
LOOKUP_BI-756009
LOOKUP_BI-756009
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1737 Visits: 1485
Hi,

I need to come up with a way to gather information from sp_spaceused of all databases sitting on diff servers and store the results in a db in server [A]. The SSIS package has to execute on A and store results of execution on A too. No executon should take place on any other server besides A, all other servers are production servers.

This is what I have done so far.
1) Created a Execute SQL Task that queries a table srvname on server A.This would return list server ip add, username, password for servers to be checked. Prob : Some servers use windows and others SQL authentication. How do I strore this ?
2) Results of ip add, username and password is saved as 3 separate variables of object data type.

Im looking for some advice on how I can accomplish this task. How can I dynamicly build the oledb connection manager using the 3 variables[ ip add, username, password] taking into consideration if its windows/ sql authentication and pasing through a for loop that would loop thru each connection manager and gather sp_spaceused results and dump to a table on Server A.
andrewkane17
andrewkane17
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1831 Visits: 3266
This series of articles details how to remotely collect server info:

http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/61621/



LOOKUP_BI-756009
LOOKUP_BI-756009
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1737 Visits: 1485
Thanks Old Hand, Im gonna dig in to this and try.

Shall keep posting if any doubts
LOOKUP_BI-756009
LOOKUP_BI-756009
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1737 Visits: 1485
Im trying something similar to my previous problem.Get list of database name from each server based on list of server name from a table and dump results to flat file.For testing purpose I only have 1 entry in serverlist table that uses SQL authentication to connect.

Here is my problem.Everything run's well no errors BUT no results are captured and upon opening MultiServer connection manager, no entry in password field even when save password is ticked.

1) Create table serverlist [name,ipadd,active,username,password,connectionstring]
2) Create a SQL Task 'select connectionstring from serverlist where active=1'
3) Store results in var ConString of type object
4) Create for each loop,Enumerator= Foreach ADO, ADO source variable= ConString, variable mapping= SrvCon of type string Index=0
5) Create data flow task within For Each Loop.
6) In data flow task, Define Olde db source, where ole db connection mng = MultiServer, access mode = SQL Command ,SQL Command Text = use master
select name from sys.databases
7) From ole db source to flat file connection manager as destination

Configuration for MultiServer oledb connection manager
1) Expression connection string = var SrvCon

Connection string in table serverlist is saved as such
Data Source=111.111.111.111;User ID=user;Password=pass;Initial Catalog=master

LOOKUP_BI-756009
LOOKUP_BI-756009
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1737 Visits: 1485
An update, when I pick MultiServer in the dropdown of servers in Oledb Source within the data flow task, which is within the for each loop.

I get the following msg

Error at Data Flow Task [OLE DB Source [1]]: The AcquireConnection method call to the connection manager "MultiServer" failed with error code 0xC0202009.

Error at DbCapacity [Connection manager "MultiServer"]: An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'user'.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Invalid connection string attribute".

LOOKUP_BI-756009
LOOKUP_BI-756009
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1737 Visits: 1485
I have solved this by using servername instead of connection string in expression field of my oledb conn mng
krn045
krn045
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 68
Hi All,

I am trying to get the details of new databases created on a weekly basis. My Multiserver connection manager is not working, throwing the below error.

Test Connection failed because of an internal error in intializing provider. Login timeout expired

An error has occured while establishing a connection to the server. when connecting to sql server 2005,
this failure may be becuase by the fact that under default settings sql server does not allow remoted connections.

Named pipes provider: Could not open a connection to sql server [53]

SQL Server Version: 2005 Developer edition
Services enabled and running : SQL Server, SQL Server Browser, SQL Full text search
Network settings: TCP\IP, Named Pipes, Shared memory

--------------
Followed the below steps.

1. Created a sql server lists table with sql full names.
like row1 KRN045
row2 KRN045\TEST
2. Populating the ADO variables using execute sql task with full result set. Result variable is SQL_RS.
Scope: Package
Data Type: Object
Value: System.Object

Connection manger: Database: Monitor, Server: KRN045, SQL- Select SQLFullName from tblvwSQLServer

3.Right click on the empty panel for the Control Flow Tab. Select Variables. Then select the Add Variables icon and add the following two variables.

SRV_Conn

Scope: Package
Data Type: String
Value: KRN045

4.Right Click in Connection Managers Panel

* Select New OLE DB Connection
* Click New
* Enter Server name that has the KRN045
* Click OK
* Click OK
* Right Click on the newly added connection
* Select Rename
* Enter MultiServer

Now we need to customize this connection

* Right Click on MultiServer
* Select Properties
* Change the Following Properties
*
o Expressions click ... box
o
+ Click Property
+ Click Drop Down Arrow
+ Select ServerName
+ In the expression box type @[User::SRV_Conn]
+ Click OK
o Initial Catalog change to Master

Getting the above error when i try test connection.. Please let me know where i am going wrong.
sanjaykp
sanjaykp
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 179
Hi, were you able to reslove the MultiServer issues? If so can you elobrate how you did it. I am having the similar issue.
srikantht.dev
srikantht.dev
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 194
I get the below error at Step7.

Error at Central Pacakge Database Properties [Connection manager "MultiServer"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search