February 10, 2009 at 3:35 am
Hi
I have made a .dtsConfig for a SSIS-package.
It has the following tag:
I am interested in changing the connectionstring - that is all under ConfiguredValue!
The reason is that the package should be able to run on any machine that as an SQL 2005 database (includind SSIS). However, I cannot find any place where I from program-control can figure out the connectionstring on a given pc that has SQL 2005 installed (SSIS).
How to do that?
BR
Claus
February 10, 2009 at 4:36 am
Connections are not specific to or dependent on client machines. All they need the connection information (i.e. provider, data source, credentials etc.) to access the server.
Here is a few sample connection strings to connect to an instance of SQL
"Provider=SQLNCLI;Server=SomeServer;UID=a;PWD=a;Database=SomeDB"
"Provider=SQLOLEDB.1;Data Source=SomeServer;User ID=a;Password=a;Initial Catalog=SomeDB"
"Provider=SQLOLEDB;Server=SomeServer;User ID=a;Password=a;Database=SomeDB"
And also, you can see the connection string by creating .udl file (its just a text file with .udl extension)...
--Ramesh
February 10, 2009 at 5:21 am
I am not sure I understand what you write.
It is the case that running the SSIS package on a different machine will mean that I need a different connectionstring.
I am just interested in finding how the connectionstring will look on a different pc, I do not want to specify it - just to know what it is. I want to get the text-string that tells me how the connectionstring look at this other machine.
Maybe it is a stupid question!
BR
Claus
February 10, 2009 at 6:41 am
I should probably add, that normally (I guess) you make a .dtsConfig file for each of the servers/machines/pc's that you want to run the SSIS package on.
However, I want to make it somewhat more flexible, so I did not need to know the servers/machines/pc's beforehand as the idea is under program-control to change the (default)-settings in the .dtsConfig file. However to do that I need to know the connection string that a particular server/machine/pc will use in case the SSIS package is to be run.
That is the reason for this question.
We are talking about MS-SQL 2005.
BR
Claus
February 10, 2009 at 7:41 am
cam (2/10/2009)
I should probably add, that normally (I guess) you make a .dtsConfig file for each of the servers/machines/pc's that you want to run the SSIS package on.
Normally, we create a package & a configuration file, then we deploy & install the package to the SQL Server & manually change the connection string.
cam (2/10/2009)
However to do that I need to know the connection string that a particular server/machine/pc will use in case the SSIS package is to be run.
I'm totally CONFUSED::unsure:...You have the package, have the configuration, and deploy it to some (un)known machine. Then you need to know the connection string that this machine will use, without changing anything. Am I correct? Well, if yes, then the connection string will look exactly the same as the deployed configuration's con. string.
Or You don't deploy it and still want to know what would look like if it was executed???
I would like to help you, but I cannot, unless you provide some more information.
--Ramesh
February 10, 2009 at 7:50 am
Hi
You are probably only confused due to my stupidity!
On my machine I have following:
The part in ConfiguredValue I need to change, probably only: Data Source=FFL_LAPTOP\FFL_LAPTOP.
On another computer/server/etc this instance of the SQL 2005 server is different - a different name. It is this name I am interested in finding, that is finding under program-control - I can easily go to for example the definition of the ODBC connection in look in the GUI to see the server-name (I know there will be a ODBC connection). I am trying to find the registry/ini-file or wherever I can find this information on any given windows machine.
Hope it helps what I I am searching for.
BR
Claus
February 10, 2009 at 8:07 am
OK, got it this time, check this registry entry "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL"
EDIT:
And also, there are many free tools available that does this.
--Ramesh
February 10, 2009 at 8:15 am
hI
Thank you, could you please guide me to some software that could alternatively be used?
BR
Claus
February 10, 2009 at 10:12 am
cam (2/10/2009)
hIThank you, could you please guide me to some software that could alternatively be used?
As of now, I don't know of such software.., but my best pal "Google" does know it:D
--Ramesh
February 10, 2009 at 10:33 am
The easiest for me is to right click on the instance choose properties and get the name.
This will only work if sql browser service is running otherwise you will have to use the IP addresses to connect.
Additionally, in SSIS 2005 the connection string property is read only to shange the database connection you have to expose "Package.Connections[DBConnection].Properties[ServerName]" property to your config to set it.
I have not tested this with 2008
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply