Reading Connection Strings Programmattically In A VB Script
Jan 31, 2008
I'm working on an SSIS package that ends with a VBscript that takes the source flat file, and renames it into a backup version of itself. I can get the script working if the path is hard coded, but I want to be able to tie the source and destination directories to my existing connection manager for the source file used in the data flow. How can I get my VB script to read a specific connection string from a connection manager? The senior programmer working with me on the project would prefer to not use package level variables if it can be avoided.
I am trying to get the connection string values in a raw SSRS XML file. The XML for the SSRS rdl is located in a SSRS rdl file named SampleSSRSXML.xml that you can download from the URL below.The XML is read into a SQL XML Variable named @ReportCode. I have included my many attempts at writing some SQL XML to return all of the connection strings in the SSRS XML file, but everything I have tried returns a NULL connection string.write the correct XML to get all of the XML values for Connection String in the SSRS XML file I provided?
I have tried many different ways to get the connection string, but I am always getting no value in every case I show below:Have I made a mistake in my Cross Apply Structure matching the raw SSRS XML file structure?I am unclear what ./Text() means and when to use it?I have my sample SSRS XML file loaded at the URL Below:
;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' AS RS) SELECT CP.cp.value('ConnectionString[1]', 'nvarchar(150)') AS ConnectionString FROM @ReportCode t CROSS APPLY t.CurrentReportXML.nodes('/Report/DataSources/DataSource/ConnectionProperties') AS CP(cp)
All -- Please help. I have some questions about connection strings.
BACKGROUND...
Note that I am using the "SQL Native Client OLE DB Provider", SQL Sever 2005 Express, ASP.NET, C#.NET, and VS.NET 2005. Note that I do not want to use the "Attach a database file" type of connection string. Note that I am using the site http://www.ConnectionStrings.com as a reference. Note that this "Standard security" connection string...<add name="LocalSqlServer" connectionString="Server=MYTESTOFFICESERVERS;Database=MyDatabase;Uid=MyDatabaseUser;Pwd=MyDatabasePassword;" />...does not work and causes the following run-time error...A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) Note that this "Trusted connection" connection string...<add name="LocalSqlServer" connectionString="Server=MYTESTOFFICESERVERS;Database=MyDatabase;Trusted_Connection=yes;" />...works but is less-than ideal because it uses a Trusted Connection rather than a Username/Password combination.
QUESTIONS... (A) What exactly does the error in Item 4 mean? (B) Do 4 and 5 imply that one must use a Trusted_Connection for all SQL Server 2005 Express connection strings when the database does not reside in the web application's App_Data folder or use a "Attach a database file" type of connection string? (C) Are there any alternatives? (D) What do you suggest?
I am trying to develop a web site. I have a local ms sql database on my machine. I am trying to connect to a ms Sql database on a goDaddy server from the application. I am trying to understand the connection string and its total properties. here is what I think should be in my web.config file < add name="Personal" connectionString="Server=whsql-v12.prod.mesa1.secureserver.net; Database=DB_XX10; User ID=myID; Password=myypassword; Trusted_Connection=False" providerName="System.Data.SqlClient"
/> <remove name="LocalSqlServer"/> can someone please tell me where I am going wrong, Thanks for your help.....
I developed an application and deployed it onto a hosted provider. I use SQL 2005 Express for development SQL 2000 for production. Both systems work. I backed up my production database and wish to use it for my development testing. I restored it as a SQL 2000 database on my development machine and changed my connection strings to point to it. I added it as a new Data Connection in Database Explorer and can see all of the data through it, but I see that all the tables now have the owner name in parenthesis as part of the table name. My application now fails trying to find any of the tables. It works if I include the owner name as part of the SELECT statement. So much for data separation. I don't know where to start. Is this an issue between SQL 2005 Express and SQL 2000, or is it an issue between SQL and ASP.NET, or is it a hosting provider issue, or what?????
Hey guys, don't know if this is in the right place but here goes. I'd like some advice on optimizing the way I write my code. Here's the scenario. I have a select string for 3 different tables. One for the main gridview, and two others that are binded to dropdownlists. So I created a Sqldatasource1,2,3 and had to create the connection strings for each one. ex. SqlDataSource1.ConnectionString = "blah", SqlDataSource2.ConnectionString = "blah", SqlDataSource3.ConnectionString = "blah"the thing is, the connection string itself is the same. Is there a way to consolidate that? So that when I use a select/update/insert command for each sqldatasource i dont have to have another connection string? Also, having multiple connectionstrings, does that slow down the performance?
Hi, Im struggling to work out the connection string to my ms sql database. My website is being hosted with freezone.co.uk and I have a ms sql database called "aspnetdb". I usually log into the database with a username but no password. I don't no what other information is needed to create my connection string? Thanks, Mark Lawton
The strange thing is that when I use the Connection Manager dialog screen and click on "Test Connection", I get the response that the connection succeeded. It is only when I try to run the task that I get an error.
Here is the error I am getting:
[OLE DB Source [1]] Error: The AcquireConnection method call to the connection manager "MyDataBase" failed with error code 0xC0202009.
[DTS.Pipeline] Error: component "MyDataBase" (1) failed validation and returned error code 0xC020801C.
I have eliminated the obvious errors (incorrect Userid or password). Also, I am unable to use Integrated Security as this package needs to be distributed to various systems that may or may not be on our company's primary domain.
It seems I am missing something really basic here, but am not seeing it.
This is probably a very basic question and I apologise for asking. Can an app developed with VWD using SQLexpress be used with server2000? if so how do I modify the connection string.
Hi, I am looking to allow a user to select which database they need to connect to, and then for them to be able to use that connection string until they choose a different database. I have a separate database specifically to hold the list of databases with the respective connection strings, and various forms that are currently looking at the web.config for the connection string. Unfortunately, I can't just put all the connection strings into the web.config as the number of databases available to the user will increase on a weekly basis which will be handled by a database administrator, so I want a user to select from a list of databases (for which they have permissions), and for that connection string to remain for that user. Potentially there will be 20 or more users each connected to a different database (all SQL Express). If anyone can provide any help on this I will be truly thankful as I've hunted on the internet for hours and have been unable to find anything that has helped. Thanks in advance. Paul
Dynamic connection strings I have found lots of people asking for something similar to this but cannot find any solutions - can you help? I am developing an application (ASP.Net2) in which users are associated with 'Clients' and every Client has their own SQL Server database. Users are authenticated using standard ASP.Net Authentication via a separate database (common for all users). This includes a table linking them to a Client and each Client record includes the connection string to their database. Currently a default connection string is held in the web.config file. My problem is that I don't know how to dynamically change the connection string after a user has logged on. I have a large number of databound controls, many of which are declared and some are coded in the VB.Net code behind. I know I can use code behind to change the connection string for a SQLDatatSource: I thought I had it cracked with the following: Public objClientConnection As New ConnectionStringSettings Public Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs)If Session("ClientDBConnectionString") <> "" ThenobjClientConnection.ConnectionString = Session("ClientDBConnectionString")objClientConnection.Name = "ClientDBConnection"objClientConnection.ProviderName = "System.Data.SqlClient"ElseobjClientConnection = ConfigurationManager.ConnectionStrings("WebTool1ConnectionString")End IfEnd Sub The session variable Session("ClientDBConnectionString") holds the client specific connection string and is populated when the user logs on. If this is not populated then the connection string defaults to one collected from web.config. This should ensure there is always a design time connection string available. The trouble is, when I try and declare the SQL data souce like this: <asp:SqlDataSource ID="dsTest" runat="server" ConnectionString="<%# objClientConnection.ConnectionString %>" SelectCommand="usp_SEL_DocumentTypes" SelectCommandType="StoredProcedure" ></asp:SqlDataSource> I get an error: The ConnectionString property has not been initialized. I have been going around in circles on this for days and would really appreciate some help. Am I on the right lines? What needs to change to make it work? Is there a better way? Many thanks, Cliff
What am I doing wrong? This works: SQLConn.ConnectionString = "Data Source=McD_DB;Initial Catalog=McDFeelGood;User ID=uuu;Password=ppp" Now I want to put it in my web.config, but these don't work ("ConnectionString property has not been initialized"): SQLConn.ConnectionString = ConfigurationSettings.AppSettings("McDFeelGoodConnectionString") (VS IDE says this method is obsolete)SQLConn.ConnectionString = System.Configuration.ConfigurationManager.AppSettings("McDFeelGoodConnectionString") <connectionStrings> <add name="McDFeelGoodConnectionString" connectionString="Data Source=McD_DB;Initial Catalog=McDFeelGood;Persist Security Info=True;User ID=uuu;Password=ppp" providerName="System.Data.SqlClient" /></connectionStrings>
Hello, I'm trying to setup a typed dataset with a table adapter in .NET 2.0, and the problem I am having is that I cannot get the table adapter to use existing connection strings setup in the web.config file. How can I get it to do so? It doesn't see the connection strings, and so it wants me to create a new connection, which I don't want to do.
I should start by saying that I'm new to SQL Server and ASP.NET. My question is about connection strings. With so many possibilities of these strings, how will I ever know what is best to use or try when one does not work. Is there a rule of thumb or an article or even a book that someone can recommend? Something that will demystify this part of working with the SQL Server and ASP.NET?
My question is, are the user id and password optional when creating a database connection string? I've created an asp page without including them, but in my aspx page (which is basically supposed to do the exact same thing as the asp page), I'm receiving the following error:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
Source Error:
Line 48: DisplayConnection = New SqlConnection("Server=infomart;Database=psg") Line 49: DisplayCommand = New SqlCommand("SELECT RollOutID,RequestedDate,Background,Product,StagingPushDate,ProdPushDate FROM rolloutrequests ORDER BY RollOutID DESC", DisplayConnection) Line 50: DisplayConnection.Open() Line 51: DataGrid2.DataSource = DisplayCommand.ExecuteReader() Line 52: DataGrid2.DataBind()
My app is in a shared hosting environment. They use SQL Server authentication and I'm not wild about storing user ids and passwords in web.config. What's the best practice for storing this connection string information?
I am trying to construct a connection string via iSeries OleDb provider. The default list of libraries on the AS400 for my user is fine for production. But in test scenarios the code/data libraries are rolled out through a sequence of test libraries. How do I specify the additional libraries for testing.
I connect fine and can execute sql. the statements like insert into TestLibrary.Table work fine, but the associated trigger code isn't called.
Would it be better to use the .Net Connection set of classes instead. I switched from ODBC to OleDb because of assumed thread safety issues.
Hello everyone! I am having some more problems while trying to get my site online. I built the site offline using Visual Web Developer Express Edition, and used "SQL Server Express" that comes with ASP.Net. Therefore, I have the ASPNETDB.mdf and mydatabase.mdf in the App_Data folder. Now, on my local machine everything runs fine. When I tried to put the site online however, I get the following error:An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) This is the connection string in my web.config file: <connectionStrings> <add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|mydatabase.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/> </connectionStrings> Now, my web-host does have SQL Server 2005. Does my ASP.Net application use that though, since I have it running just through SQL EXPRESS? Also, does anyone know where I could change my "SQL Server 2005" settings? My web-host uses Plesk 8.2.0, and the database managing program is "ASP.Net Enterprise Manager", but I can't find any options anywhere to change the Server settings to allow remote connections. Any help would be greatly appreciated! Thanks in advance, Markuu
I'm coming from ASP and I used to have a global connection string accessible to all of my ASP pages that I'd use for all of my data access. This was really convenient because I could easily switch to a backup or local data source for testing/debug by changing the connection string in one place.
Now, I'm using ASP.NET with Web Matrix and I love the drag and drop functionality but its dropping my connection string all over the place. How can I do this and keep my connection string in one spot so I can have the same convenience of switching data sources.
From my Web.Config<connectionStrings> <add name="CustomerConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename="D:-- Work Documents --WorkezWebApp_DataASPNETDB.MDF";Integrated Security=True;Connect Timeout=30;User Instance=True" providerName="System.Data.SqlClient" /> </connectionStrings>It's obvious to me that the next in orange would cause my db application not to work when i roll it out onto the net.Am i correct to say this? I recalled i needed something like "Server.mappath("path-name")"anyone know what i need to do?
Due to some problems, we have to reformat Our Windows NT4.0 Server. The SQL Server 2000 has several ODBC Configured as "User DSN" and "System DSN" .
Question: Is there any way that we could copy these User DSN and System DSN Config Files so that after Rebuilding Server AND Database, we could Re-aaply/ Re-create these ODBC DSN specifically "System DSN" as they are More Number.
Due to some problems, we have to reformat Our Windows NT4.0 Server. The SQL Server 2000 has several ODBC Configured as "User DSN" and "System DSN" .
Question: Is there any way that we could copy these User DSN and System DSN Config Files so that after Rebuilding Server AND Database, we could Re-aaply/ Re-create these ODBC DSN specifically "System DSN" as they are More Number.
In this posting they talk about setting up a configuration file to setup the connection string, but I am not sure how to apply that connection string to my connection manager. When I go into my configuration file, I can see the connection string, and when I go into the advanced properties of the connection manager, under the "Named ConnectionStringFileName" property, I set the property value to the name of my configuration file, but when I test the connection I get an error that the connection string property has not been initialized.
Can someone please assist me with identifying what I am doing incorrectly?
I've been planning on putting all my connection strings for my SSIS packages into package configurations. So far, I've been using XML config files, and these strip out the passwords in connection strings which then have to be retyped every time in notepad.
Has anyone got some better guidance regarding this? Could I use another type of package config to avoid this problem?
My contraints are such that I cannot use integrated security for the packages, so the password MUST be in the connection string. However, I can place the config files in a secure location where only the package and admins have access to them.
I have a SSIS package which needs to connect to 5 different servers to import data. I want to schedule one task to run this one .dtsx but run once for each server connection( 5 different servers).
Is it possible? how can i accomplish it? Is Package configuration possible? How to go about it if so?
I created a couple of Data Sources, setting them up as I have done for OLE DB connections and then deleted that connections, and created them (recreating the connections using create connection from Data Source).
But when I open them again it gives me a dialogue box (Synchronize connection string) stating following message:
This package contains at least one connection which is based on a data source. The connection string for connections and data sources listed below are currently not identical. Connection strings of connections will be updated to reflect those on the data source.
I am also using connection string variable (through Expression builder) with Package configuration for those connection
Can any tell me the solution of this problem?
I have read, in one of the forum, as a BUG but I m sure it is not as i had this problem previously on my old system but it was gone somehow (I dont know how?)
SO coming to my question. 1)What is it doing. I mean how is the package talking to this config file 2) Irrespective of what I do in the Connection String the package executes fine, which It should not do as far as my understanding goes. Why is it doesn't matter whats in String. where as it matter in later.
Since we made some 'improvements' on our package deployment I feel I am going crazy! This is what changed: We created LOCAL.dtsConfig (UAT...) files. This local file resides say at C:DWDWPackagesDWPackages. We have user environment variable called DW_XMLCONFIG which has value C:DWDWPackagesDWPackagesLocal.dtsConfig
Any package I open gives me following dialog box Synchronize Connection Strings with Connection, Data Source and Old Connection =Data Source=LPET06VPC02SQL2K5Local;Initial Catalog=DW_Dev;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False; New connection =Data Source=sql11;Initial Catalog=DW_UAT;Integrated Security=True
All of my packages have connection called TargetNet. My question; Why it does not pick up value from dtsConfig file instead using connection under Data Sources (TargetNet).
I am working on porting a Windows Forms VB.net VS 2003 app with a SQL 2000 backend - to using VS 2005 with SQL Express 2005.
I cannot seem to figure out how to set my connection string to work in the new environment. I have already recreated my database in SQL Express 2005 and ported the VS project to VS 2005.
I am trying to split a .csv file on a week+location key. As there are 500+ locations and 52 weeks in a year this is not a manual task.
I have a Script component written but need to dynamically open/close the connection manager changing the connection string in between, to the Week+location key as the filename.
It has to be do-able because the ForEachLoop does it. But how do I?
Possible or not? --> I maybe lazy - but I want to achieve just specifiying 1 variable in SSIS package ("environment") - and all the connectionStrings should "poof" magically be adjusted to correct locations
In DTS I created a SetDTSenvironmentVariables function for all my packages - so how wouldIi achieve this in SSIS?
Function SetDTSenvironmentVariables( environment ) Folder = "MyDtsPackageFolder" Select Case environment case "DEV" DTSGlobalVariables("WorkingDirectory").value = "C:Packages" & Folder case "STAGING" DTSGlobalVariables("WorkingDirectory").value = "D:Sql_working_directoryMy_productionSTAGING" & Folder case "LIVE" DTSGlobalVariables("WorkingDirectory").value = "D:Sql_working_directoryMy_production" & Folder End Select
' ' Set Connection Properties ' dim oPackage, oConn set oPackage = DTSGlobalVariables.parent oPackage.LogFileName = DTSGlobalVariables("WorkingDirectory").value & "LogsErrors.txt" For Each oConn In oPackage.connections Select Case oConn.Name case "My_DB" Select Case environment case "DEV" oConn.datasource = "SERVER01" oConn.Catalog = "My_Production" case "STAGING" oConn.datasource = "SERVER06" oConn.Catalog = "My_Staging" case "LIVE" oConn.datasource = "SERVER06" oConn.Catalog = "My_Production" End Select case "Schools.xls" oConn.datasource = DTSGlobalVariables("WorkingDirectory").value & "" & "School_Codes.xls" case else oConn.datasource = DTSGlobalVariables("WorkingDirectory").value & "" & oConn.Name End Select Next
set oPackage = nothing set oConn = nothing End Function
Everything is set up the way it should be, as far as we can tell. However, when we drop in a SQL data source and try to configure, it, we get a blank box for choosing the connection string, and it won't let us make a new one.
Has anyone encountered this before? If so, how can we fix it?
in asp usually i have code behind files those .aspx.vb file.can i make use of connection strings in there? i am not really familiar with connection strings. actually shouldn't code behind files "simulate" code being in the .aspx file themselves? i seem to not able to share variables between them?
