Problem When Selecting Dynamic Connection Setting
Aug 8, 2006
Hello All,
I have a SSIS package, desgined in such a way that, there is a table called connection which hold the connection name i.e similar to my DSN connection name & a ID for each DSN there is a unique ID. Now i want if there is a 10 record in the table & i have only 5 DSN connection then i'll work for 5 & for other 5 it 'll skip the process. my task is running success fully for 1 connection. i have used foeach loop container & 3 varibable for that 2 for holding the table value & 1 for refereing the recordset
thanks
View 4 Replies
ADVERTISEMENT
Jul 12, 2004
I have the following Tables:
Table1: Row1
----------------------------------
Name Gary
Garbage1 A
Garbage2 B
Table2: Row1 Row2 Row3
------------------------------------------------------------------
Name Gary Gary Gary
Value 1 2 3
Desc Day Afternoon Night
Table1 has a 1 to Many relationship with Table2
Id Like to have the select statement Return the data as follows:
Gary,A,B,Day,1,Afternoon,2,Night,3
not like this:
Gary A,B,Day,1
Gary A,B,Afternoon,2
Gary A,B,Night,3
Any Ideas?
View 3 Replies
View Related
Sep 25, 2015
I've got an update statement with a subquery (I'll post the code further down) that I need to either make dynamic or do something else to make sure it does what I want.
The query is as follows:-
UPDATE dbo.tbl_Process_List_Control_Table
SET LastUpdateDateTime = (
SELECT ISNULL(MAX([LatestRowUpdateDateTime]), @LastUpdateDateTime)
FROM [wtbl_Process_List_Patient]
)
WHERE ProcessList = @ProcessName
This is a called proc with the following parameters:-
@LastUpdateDateTime is the date of the last record to be loaded
@ProcessName is the name of the process that was started/finished.
I need to make [wtbl_Process_List_Patient] dynamic so it looks at a different table based on a passed parameter.I've tried making the whole thing dynamic but it states I need to declare @LastUpdateDateTime which I can't see how to do as it's already passed to the proc (as is the process name).
View 6 Replies
View Related
May 25, 2004
Not wishing to derail the other recent thread on loading a local variable, I've posted this query (hee,hee,hee...I kill me) on a separate thread...though I think I am trying to do something similar...that is to build a dynamic select statement, but return a count of the rows it finds/doesn't find to a local variable...using the (amazingly timely) responses above, I tried this:
Note that the local variables @TargetDate and @TLevel are necessary because they are being passed into the procedure as variables....
DECLARE @SQLCmd varchar(256)
DECLARE @TargetDate smalldatetime
DECLARE @TLevel int
DECLARE @n int
SET @TargetDate = '2004-05-24'
SET @TLevel = 1
SET @SQLCmd = 'SELECT @n = count(*) FROM EventLog WHERE ((CONVERT(varchar(10), [Date], 101) = ''' +
CONVERT(varchar(10), @TargetDate, 101) + ''') AND (MsgLevel = ' +
CONVERT(varchar(3), @TLevel) + '))'
exec (@SQLCmd)
if @n > 0
print 'yep'
else print 'nope'
and, it's TRYING to work...but apparently the local variable @n is not recognized in the execution of the dynamic statement, as this is the output:
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@n'.
nope
Thoughts?
View 8 Replies
View Related
Jun 8, 2008
I have a simple gridview control set up that contains a single ControlParameter (a DropDownList) whose value is used in my SqlDataSource's SelectCommand:
SelectCommand="SELECT * FROM [Wood_table] WHERE [wood_type] = ISNULL(@wood_type, [wood_type])"
The ISNULL check is so that I can select "ALL" from my dropdown and get all the rows with null values.
Everything works fine. Now what I'd like to do is exclude a specific wood_type value from the query if a checkbox control is selected. So what I'd like my select query to be when it's checked is something like
SelectCommand="SELECT * FROM [Wood_table] WHERE [wood_type] = ISNULL(@wood_type, [wood_type]) AND @wood_type <> 'pine' "
So I'd like the option of excluding that certain type of wood from the default query (all rows). I thought it might be better to just have a value in the dropdown list that was "ALL except pine" but that doesn't seem like it would work, what would I bind that to?
I fooled around with having the CheckBox oncheckchanged event set a global based on the checked status, easy enough, but then how can I modify my SelectCommand? Should I just access the SqlDataSource object programmatically in my CheckBox handler and fiddle with the SelectCommand property? I tried this and it works, but this seems messy, now if I modify my GridView in Design mode I need to remember to update my SelectCommand strings in the CheckBox handler too. Is this the best way to do this?
View 3 Replies
View Related
Feb 24, 2006
Hi there,
I am writing a Custom Destination component with a custom UI. The UI contains a combo box which contains the connection names of type €œFLATFILE€?. I also have provided a button which would create a new connection of type €œFLATFILE€? by making a call to CreateConnection method of IDtsConnectionService.
The combo box gets properly updated showing the connections of type €œFLATFILE€? but on clicking on the new Connection button the application hangs up.
Am I missing something or is there some other way to do it?
The function are the events handlers which are called by the UI.
void form_GetConnections(object sender, AvailableColumnsArgs args)
{
Debug.Assert(this.ServiceProvider != null, "Service Provider not valid.");
this.ClearErrors();
try
{
IDtsConnectionService connectionService = (IDtsConnectionService)this.ServiceProvider.GetService(typeof(IDtsConnectionService));
if (connectionService != null)
{
ArrayList temp_Connections =
connectionService.GetConnectionsOfType("FLATFILE");
args.AvailableColumns = new AvailableColumnElement[temp_Connections.Count];
for (int i = 0; i < temp_Connections.Count; i++)
{
ConnectionManager runtimeConnection = (ConnectionManager)temp_Connections;
args.AvailableColumns.AvailableColumn = new DataFlowElement(runtimeConnection.Name, runtimeConnection);
}
}
}
catch (Exception ex)
{
this.ReportErrors(ex);
}
}
void form_GetNewConnection(object sender, AvailableColumnsArgs args)
{
Debug.Assert(this.ServiceProvider != null, "Service Provider not valid.");
this.ClearErrors();
try
{
IDtsConnectionService connectionService = (IDtsConnectionService)this.ServiceProvider.GetService(typeof(IDtsConnectionService));
if (connectionService != null)
{
connectionService.CreateConnection("FLATFILE");
ArrayList temp_Connections =
connectionService.GetConnectionsOfType("FLATFILE");
args.AvailableColumns = new AvailableColumnElement[temp_Connections.Count];
for (int i = 0; i < temp_Connections.Count; i++)
{
ConnectionManager runtimeConnection = (ConnectionManager)temp_Connections;
args.AvailableColumns.AvailableColumn = new DataFlowElement(runtimeConnection.Name, runtimeConnection);
}
}
}
catch (Exception ex)
{
this.ReportErrors(ex);
}
}
Has anyone else run into this?
I am running
SQL 2005 9.0.1399 and VS 2005 8.0.50727.42 (RTM.50727.4200) on Windows Server 2003 Enterprise Edition SP1.
Any suggestions would be welcome.
The code sample is an extension to the RemoveDuplicates sample (Dec 2005) which comes along with the SQL Server.
TIA,
Robinson
View 5 Replies
View Related
Apr 22, 2008
Hi
I currently have an SSIS package which looks for an excel file and uploads its. The connection is set up to look for a file upload.xls in a folder called Upload on the C drive. So each time a file needs uploaded, the upload.xls is copied into C:Upload. This all works fine. However now I need to change it so that the files that are uploaded take the format Upload10012007.xls, so they will have the date appended onto the end of the Upload.
How do I go about setting up my connection to look for Upload10012007.xls? The file name will be dynamic as it will change each day but it will always contain Upload at the start of the filename.
Thanks for your help,
Michael
MichaelRaj Arokiyasamy
View 1 Replies
View Related
Sep 20, 2006
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
View 9 Replies
View Related
Apr 13, 2006
I'm looking for some clarification.
We need to pull data from our DB2 system to our Itanium SQL Server.
Is this simply a matter of setting up the 64-bit DB2 Connect drivers on the Itanium box with the exact same DSN name used on ther 32-bit box used to define and deploy the SSIS package?
Has any one done this, yet? If so, is there anything that we need to look our for?
Thanks,
- Paul
View 1 Replies
View Related
Dec 12, 2005
Hi I downloaded MSDE2005 and I tried to set up an ODBC connection to adatabase I created. I can see the database using client but I got thiserror through the ODBC manager[Microsoft][SQL Native Client]An error has occurred whileestablishing a connection to the server. When connecting toSQL Server 2005, this failure may be caused by the fact thatunder the default settings SQL Server does not allowremote connections.Can anyone, please tell me what I need to do to get this setup? I triedto dabble with the TCIP and remote setting but nothing worked.Regards
View 1 Replies
View Related
Feb 15, 2006
I've mapped a sqlserver machine within vs2005, but when I attempt to set the database connection, I dont see place to stick the port number (even in the advanced area)... it continues to fail when I test the connection.. I can telnet into the machine on the sql server port, so I know its open to me...
Also - when I publish this to an IIS server, what is the syntax for placing the connection and port number in web.config? I've tried various settings, cant get it to work. <appSettings><add key="con" value="Server=12.123.123.123;PORT=1234;UID=*****;PWD=*****;database=mydb" /></appSettings>
Thanks in advance for the help,
Todd
View 3 Replies
View Related
Mar 1, 2006
When setting up an ODBC connection on a non-sql Server X and choosing from the drop down menu which SQL Server to connect to, I see two identical names for my SQL Server (Y). No matter which one I choose, Im not able to go to the screen after being asked how I want to log in (SQL server authentication)
If I build a ODBC connection from any other desktop, I only see one SQL Server Y, and am able to establish a connection to SQL Server Y, with the same user account and password as when Im on Server X.
Server X has two network cards and is teamed...could that be a possible problem ?
Any ideas ?...Thank you
View 7 Replies
View Related
Jul 20, 2005
I need to set multiple values for some SQL statements, for exampleSET NUMERIC_ROUNDABORT OFFGOSETANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ONGOin a .sql file, but would like to reset them to their previous settingsagain for other SQL statements. I didn't find anything in books online. Ithought just calling "set" was enough, but apparently not ....Thanks.
View 3 Replies
View Related
Jan 30, 2007
I used to do this in DTS but wondered how to do it in SSIS. I have a For Each ADO recordset loop that reads these four columns into variables:
DataSource Name, Name(for a WHERE clause), user and password.
For each "Building" there is a new server to connect to, with identical tables. I have 10 tables in the loop to pump from one database to a SQL 2005 database (i.e. 10 dataflows). I want to set the source connection information with the variables. My questions:
1. How do I set the connection information for the source connections? Do I just set the properties for the connection manager and then it sets for all the sources? What property do I set--do I need a script task?
2. How do I use the values from the "Name" column in a WHERE clause in each data flow?
Thanks!
Kayda
View 4 Replies
View Related
Feb 1, 2007
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:
"Data Source=" + @[User::gv_DataSource] + ";User ID=" + @[User::gv_Username] + " ;Password=" +
@[User::gv_Password] + ";Initial Catalog=blue;Provider=Sybase.ASEOLEDBProvider.2;Persist Security Info=True;"
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.
What am I doing wrong here?
Thanks,
Kayda
View 1 Replies
View Related
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
View 8 Replies
View Related
Sep 17, 2015
I have a SQL Server 2014 installation on a server (CHRIS-PCCHRISSQL).I have SQL Server 2014 management studio installed on local server called Pootle.I have gone through the configuration on server (CHRIS-PCCHRISSQL) inc the following:
(1) I have set up a user on CHRIS-PCCHRISSQL called sqladminuser at server level with the Server Roles of 'Public' and 'SysAdmin'
(2) The computers are both on the same homegroup.
(3) On Chris-PC , I have opened up the firewall port 1433 as Inbound Rule
(4) On Chris-PC, Within SQL Server Configuration Manager,the 'SQL Server Network Configuration for Protocols for CHRISSQL' have been set up as follows:
- The TCP protocol is enabled
- I have set up IP2
as follows:
Active: Yes
Enabled: No
IP Addres: 192.168.0.3
However when I try to connect from SQL Server Management Studio 2014 on my local machine Pootle to Chris-PCCHRISSQL using SQL Server Authentication with the user sqladminuser
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)how to set up a remote connection from SQL Server Management Studio
on my local machine Pootle to CHRIS-PCCHRISSQL
View 6 Replies
View Related
Nov 9, 2007
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.
View 3 Replies
View Related
Mar 19, 2014
 "variables in SSIS for setting any connection string". I tried to Google it but cannot find any specific link.
View 4 Replies
View Related
May 18, 2007
I am trying to connect to multiple data sources of same type say (sql) pn different servers and run the same data flow task on all of them.
what i need to acomplish is make the connection dynamic, i tryed the sample flat file connection and i understand that i have to
1, declare a variable 'connectionstr'
2, in the connection manager properties > expression property i have to select connection string and exeression as @[user::connectionstr']
then i am out of thoughts i know i must use FOR loop but how
even FOR loop has expression, enumerator.....
how would i point the connection string variable to a list of values(srvrname,databasename, tblname) stored in a table.
Thanks in advance...
View 1 Replies
View Related
Sep 12, 2006
Hello ,
I have a table having different ftp url,user name, passwod, port no.I want to copy the file from all the location on my server at.
How can I change the connection string/ FTP location for FTP connection manager at rum time in SSIS.
Thanks
View 14 Replies
View Related
Aug 7, 2007
Hi,
SSIS packages are now created and tested against dev sql server.
Now I would like them all to point to the live sql server.
What is the best way to have the connection dynamic?
Obviously I prefer not to go through each package and change the connection manager.
Thanks
View 36 Replies
View Related
May 17, 2007
Hi All,
A how to question...I have to run a querys on 19 as400
which means i have to supply 19 server,library and file names then run query and update destination table.
Idea was to create a table with server,library and file names and query
iam not able to figure out how iam going to make the connection manager to dynamically take this parameters.
Any help appreciated and thanks for taking time to read this issue.
View 5 Replies
View Related
Feb 5, 2007
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
View 4 Replies
View Related
Feb 6, 2007
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
View 50 Replies
View Related
Feb 19, 2001
Hi,
i want to modify my DTS to don't have any maintenance to do on it if a Username or password change.
Each DTS will have an ActiveX script to read connections properties in a table that i created and will automatically set each connection in the DTS each time that the DTS Run. So, we will have only to update the table and the DTS will continue working fine.
To update the Connections properties i have no problem, the problem is how can i retrieve the data from my table to get the connection properties, without hardcoding a connection inside my ActiveX script? Because i don't want to have to change each ActiveX script in each DTS if the SQL Server paswword change.
Any idea will be appreciated
Thank
Martin
View 3 Replies
View Related
May 29, 2007
I have created a connection in connection manager.
using expressions i have declared a connection string
"Data Source=" + @[User::servername] + ";Initial Catalog=" + @[User::catalog] + ";Provider=SQLNCLI.1;
Integrated Security=SSPI;
Auto Translate=False;"
when i click 'evaluate expression ' it just displays
Data Source=;Initial Catalog=;Provider=SQLNCLI.1;
Integrated Security=SSPI;
any anyon etell me if iam doing any mistake
View 5 Replies
View Related
Aug 15, 2007
We were able to use a dynamic connection string in the report designer, but once we deployed to the report server we are getting the following error:
Error during processing of the ConnectString expression of datasource €˜Dynam€™. Has anyone experienced this, and how did you fix it?
View 5 Replies
View Related
Mar 2, 2007
Our Reporting Services environment uses Oracle as the data source. Based upon the user connecting to the database determines what rows they will see for various tables. How can we dynamically pass the username/password to the connection string? Background: Our users log into Active Directory and are assigned to a group. The AD group name is used to access a control table in Oracle that contains the database username/password for that group€™s connection to Oracle. All subsequent connections to Oracle will use the group€™s username/password from the control table. We have an ASP.NET application that works like this and stores the connection information in the session state. How can we do something similar with our connection in Reporting Services? Note: Our Oracle Database does not use Windows Integration.
View 7 Replies
View Related
Jul 17, 2007
I work on a system that is mirrored. there is production, and UAT(user acceptance testing). We have to do repetitive tasks on the systems. I want to write an application to make those tasks easier, using integration services. I would rather have one app and be able to specify which database server the dtsx packages would use each time. is this possible in dtsx packages, to specify a particular sql server? the tables and databases would not change between production and uat
View 7 Replies
View Related
Mar 1, 2007
I need to be able to deploy my updated website to many customers on a monthly basis and dont want to be mucking around changing the connection strings each time. Some of my web servers have multiple copies of my site and DB so each website will need a different connection string.
The simplest method I could come up with is to use the Application Name field in IIS as it doesn't get overwritten by Visual Studio when I deploy the site.
I am trying to write some code to dynamically change the connection string in the web config but cannot find any way of reading the Application Name field in IIS to use in altering the connection string. I'm using the Global.asax file to change the connection string before the DB gets called.
I had tried embedding the DB in the website folder but it would overwrite the customers database.
View 6 Replies
View Related
Feb 27, 2007
Hi,
In c# - how to pass uid,pwd,dbname and servername as input parameters from vs 2003 windows application (am calling the rdl file from reporting service 2005 web service) to sql server 2005 rdl files.
Thanks,
Shanthi
View 1 Replies
View Related
