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.
This has been happening to me since I started using Configuration files. I use one XML config file that stores Machine Specific settings and one connection string to my Metadata database. The problem is the database connection.
When I set the conection in the Configuation file, save, close and reopen the package, a dialog box opens with the title "Synchronize Connection Strings".
To reproduce the problem:
1. Ceate New SSIS Project 2. New Data Source Localhost.Adventureworks 3. In Package.dtsx create an New Connection from Data Source... 4. Choose AdventureWorks 5. Save Package 6. Close Package 7. Open Package
There is no problem at this point.
8. Enable Package Configurations 9. Create a new XML Configuration File 10. Give it a name and a location 11. Click Next 12. Under Connection Managers Click Adventure Work 13. Click Next 14. Give it a Name 15. Click Finish
16. Save 17. Close 18. Open
Uh-Oh!
"Synchronize Connection Strings" Dialog Box Hare is what it tells me.
Connection Adventure Works
Data Source Adventure Works
Old Connection String Data Source=.;User ID=;Initial Catalog=AdventureWorks;Integration Security=True;
Old Connection String
Data Source=.;Initial Catalog=AdventureWorks;Integated Security=True
19. Press OK
20. Save Package 21. Close 22. Open
Uh-Oh, still there.
Has anyone else run into this? If so, how can I get around it?
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>
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?
I'm trying to figure out how to retreive connection strings from the machine.config instead of storing the conn strings in a *.dtsconfig file. The reason that I'm trying to use the machine.config is for the simple reason that I'll be able to manage all of the conn strings in one location on each server. (Instead of mutliple dtsconfig files.) Is it posible to utilize the machine.config in SSIS? If so, could someone explain how, or better yet, have an example?
Currently have a single hard coded file path to the SSRS config file which parses the file and provides the reporting services web service url. My question is how would i run this same query against 100s of servers that may or may not share the same file path as the one hard coded ?
Is there a way to query the registry to find the location of the config file of any server ? which could be on D, E, F, H, etc.
I know I can string together the address followed by "reports" and named instance if needed, but some instances may not have used the default virtual directory name (Reports).
Am I going about this the hard way ? Is there a location where the web service url exists in a table ? I could not locate anything in the Reporting service database. Basically need to inventory all of my reporting services url's.
Currently we have a 1.1 app that sets the connection string as a Application variable, we are wanting to change that and place it in the web config file, when I do this in 2.0 it's a simple process but in 1.1 it's different. It doesnt seem to like the <connection string> tag, does it go in the appsettings tag and if so what should it look like and how is it referenced.
I'm trying to connect to a Sybase DB using an OLE DB connection. Now, it works fine when I run the project without the package config enabled, but when I add the Sybase connection to the xml config file, & enable it the connection fails.
This is the error I see in the progress Tab
[Connection manager "AcidPrd.basketUser"] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "ASE OLE DB Provider" Hresult: 0x80004005 Description: "Login Failed. Check for valid user ID, server name and password.". An OLE DB record is available. Source: "ASE OLE DB Provider" Hresult: 0x80004005 Description: "[Native Error code: 4002] [DataDirect ADO Sybase Provider] Login failed. ".
I've recently uploaded my website, http://www.bigredsongbus.com, to my host. Unfortunately, they don't support the use of SQL Server Express. So, I've purchased an addon through my host - discountasp.net. I've attached my database file to their server, now all that I need to do is to change the connection string in my .config file so that it points to the SQL Server. I don't know how to do this. Any help, please?Jeffrey Way
I am developing one SSIS package to migrate an Oracle Database to SQL Server 2005 DB through ODBC. My problem is with configuration. I have to use configuration file so that in production environment the end-user must be able to change the user credentials to connect both database servers. I am trying xml configuration file. When I try to use xml configuration, my odbc connection is giving error. But if I disable the configuration through "Package Configurations Organizer", it works fine. Using an XML Editor I checked the .dtsconfig file and entered both passwords for the both connections.
I tried logging and below are the few text from log file related to error.
Code Block User:Diagnostic, Nebu, BLRDOMNebu, DestOdbcConnection,{E3535ED4-1D21-41B9-9796-E9F5B47FFD5B}, {6820E259-18FF-4FCA-B820-73EE931D2095},12/17/2007 8:16:39 PM,12/17/2007 8:16:39 PM,0,0x,ExternalRequest_pre: The object is ready to make the following external request: 'IDbConnection.Open'.
User:Diagnostic,Nebu, BLRDOMNebu, DestOdbcConnection,{E3535ED4-1D21-41B9-9796-E9F5B47FFD5B},{6820E259-18FF-4FCA-B820-73EE931D2095},12/17/2007 8:16:41 PM,12/17/2007 8:16:41 PM,0,0x,ExternalRequest_post: 'IDbConnection.Open failed'. The external request has completed.
I am using Protection level = EncryptSensitiveWithPassword for the package.
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)
I'm a newbie to SSIS so this question may cause people to point and laugh. I apologise if that's the case.
I have a configuration database that holds connection details to a remote database. I want to use these connection details to dynamically create a new connection to some remote database.
I usually work in with C# where this would be simple but I can't find how to do this using SSIS.
I have found people using configuration files but as I already have my connection data in a table I don't really want to strip it out. (Also, is it a security risk to have database connection strings stored in a config file?)
I have been able to implement a custom script object to build the connection string and assign it to a user variable within the package but can't find any way to then use that variable to connect to the remote database.
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 experienced a weird error while deploying my SSIS package. After running the manifest file, i noticed that one of the configuration file's path was not updated in the dtsx file. My solution has 8 packages and almost every package has 2 configuration files. Except 1 file every other config file's path is being updated. Has anybody experieced such a problem?
In toder to make my SSIS packages portable, I need to be able to set connection string to sql package configuration. I thought I can do that via XML package, problem is, at execution time SQL server package configuration is queried first, then variables from XML. This way, I have no chance to set connection via XML. Any ideas on how to aproach this ? (I want to have as much configuration on SQL server so I can modify it easily from future GUI application.
hey guys i keep getting this error and I am not sure how to sort it and google just aint working for me tonight. Exception Details: System.InvalidOperationException: The ConnectionString property has not been initialized.
Line 31: SqlDbType.VarChar, 40)) Line 32: objCmd.Parameters("@IssueName").Value = IssueName.Text Line 33: objCmd.Connection.Open() Line 34: ' Test whether the new row can be added and display the Line 35: ' appropriate message box to the user.
[InvalidOperationException: The ConnectionString property has not been initialized.] System.Data.SqlClient.SqlConnection.PermissionDemand() +852131 System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection) +22 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 ASP.newpriority_aspx.submit(Object Source, EventArgs e) in C:Prototypeofficial1htmlewpriority.aspx:33 System.Web.UI.HtmlControls.HtmlInputButton.OnServerClick(EventArgs e) +105 System.Web.UI.HtmlControls.HtmlInputButton.RaisePostBackEvent(String eventArgument) +106 System.Web.UI.HtmlControls.HtmlInputButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Dim strConnection as string = ConfigurationSettings.AppSettings("GeolinkSupportdb") Dim sqlConn = New SqlConnection(strConnection) Sub Page_Load(sender as Object, e as EventArgs)
End Sub
Sub submit(Source As Object, e As EventArgs) 'assign the user entered information Dim objCmd As SqlCommand 'Dim dbIssueName = IssueName.Text 'You must open the connection before populating the DataReader Dim strSQL1 = "Insert into Support_Issue Values @IssueName" 'SqlConn.Open() objCmd = New SQLCommand(strSQL1, SqlConn) objCmd.Parameters.Add(New SqlParameter("@IssueName", _ SqlDbType.VarChar, 40)) objCmd.Parameters("@IssueName").Value = IssueName.Text objCmd.Connection.Open() ' Test whether the new row can be added and display the ' appropriate message box to the user. Try objCmd.ExecuteNonQuery() Message.InnerHtml = "<b>Record Added</b><br>" & strSQL1 Catch ex As SqlException If ex.Number = 2627 Then Message.InnerHtml = "ERROR: A record already exists with " _ & "the same primary key" Else Message.InnerHtml = "ERROR: Could not add record, please " _ & "ensure the fields are correctly filled out" Message.Style("color") = "red" End If End Try objCmd.Connection.Close() End Sub </script> database looks like this IssueID UniqueIdentifer IssueName varchar(50)
Any idea on why I cannot get my connection string to work have I missed something or trying to connect to it wrongly?
I want to store my connection string in a configuration file, however when I create the config file the connection string password is not stored. I can manually edit my config file and add the password to my connection string but I am sure that BIDS has a better solution. What is the best practice for storing connection string passwords in my SSIS Configuration files?
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.
All, I don't know what is the problem, i am not able to retrieve connection string from web.config in my code behind page I used every solution provided by people in differents forums. can anybody help me with this.I used WebConfigurationManager.OpenWebConfiguration("/TimeSheetApplication").ConnectionStrings("TimeSheetConnectionString").ConnectionString; WebConfigurationManager.ConnectionStrings("TConnectionString").ConnectionString;
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
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.
