Dynamic Connection Manager

  • 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.

  • This series of articles details how to remotely collect server info:

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

  • Thanks Old Hand, Im gonna dig in to this and try.

    Shall keep posting if any doubts

  • 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

  • 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".

  • I have solved this by using servername instead of connection string in expression field of my oledb conn mng

  • 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.

  • Hi, were you able to reslove the MultiServer issues? If so can you elobrate how you did it. I am having the similar issue.

  • 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.".

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply