Dynamically Setting Connection String Depending On Server_name?
Apr 18, 2008
I need to make my site aware of which server_name it is loading from so it uses a different connection string. (have dev + prod servers for web/sql)
Currently my connection string is in web.config as follows:
<connectionStrings>
<!-- Development and Staging connection string -->
<add name="myconnection" connectionString="server=myserver; user id=mysuer; password=mypassword; database=mydatabase" />
</connectionStrings>
I need to make sure the 'name' is the same for both connection strings since that is how the rest of my site looks for it. However, I'm not sure how to get both in here with some sort of 'if/then' statement to determine which one to use.
I've heard it could be done in global.asax with something similar to the code below, but I dont know how to assign a 'name' to a connection string for that type of setup.
Sub
Session_OnStart
ServerName =
UCase(Request.ServerVariables("SERVER_NAME"))
IF ServerName = "prod.server.com" THEN
...Set Prd string...
ELSE
...Set Dev string...
END IF
End
Sub
I have 3 web identical web apps whose only diff is that they access different SQl Server DB's. I use the SQLDataSource in a number of pages to retrieve data from the db. The apps all use Forms Auth. I would like to be able to see who is the logged on user user and assign the approp connection string to all the SQLDataSources in the app. For example when user UserA logs in they are retrieving data from on db but when UserB logs in they are retrieving data from another db. I am sure this can be done but could use a little guidance. Thanks in advance.
I am looking to dynamically change the connection string in my SSIS package, to avoid changing the connection string each time I want to run in different environments.
I'm using VWD on SQL2005EXPRESS And i keep getting a connection string errorCan someone show me how to put a connection string in the web.config, or which connection string to use to connect to my host database which they're running SQL2000..Thanks Newbie
Hi, I tried to follow the widely talked about method to dynamically populate the connection string property of my flatfileconnection manager from a variable. I keep getting the following non-fatal error.
TITLE: Microsoft Visual Studio ------------------------------
Nonfatal errors occurred while saving the package: Error at Package [Connection manager "FFCM"]: The file name ""C:ProjectsSSISHLoadTOutputOut.csv"" specified in the connection was not valid.
Error at Package: The result of the expression "@[User::CsvFullFileName]" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
Here is what I am trying to do. I have a foreach loop that iterates through a list of xml config files and reads the config information including the destination csv file name and does a data transformation. So I created a flatfile connection to a csv file did my data mappings. Created a package level variable to hold the destination file path In the Flat file conn. manager's properties -> expression -> set the @[User::CsvFullFileName] (which even evaluates fine)
When I try to run the package I keep getting the above mentioned non-fatal error..I checked the path and it is valid. I even tried
the c:\projects\...notation and the UNC path notation...all seem to give the same error
Anyone experience this before ? any thoughts would be appreciated.
Hi Folks ...Question for everyone that I have not been able to figure out. I have an application that is spread across tiers:SQL Connection defined in Web.Config file that connects to SQLServer database.DAL layer created references SQL Connection in Web.Config file - have noticed this copies the connection string information to the local properties for each TableAdapter that is defined in the DAL layer.BLL Layer that references Table Adapters declared in the DAL layer.When the web site is called, the link will provide an encoded id.Sample call to website: http://www.mysamplesite.com/default.aspx?company=AE2837FG28F7B327Based on the encoded id that is passed to the site, I need to switch the connection string to use different databases on the backend.Sample connection string: Data Source=localhost;Initial Catalog=dbSystem_LiveCorp1;User ID=client;Password=live2006 I would need to change Initial Catablog to the following:Data Source=localhost;Initial Catalog=dbSystem_LiveCorp196;User ID=client;Password=live2006How do I do this and have the connection string reflected in all of the Table Adapters that I have created in the DAL layer - currently 100+ Table Adapters have been defined.As we gain new clients, I need to be able to have each clients information located in a different database within SQL Server. Mandated - I have no choice in this requirement. Being as I don't want to have to recreate the DAL for several dozen clients and maintain that whenever I make a change to the DAL to then replicate across multiple copies. There has to be a way to dynamically alter the SQLConnection and have it recognized across all DAL TableAdapters.I'm developing with MS-Visual Studio 2005 - VB. Any help would be greatly appreciated. Thanks ...David Any day above ground is a good day ...
I am trying to set the connection string in a connection manager at runtime. Here is what I have done:
1. Created a gv_DataSource, gv_Username and gv_Password
2. Created a ForEach Loop that reads DataSource, Username and password values from a variable (it is an For Each ADO loop Enumerator). The ADO recordset is read into by an Execute SQL task before the loop.
3. Mapped values from the recordset to variable in the ForEach loop's "Variable Mappings" page.
4. Used the variables in my Sybase OLEDB Connection Manager's "Expression" property, setting the "ConnectionString" property to:
5. I set the values in my database table for the connection-I set 2 connections for which I have Sybase OLEDB datasources setup.
When I run the package, I just get the first server's data twice, it doesn't set the second server's data during the second loop. I made sure the first one was working (i.e. the ConnectionString's property was being set by the data from the current variables) by setting the variables incorrectly in the variable properties page, and then running the package. So the first row of connection information is working, but the second loop around it doesn't seem to be working. I used a msgbox in a script task to show that the variables are mapping correctly in the loop, so it seems the second time around the connection information isn't taking from the variables.
I have create packages which loads the data from flat file to sql server table, now I want to make my destination table connection dynamic what is format of connection string. I also need to pass user name and password for sql server dynamically in this case, what is the format for the connection string.
Also in package i used ADO.net as source for *.mdb files how i can set the commection to .mdb files dynamically which is used as source in my package.
I have a folder of excel files with different file names and sheet names inside the file. So File A could have Sheet1A and file B could have Sheet2B. The assumption is that the sheets would have similar data in them for all files even though sheet names are different. I was able to dynamically create the excel connection string using a for loop and choosing the Data Access mode of the excel source to be Table name or View name variable. I have run into a "[DTS.Pipeline] Error: "component "Excel Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".
Was wondering if anybody was able to play in such a scenario and resolve the issue.
By the way a lot of the posts in this blog helped me get this far so thanks for all the posts.
Hello,Is it possible to set a comparison operator using a parameter value?The code below shows what I'm after;declare @co char(1)declare @date datetimeset @co = '<'set @date = '02/02/2002'select * from recipe where date @co @dateI would use an if statement perform two seperate statements depending on the value of co, but this is only one of 13 statements where i need to have different combinations of comparision operators.thanks
We have set up Mirroring with a witness server and everything works fine when we failover from the SQL Management console.
However, if we failover when our Maccola client is connected, the client blows up - clearly because it can no longer connect to the database.
The ODBC DSN used by the Maccola client shows a checkbox for the 'select a failover server' but the checkbox is grayed out.
Also the summary of settings for the DSN at the end of the wizard reveals that the failover to server (y/N) option is set to N.
The default setting for this DSN is 'populate the remaining values by querying the server' but it doesn't appear to be getting the settings for failover from the server or any other interactive DSN settings either. The server is clearly set for mirroring.
Another suspicious item is that the DSN cannot connect to the server with SA permissions, even though the server is set to mixed security and we use the correct authentication.
Is it possible that the client MACHINE is not authenticating with the domain or sql server properly. We are logged into the client with the domain account that is the SQL admin account on the sql server box.
We should be able to interact with the sql server settings through the ODBC DSN on the client shoulnd't we?
I am using execute pacakge task to execute another package . I am giving the Connection string for the package to execute. It works fine in my development machine but when i try to run in another server after i deployed it. It looks for the datasource path of the DTSX file in the same location.
how do i set the path according to each server where the dtsx file is stored. or any other method of storing it like connection string.
if i store it in theparent package variable where should i point to...
I am creating the report and setting the datasource(dataset) dynamically to the report. I want to set the parameters also dynamically. am using Report Viewer to process the report.
When i set the parameters locally, i am getting the below error: An error occurred during local report processing
Here is my code: da = new SqlDataAdapter(strqry, conn); ds = new DataSet(); da.Fill(ds);
ReportDataSource ReportDataSourceX = new ReportDataSource(); ReportDataSourceX.Value = ds.Tables[0]; ReportParameter[] parm = new ReportParameter[2];
parm[0] = new ReportParameter("Business_Function", "SQMO"); parm[1] = new ReportParameter("Application", "ETMRS"); parm[0] = new ReportParameter("Owner", "Subbu"); //parm[0] = new ReportParameter("Business_Function", ds.Tables[0].Columns["Business_Function"].ToString()); //parm[1] = new ReportParameter("Application", ds.Tables[0].Columns["Application"].ToString()); //parm[0] = new ReportParameter("Owner", ds.Tables[0].Columns["Owner"].ToString());
I have posted concerning this-I don't mean to double post but I have isolated the problem and have new information. (and I'm desperate!!) Please direct me to a DTS forum if this is not the place for this, I didn't see one.
I am trying set the data connection object (ODBC to Sybase) twice in the same package but the Data Pump Tasks that use this connection don't seem to be using the new setting in the connection after the second time it is set, although at the end of the package I can see that the connection has been set. The pumps just use the original connection a second time instead of using the 2nd connection.
Here is the progress: 1. SQL Task1: Gets server information from a SQL Server table, and sets it to a recordset global variable in the DTS. 2. ActiveX Task1: Begins loop, and sets the connection to server2 and sets sql of datapump tasks 3. Several data pumps run. 4. ActiveX Task2: just loops around to ActiveX Task1 5. ActiveX Task1: Sets the connection object to server2 (msgbox tells me this happens successfully) 6. The datapumps continue to use server2(??) 7. Package finishes succesfully 8. I right click on first datapump and do a "execute task". It executes using server2, which for some reason it did not do during execution of the entire package.
The loops works fine (as I have gotten it from you Allan), it clearly goes through twice when I have specified two servers (I can see the data in the resulting table is doubled from the source server, and I can see it in the progress window going through the data pump steps twice). After, if I run the data pump tasks by right clicking and doing an "execute task" then it works using server2 was set.
I put a msgbox in the ActiveX Task1 to confirm the server is being set correctly.
Any ideas here? Is there some other property I need to set? Is it a setting with the Data Pump task? This all seems very weird-I feel like I must be missing something obvious.
Here is the part of the code of ActiveX Task 1 (only part) that sets the connection (let me know if you want to see the package):
Dim pkg Dim SCCScon Dim objRS Dim SQLStatement
set pkg = DTSGlobalVariables.Parent set SCCScon = pkg.Connections("Connection_Name")
set stpEnterLoop = pkg.Steps("DTSStep_DTSDataPumpTask_1") set stpFinished = pkg.Steps("DTSStep_DTSActiveScriptTask_5")
Set objRS = DTSGlobalVariables("gvDataSources").Value
I am trying to dynamically set the name of a checkpoint file and I have used expressions in the package property and evaluated the following expression correctly
Can you dynamically set the name of the table in the SelectCommand section of the SqlDataSource? (If it is relevant, I code in C#) For example, <asp:SqlDataSource runat="server" ID="SqlDataSource1" ConnectionString="<%$ ConnectionStrings:Test-MySQL %>" ProviderName="<%$ ConnectionStrings:Test-MySQL.ProviderName %>" SelectCommand="SELECT * FROM TestTable"> I would like to replace 'TestTable' with the name of a table that is extracted from a string array in the code-behind. Appreciatively,Peter
Does sombody have experience on dynamically set or change the default value of a report parameter?
Assuming: report parameters p1, p2, p3, p4 have been set up(and have their default value 'all') with the creation of the report1; report browseing is through reportviewer that embedded in the web application; datasource is datacube
What I want to do: based on the login user of the my web application, set default value of p1 as the user's username.
What I did is:
Microsoft.Reporting.WebForms.ReportParameter reportParam = new Microsoft.Reporting.WebForms.ReportParameter("P1","Mary");
Hello all, Ok, I finally got my SqlDataSource working with Oracle once I found out what Oracle was looking for. My next hurdle is to try and set the Update Command and Parameters dynamically from a variable or radiobutton list. What I'm trying to accomplish is creating a hardware database for our computers by querying WMI and sending the info to textboxes for insertion and updating. I got that part all working nicely. Now I want to send the Computer name info to a different table column depending on if it is a laptop or desktop. I have been tossing around 2 ideas. A radiobutton list to select what it is and change the SQL parameters or do it by computer name since we have a unique identifier as the first letter ("W" for workstation, "L" for Laptop). I'm not sure what would be easiest but I'm still stuck on how this can be done. I posted this same question in here a few days ago, but I didn't have my SqlDataSources setup like I do now, I was using Dreamweaver 8, it is now ported to VS 2005. Below is my code, in bold is what I think needs to be changed dynamically, basically i need to change DESKTOP to LAPTOP...Thanks for all the help I've gotten from this forum already, I'm very new to ASP.NET and I couldn't do this without all the help. Thanks again!
Hi all I hope someone can help me on this issue: I am designing dynamical reports, that are abel to run in four different languages - so far everything works just fine for me (I select my headers and labels from a database). Only problem is the prompt text for report parameters. How can I set this text dynamically?
E.g. I have a parameter where the user selects a storage - this prompt should be "Storage: " in the English report and "Lager: " in the Danish report. I have absolutely no idea how to fix this.
I have been playing with integration services for a few days and at the moment, its up there with my list of software that I find ......painful.
What I am trying to do is read different tables from my one SQL database, then populate my Access database with its data.
I have put a foreach loop which goes through a collection SQL statements that I have entered into it. It first assigns it to a string variable called tablenameVar which contain statements such as "Select * from Terminals". Then the datareader is supposed to execute it based on the connectstring which never changes, and the SQLCommand value which I passed using the variable @[User::tableName]. However when I try to run it, I'm getting this error.
I have developed an SSIS package which extracts and creates 5 flat files and finally using Process Extraction task zip the folder. On my Dev environment everything is working fine but when I am moving to SIT and UAT, not able to set up jobs dynamically by importing XMLConfig file.I created variables and assigned values but still it doesnt take.Below are varaibles I created for flat file destination, Arguments and Working Directory (for zipping)On UAT when I go to SQLAgentJobs to set, import .dtsx file, XML config file....the new values doesnt appear. why ?DataSource is taking always dev location....why ? How can I set it up to take dynamic values what I mentioned in config file ?
We want to start a job remotely on another SQL7 server.
innocently we thought that should be no problem with sp_start_job.
We used the command listed below:
use msdb go EXEC sp_start_job @job_name='MyJobName', @server_name='blablabla'
The strange thing is, that sp_start_job ignores the @server_name at all.
Even if we enter complete nonsense for @server_name the job gets started as long it is present on the local sqlserver instance. sp_start_job seems to search locally for the given job_name, only. Consequently if we enter the remote server's name the job fails as the jobname is not present on the local sqlserver.
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, I have a script task in SSIS which dynamically generates a string for a file name to be used as flat file source. I execute the task and it executed with success; but when I checked the result of the variable TotFileName from the Expression builder window for the flat file connection manager it was not populated with a file name like \MyServerMyDriveMyFolder200706daily.txt. So something might still be missing from the script below. Or is the way I do it correct? Can someone help with this? Thanks a lot!!
I have created package level variables ImportFolder (value like: \MyServerMyDriveMyFolder ) and TotFileName (value field empty) and make ImportFolder a ReadOnlyVariable and TotFileName a ReadWriteVariable. Then I use expression to set the property for flat file connection manager to use the TotFileName variable.
(Basically the idea is: if now is July 2007 then the filename should be 200706daily.txt; if now is Jan 2008 then the filename should be 200712daily.txt)
-----code------
' Microsoft SQL Server Integration Services Script Task ' Write scripts using Microsoft Visual Basic ' The ScriptMain class is the entry point of the Script Task.
Imports System Imports System.IO Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes. ' To access the object model, use the Dts object. Connections, variables, events, ' and logging features are available as static members of the Dts class. ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure. ' ' To open Code and Text Editor Help, press F1. ' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dim TotFileName As String Dim TrueFileName As String Dim sYear As String Dim sMonth As String Dim sDate As String
i have to store some data on a remote sever(MS SQL SERVER2000). The scenario is like 1. The web application runs on a local machine. User (who inputs) uses through LAN.2. The Input should be stored in the remote server. if the remote connection is ok. otherwise it should be saved in local server's database(MS SQL 2000).3. In the application's web.config there is a connection string pointing to the remote server and another one (alternating one) points the local server's database. in scenario like this i first to tested the remote connection. if it is not ok then i initialize the local server's connection like thisprivate MyConnection() { try { connectionSql = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnForRemote"].ToString()); connectionSql.Open(); } catch (Exception ex) { connectionSql = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnForLocal"].ToString()); } finally { connectionSql.Close(); } connectionSql2 = new SqlConnection(ConfigurationManager.ConnectionStrings["Temp"].ToString()); }My problem is when the remote connection is lost it takes almost 1 minute to store in local database. how can i make it more time efficient. Thanks....
Hi - hope someone can help. We're creating a ASP.NET C# site that allows a user to connect to any of our 100+ SQL Servers and query a database that sits on every server. I'm trying to work out (and failing) how we can set the 'Data Source' in a connection string dynamically using the server name selected by the user in a drop down box. Any help appreciated, thanks in advance,
Hi. I have this kind of problem since I am not very conversant with SSIS and stripting in VB.NET.
The set-up is the following: Flat File Source -> Script Component -> Flat File Destination
The flat file source looks like this:
NameOfFile Headers Data Data Data and many more rows of Data NameOfAnotherFile Headers Data Data Data and many more rows NameOfAnotherFile Headers Data Data and so on in the same manner...
My stript looks like that (not very complicated): Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) Dim strRow As String = Row.Column0.Trim() Dim strFolder As String = "Data" Dim strConn As String Dim charSep() As Char = {CChar(" ")}
If Mid(strRow.ToLower(), 1, 2) = "d0" Then
' This is the file name, so start a new connection to a new file ' which definitely does not exist yet strConn = strFolder + strRow + ".csv"
' DestinationFile is the name of the ' Flat File Connection Manager With Me.Connections.DestinationFile .ConnectionString = strConn End With
Else Row.Column0 = String.Join(",", strRow.Split(charSep, StringSplitOptions.RemoveEmptyEntries)) End If End Sub
I have several questions regarding this one.
1. Is it ok to change the ConnectionString property of the manager to redirect the row to a different file? If not, what more to do?
2. If the new destination file does not yet exist, will it be created for me or will I get an error? If I do, what to do not to?
3. The most important: What steps to take so that a row is discarded from the pipeline in the script? I want some rows not to be directed to the file destination. These are the lines that contain the name of the file into which the data below belongs.
If you can optimize some steps in the script, then please do so by all means. Thank you for any comments and the knowledge you kindly agree to share with me. Darek
I have below SQL. When I run it I get the 'Each GROUP BY expression must contain at least one column that is not an outer reference' error. The date and string expressions are generated dynamically and need to be grouped upon if possible. What am I missing?
INSERT INTO tblStaffPayrollHistory (StaffID, FromDate, ToDate, PayrollNo, EventID) SELECT DISTINCT tblStaffBookings.StaffID, CONVERT(DATETIME, '2015-05-17', 102), CONVERT(DATETIME, '2015-05-17', 102), 'tree', tblEvents.ID FROM tblStaffBookings INNER JOIN tblEvents ON tblStaffBookings.EventID = tblEvents.ID WHERE ... GROUP BY tblStaffBookings.StaffID, CONVERT(DATETIME, '2015-05-17', 102), CONVERT(DATETIME, '2015-05-17', 102), 'tree', tblEvents.ID
I have an ssis package that contains a DateTime variable named RefDate its value saved in the package is 8/31/2006. I setted up a Package configuration for read the value of RefDate from the parent package.I inserted a script task with message box to verify that the package configuration works. When I launch the parent package the message box shows the value of RefDate setted in the parent package. The problem is that when I use RefDate to dynamically set the connection string of the log connection of the SSIS log provider for XML files for the child package, the connection string contains the child value of RefDate.
Hi, I am new to SSIS and i have to develop a ssis package which will run in a production machine through VB.Net(2003) exe.I am facing a problem while setting connection string of SSIS package dynamically.Can anybody help me on this?
