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>
      
          <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

Dynamically Assign Connection String

Feb 28, 2008

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.

View 1 Replies View Related

How To Change Dynamically The Connection String.

Nov 15, 2007

Hi,

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.

Thanks in advance

View 3 Replies View Related

Help On Setting Up Connection String

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

Dynamically Populate Flat File Connection String

Mar 12, 2008

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.

Thanks

View 5 Replies View Related

3-Tier Architecture - Need To Change Connection String For DAL/BLL Dynamically On Login

Oct 31, 2006

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 ...

View 1 Replies View Related

Setting OLEDB Connection String With Variables

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

Integration Services :: Passing Connection String Dynamically To SSIS Package

Jul 12, 2012

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.

View 8 Replies View Related

Dynamiclly Setting Excel Connection String Issue

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

Data Warehousing :: Variables In SSIS For Setting Any Connection String

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

Setting Comparison Operator Depending On Parameter Value?

Apr 6, 2006

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

View 2 Replies View Related

No Failover And Client Crashes ODBC DSN Setting For Failover In Connection String

Mar 3, 2007

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?

Are we missing a service pack on the client?

Thanks,

Kimball

View 1 Replies View Related

Setting Connection String For The Package To Execute From Another Package

Jun 29, 2006

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...

thanks

aa

View 1 Replies View Related

Getting Error When Setting Parameters Dynamically

Feb 5, 2008

Hi All,

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());

RptViewer.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Local;

RptViewer.LocalReport.ReportPath = "C:\ETMRS Reporting\ETMRS Reports\ETMRS Reports\PlannedTestCasesSummary.rdl";
//RptViewer.LocalReport.SetParameters(parm);

RptViewer.LocalReport.DataSources.Add(ReportDataSourceX);
RptViewer.LocalReport.SetParameters(parm);
RptViewer.LocalReport.Refresh();

Can anyone please help me in resloving this issue.

Thanks in advance,
SR.

View 9 Replies View Related

Setting DTS Connction Object Dynamically

May 12, 2006

Hi:

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

stpFinished.DisableStep = true
stpEnterLoop.DisableStep = false
stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting

msgbox "DSN is now: " & SCCScon.DataSource

SCCScon.DataSource = objRS.Fields(1)
SCCScon.UserID = objRS.Fields(2)
SCCScon.Password = objRS.Fields(3)

msgbox "Now I've set the DSN and it is now: " & SCCScon.DataSource

_____________________________________________
Thanks as always,
Kayda

View 1 Replies View Related

Dynamically Setting A Checkpoint Filename

Mar 14, 2008

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

Expression: "C:\CheckPoints\" + "CheckPointFile_ "+ @[User:BName]" + ".xml"

Evaluates as: C:CheckpointsCheckPointFile_@[User:BName].xml

when I try to save the package, it comes up with an error saying

Error at Caller Package: the filename is not valid. the filename is a device or contains invalid characters.

Anyone know why?

View 4 Replies View Related

Setting User Variable Dynamically

May 24, 2007

Hi,

I have a package that uses a variable string (in date format) to execute a package.

I want to modify that variable in such a way that if no value is set for that variable then use system date else use date in varaiable.

Can someone help me out in this.. I know it has something to do with the expression builder but donn know how to do it.

Cheers

View 5 Replies View Related

SqlDataSource SelectCommand - Dynamically Setting The Name Of The Table?

May 7, 2007

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

View 4 Replies View Related

Setting Default Value Of A Report Parameter Dynamically

Jan 26, 2007

Hi all,

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");

ReportViewer1.ServerReport.SetParameters(new Microsoft.Reporting.WebForms.ReportParameter []{ reportParam });

what I got when reveiw the report:

P1 has no default value, and p2, p3, p4 greyed and could not choose value from them.

Any idea and suggestion will be pre-appreciated!

Jone

View 1 Replies View Related

Setting Child Package Inner Variables Dynamically

Oct 19, 2006

Hi,

