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.
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>
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?
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;
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.
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.
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
Every time I move my project from my computer to the testing server I have to change the connection string references in the aspx side and in my code behind. For the code behind I declared the SqlConnection string at the top of the code-behind page for both connection strings and comment out the one not in use. Obviously I then comment out the string not in use in the WebConfig as well. Being superlatively lazy I always look for the easiest and quickest way to do most anything - connection strings included. Having to comment out strings twice brought rise to the question of whether I can refer to the connection string in the web.config file from the code-behind page. I'm sure it can be done, and I did a good amount of hunting around, but I couldn't find any examples of code that would do that. Currently, as I said above, I have two connection strings declared at the top of my code-behind. Here's an example of one:Private sqlConn As New SqlConnection("Data Source=DATABASESERVER;Initial Catalog=DATABASE;User ID=USER;Password=PASSWORD") Then, I just use sqlConn as usual in my binding without having to "dim" it in every sub:sdaPersonnel = New SqlDataAdapter(strSqlPersonnel, sqlConn) Then there's the SqlConnections set up by the wizard on the aspx side:<asp:SqlDataSource ID="sqlDataSourcePayrollCompany" Runat="Server" ConnectionString="<%$ ConnectionStrings:DATABASECONNECTIONSTRING %>" ...> And for the connection in the web.config:<add name="DATABASECONNECTIONSTRING" connectionString="Data Source=DATABASESERVER;Initial Catalog=DATABASE;User ID=USER;Password=PASSWORD" providerName="System.Data.SqlClient" /> So, what would be the code in the code-behind page to refer to the connection string in the web.config file? Thanks!
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 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.
Ok, here's the deal. This has been killing me for months now.
Here's the setup - SQL 2000 database on a Shared Infrastructure (cluster). I have a database on a named instance. My port number is xxxxx. I have dbo (through Windows Auth) permissions on my database. When I connect at the office, I get through fine, no question. When I connect at home, I get error 26 in my asp.net 2 app (using System.Data.SqlClient), cannot find SQL Server database. I can connect through the SQL 2005 Management Studio using the same servernameinstance name I use at the office. I can connect through the SQL 2000 Query Analyzer using servernameinstancename;(semicolon)portnumber. I've tried all sorts of combinations in my web.config - servernameinstancename,(comma) portnumber servernameinstancename;(semicolon - causes web.config to blowup) portnumber ipaddressinstancename, (comma) portnumber
and i still get nothing. usually it's 'error 26' - but if i use this:
Data Source=ipaddressinstancename,portnumber;Network Library=DBMSSOCN;Initial Catalog=xxx;Trusted_Connection=Yes;
I get 'Login failed for user domainuser'
I've tried making an alias. I've tried using ODBC. I've even tried a freakin' DSN. No dice.
Nothing seems to work - I'm at the end of my rope. Can someone please help?
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?
I used 3 seperate web.config files - one for Development seerver, one for Certification server, and one for Production server. The only difference between these three is the within the <connectionStrings> tags. For Example: <connectionStrings><add name="strConn" connectionString="server=xxx;uid=xxxx;pwd=xxxxx;database=yyy;"/></connectionStrings> Within Development the connectionString points to the Development SQL server, Cert to cert SQL, and Prod to Prod SQL. I do not have permissions to copy new Web.Config files into Cert or Prod, so when I have a change within my web.config I have to have another group manually add these files. Is there a way to identify the domain I am curernt within and use a specific set of connections string within the web.config based off this information. If so, can anyone post an example of how to do this...
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.
I have several SSIS packages, some of which retrieve data from excel files - they work fine from th VS designer until I try to store configurations in a SQL database - then I get the following error:
SSIS package "ITAMdiv2.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Error: 0xC0202009 at ITAMdiv2, Connection manager "ExcelDIV2": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Error: 0xC020801C at Data Flow Task, Excel Source [84]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ExcelDIV2" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at Data Flow Task, DTS.Pipeline: component "Excel Source" (84) failed validation and returned error code 0xC020801C.
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?
Just downloaded and installed the VS 2008 Express and created/tested some websites. I have done several aspx websites using VS 2005 during the last 2 years. Still I don't quite understand the details of coding the database connection and DataBing. For example, what does 'providerName',and Integrated Security really mean?Why in the Web.config file these are providerName="System.Data.SqlClient" and Integrated Security=True? Why in Default.aspxthere are both ntegrated Security=True;and USER ID=WEB; Password=webwebweb1". I know these may deal with the authentication mode of my SQL Serverdatabase.For the VS 2008 Express and SQL Server 2005 Express, I installed in my XP home PC, I used the file system websites, If I upgrade to XP Pro and install IIS web server, and use SQL Server 2005, what might be different? Jeffrey Web.config <connectionStrings> <add name="NorthwindConnectionString" connectionString="Data Source=D5MRY6G1SQLExpress;Initial Catalog=Northwind;Integrated Security=True" providerName="System.Data.SqlClient" /> </connectionStrings>Default.aspx <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=D5MRY6G1SQLExpress;Initial Catalog=Northwind;Integrated Security=True; USER ID=WEB; Password=webwebweb1" ProviderName="<%$ ConnectionStrings:NorthwindConnectionString.ProviderName %>"MasterDetail.aspx <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
I have deployed to production a number of nested packages (parent packages that call child packages) to the SQL msdb via the Save As option rather than building a deployment utility. These packages reference configuration files in a static location off of the c: drive on the production server. In the development environment, when connection changes are made and I run the Reload with Upgrade option the connection manager takes on the new server and user id settings. However, out on the production side I get the following error from the SQL job log:
Cannot load the XML configuration file. The XML configuration file may be malformed or not valid.
As a result the SQL job uses the default connection information which references the development database rather than the production database. I did research the error but found no good solutions. Is there a way to ensure the configuration files are formed correctly and that the packages are correctly referencing the configuration files? We are trying to run the ETL updates via a SQL job.
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.
I'd like to know what's considered a best practice in the following scenario...
We're in the process of converting DTS to SSIS, our current SQL2K setup has one DTS package per database, each database and package are mirror images of each other, with the exception of connection properties and the files path's they pull from. (Occasionally there may be a one off Execute SQL Task that differs from package to package...but this will be handled in a different manner outside of the package from this point on)
From a administration perspective this has been somewhat cumbersome, as every time there is a schema change we need to update the transformations in each individual package...after updating 120 packages my index finger has a bad case of carpel tunnel!
My thought with SSIS is that I can create one package, with many configuration files (one for each database).
Now my question is, 1) is this a good idea? 2) Anytime we create a new database (New Customer = New Database) and need to create a new xml config file, do I have to create the config in BIS/SSIS package then redeploy the package?...or Can I simply add a new config file to the appropriate directory and reference this config in the scheduled job?
I added a connection (ADO.NET) object by name testCon in the connection manager - I wanted to programmatically supply the connection string. So I used the "Expressions" property of the connection object and set the connectionstring to one DTS variable. The idea is to supply the connection string value to the variable - so that the connection object uses my connection string.
Then I added a "Backup Database Task" to my package with the name BkpTask. Now whenever I try to set the connection property of BkpTask to the testCon connection object, by typing testCon, it automatically gets cleared. I am not able to set the connection value.
Then after spending several hours I found that this is because I have customized the connection string in testCon. If I don't customize the connection string, I am able to enter the "testCon" value in the connection property of the BkpTask.
Hi,My application is acting weird and the research I've done leads me to an answer why it's breaking, but not how to fix it.My web app is hosted by a remote company. I have the main website sitting in the root web directory and in a subfolder I have another application that will handle login and updating the content in the SQL 2K5 backend (not an Express DB).The website itself is able to pull in informaiton from the DB without any trouble, so I know it's able to connect to the DB and everything is peachy. The login app though is acting as though it can't get to the DB. I've applied the ASPNET schema to the DB using the aspnet_regsql.exe from the framework and the login application is using the same connection string as the main website.One thing to note here is that the website and the login app have seperate web.config files since they were developed as different projects.With my customized provider defined in the web.config for the login app I get this error on page load:"Parser Error Message: It is an error to use a section registered as allowDefinition='MachineToApplication' beyond application level. This error can be caused by a virtual directory not being configured as an application in IIS." If I comment out the custom provider, the page loads but when I try to create a user it tells me that "...the SQL Server couldn't be contacted. This may be because it doesn't accept remote connections." Thing of it is that it's not a remote connection and it's the same connection string and db as the other site.Now, the research I've done points at the two different web.config files, which makes sense. But how do I make the one web.config work for both the website and the subdirectory with the login app? Keep in mind that I can't create any new virtual directories or modify IIS since this is a remote hosting company.Can someone help me understand the fix with a little more clarity?
I'm kind of the first person at my new company to start a project using SSIS. My question(s) revolves around setup and configuration, maybe even best practices.
1. Connection Manager Config - What are some of the methods you use to config your connection managers? I was planning on creating a folder (C:SSISConfig or something) and keeping a config file for each connection there. That way it would be fairly trivial for each developer to also put said folder on there box for deving.
2. Solution set up - Are people creating solutions for each package or just using one Master solution and adding all the packages to that? Or maybe some combination, like having solutions for different areas of functionality or something..? I'm trying to figure out what the pain points are with different approaches.
If you have any other information or horror stories about SSIS that you think might help, feel free to pass them along.
I want to make an application using a distributed database (single logical database, parts of which are located at more than one location or more than one server). It acts as a single collection of data but the database itself is geographically in separate locations. How do I make it in SQL Server 2005?
I am trying to use a custom C# dll from within a script task. The C# dll relies on the Enterprise Library 2.0. Where do I put the enterprise library configuration when I am debugging a package and then when I have the package in production. I cannot find any documentation anywhere on this.
Does anyone have an example of a web.config file that works with Dynamic Data controls? I asked because I can't seem to arrange my "config sections" in any working order. I use 1 connection string in the web.config to establish a conversation with the database. Thanks in Advance.
I am using vs2005 and have a solution file with 4 projects, 2 of which are presentation layers (admin sections), 1 is a business logic layer, the other a data access layer. The data access layer project is simply a class library that will provide data access functionality. Im using a dataset (xsd) to create my data access objects. My question is how do I select a connection string from the web config file (which is in each of the presentation layer projects) as the data access layer is a class library there is no web.config. Using the connection string selector in the dataset wizard doesnt show any available connection strings.