I am trying to execute a DTS on sql server 2000 using the "Execute DTS 2000 Package Task".

I can see the inner variables and when I set the values everything works fine.

Now I want to pass the values dynamically. How can I achieve this?

Many thanks

View 9 Replies View Related

Setting SqlDataSource Update Command And Parameters Dynamically C#

Aug 31, 2007

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!

<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:CAT %>"ProviderName="<%$ ConnectionStrings:CAT.ProviderName %>" SelectCommand='SELECT * FROM "COMPUTER"' UpdateCommand="UPDATE COMPUTER SET DESKTOP = :DESKTOP, TECH = :TECH, SERVICE_TAG = :SERVICE_TAG WHERE USERNAME=:USERNAME">
<UpdateParameters>
<asp:ControlParameter Name="USERNAME" ControlId="txtUserName" PropertyName="Text"/>
<asp:ControlParameter Name="SERVICE_TAG" ControlId="txtServiceTag" PropertyName="Text"/>
<asp:ControlParameter Name="TECH" ControlId="txtTech" PropertyName="Text"/>
<asp:ControlParameter Name="DESKTOP" ControlId="txtComputerName" PropertyName="Text"/>
</UpdateParameters>
</asp:SqlDataSource>

View 1 Replies View Related

Setting Prompt Text For Report Parameters Dynamically

Oct 31, 2007

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 am greatful for any help.

View 6 Replies View Related

Setting Expressions In Datareader's SQLCommand Property Dynamically

Sep 19, 2006

Hi all,

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.

TITLE: Package Validation Error
------------------------------

Package Validation Error

------------------------------
ADDITIONAL INFORMATION:

Error at Data Flow Task [DTS.Pipeline]: "component "DataReader Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

------------------------------
BUTTONS:

OK
------------------------------

Does anyone have an idea of what I should do? or maybe a better way to do it? I appreciate you guys for taking a time to look at this.

Thanks,

Joseph

View 1 Replies View Related

Integration Services :: Dynamically Setting Values Using XMLConfig Files

Sep 29, 2015

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 ?

View 14 Replies View Related

Sp_start_job Does Not Evaluate "@server_name" Parameter

Apr 30, 2001

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.

Any ideas what's going wrong here.

Greetings from Mannheim, Germany
Ricardo

View 2 Replies View Related

Cannot Set Connection Property Of Backup Database Task If Connection String Is Customized In Connection Object

Aug 23, 2006

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.

Is this an intrinsic issue?

View 2 Replies View Related

Why Was ReadWriteVariable Not Populated/updated With A Value Of The Dynamically Generated String?

Jul 27, 2007

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

sYear = CStr(Year(Now()))
sMonth = CStr(Month(Now()) - 1)

If (Month(Now()) < 11 And Month(Now()) > 1) Then
sMonth = "0" & sMonth
End If

If (Month(Now()) = 1) Then
sMonth = "12"
sYear = CStr(Year(Now()) - 1)
End If

sDate = sYear & sMonth

TrueFileName = sDate & "daily.txt"

TotFileName = CStr(Dts.Variables.Item("ImportFolder").Value) & TrueFileName

Dts.Variables.Item("TotFileName").Value = TotFileName

Dts.TaskResult = Dts.Results.Success
End Sub

End Class

View 6 Replies View Related

Dynamically Choosing Connection --please Help

Jul 26, 2007

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....

View 5 Replies View Related

Dynamically Creating A SQL Connection

Mar 6, 2008

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,

View 2 Replies View Related

How To Dynamically Change Connection?

Oct 26, 2007

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

View 1 Replies View Related

Transact SQL :: GROUP Dynamically Generated Date And String Expressions

May 17, 2015

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

View 2 Replies View Related

Dynamically Set The Connection Of SSIS Log Provider For XML

Mar 21, 2007

Sorry for my bad english.

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.

Thanks in advance.
Renzo.

View 6 Replies View Related

How To Set Connection Of A SSIS Package Dynamically

Apr 27, 2008

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?

View 1 Replies View Related