I have 4-5 DTS packages with more than 100 connections. If I want to change the connections of the source (Oracle Database) from Server 1 to Server 2, how do I do it dynamically....? I know that I can go to Disconnected Edit and change the connections manually , but it is a very tedious process as there are more than 100 connections.
Thanks.
Well I've made my way to these forums for another reason, but figured I'd take the time to ask this question as well.
I've been using VS 2005 and creating some SSIS packages. However I've found that if I create a package with server 166 (just to give an identity) as the destination, then want to go to server 234 with the same data... I can't.
When I add a new conenction to the package for 234 and then try and change the output destination to use 234 it will never connect.
I get a DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER error regardless of what I do.
I find all kinds of Google messages about this but none of them work for me.
I end up having to create a new package and then copy and paste the SSIS contents to it and use the newly created connections in this new package to make it work. Which results in me having to upkeep n packages for each server we have.
Thanks!
- Clarence
1) We are doing data migration from SQL Server 2000 OLTP design to SQL Server 2005 OLAP design.
2) We have used SSIS packages and data flow tasks in which we mentioned connection strings for source and target
containers.
3) We have a master execute package which contains series of execute packages in relational order.
4) When we execute this master package, we should be able to dynamically specify different source and connection
strings for all packages.
5) Can we do it with dtexec option /Conn[ection] IDOrName;ConnectionString
We know what to give for ID or Name but what do we give for ConnectionString. Will it be the source/target one? Can we specify both source and target ones with which the data flow packages get executed.
I have an ActiveX task containing vbscript that when placed in a DTS package, will set all Connection and DataTransformation (DataPump) information correctly. Additionally, the package appears to work perfectly afterwards.
The Question:
Why when I go into the DTS Designer and open a transformation (Black Arrow) between connections - that I get the "Verifying Transformations" dialog with the three radio button options of "Remove Invalid transforamtions", "Change Source/Destination", and "Remove all transformations and redo auto-mapping"?
It appears that the SourceObjectName and SourceSQLStatements are getting correctly updated as well as the DestinationObjectName, etc.
Is there a property that is supposed to be set when I modify the source and destination properties in my code to prevent this dialog?
Thanks a bunch in advance for any assistance
Scenario:
130 dtsx packages using 4 matching connections.
3 of those connections are stored in an SSIS Configuration table in an Operational database.
The last connection is in a shared data source and points to the Operational database so the packages can grab the others.
Problem:
It's time for deployment and all of those connections must change to production servers.
The 3 are no issue, just change the ConfiguredValue in the SSIS Configuration table on the production box to point to the other production servers.
However, the fourth one... I had made an assumption that when you changed a shared data source it filtered down throughout all the packages. We all know what assumptions do to you.... So. I need a way to change all 130 connections (and be able to change ALL packages quickly and simply for other projects in the future)
Solution:
It has been suggested that we use another package to run though all of the packages and change the connection with a script task. I can live with this (and more importantly so can our DBS's who have to deploy).
I have one snippet of code to ADD a connection using a variable holding the connection string, but we dont' want to add one, just change an existing one.
Has anyone else done this? Or had a similar problem and way to fix?
We are likely to have many projects in which the connections MUST change at deployment, and the idea of going into every package to make the change is sad at best... We would be more likely to move back to a competitor's product that has a connection repository, than continue with SSIS.
Ches Weldishofer
ETL Developer
Clear Channel Communications
Whenever I make a breaking change to a custom SSIS component/tasks and update the Assembly Version, it seems to break my packages beyond repair, telling me it can't load the task:
Error loading Package1.dtsx: Error loading a task. The contact information for the task is "". This happens when loading a task fails.
All of the properties of said task now show:
Could not get value for property 'c-155-designer-name'. Specified cast is not valid.
Typically, a "breaking" change when it comes to code just means that you need to update your components to adhere to the new contract of the updated signatures. But with SSIS, it seems the only solution to this is to completely remove the component, and re-add the new version, and re-enter all of the property values/expressions. If I have a package containing 10 instances of a task that only had one property removed, for example, this results in a very time-consuming process of fixing my package.
So my questions:
1) Am I doing something wrong in my versioning/deployment that is causing my packages to unnecessarily break?
2) If this is just "by design" and the way it's meant to behave, what is the best practice for making breaking changes to custom tasks/components used by many packages? Should I just never change the assembly version, even when it is a breaking change (this seems to be less disastrous)?
3) As a last resort, if I'm stuck with having to fix the broken tasks, is there a better way to fix them rather than having to completely remove them, re-add them, and re-set all of their properties/expressions?
Thanks in advance.
Jerad
I've run into a problem with SSIS packages wherein tasks that write or copy files, or create or delete directories, quit execution without any hint of an error nor a failure message, when called from an ASP.NET 2.0 application running on any other machine than the one where the package was created from. By all indications it appeared to be an identity/permissions problem.
Our application involves a separate web server and database server. Both have SQL Server 2005 installed, but the application server originally only had Integration services. The packages are file system-deployed on the application server, and are called using Microsoft.SqlServer.Dts.Runtime methods. For all packages that involve file system tasks, the above problem occurs.
When the above packages are run using the command prompt (either DTEXEC or DTEXECUI) the packages execute just fine. This is expected since we are using an administrative account. However when a ShellExecute of the same command is called from ASP.NET, the same problem occurs.
I've tried giving administrative permissions to the ASPNET worker process user to no avail.
I have likewise attempted to use the SQL Server Agent job approach but that approach might not be acceptable for our clients since it means installing SQL Server 2005 Database services on the application server.
I have read the relevant threads in this forum, namely http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1044739&SiteID=1 and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=927084&SiteID=1 but failed to find any solution appropriate for our set up.
Anybody got any idea on how to go about this?
We manage some SSIS servers, which has only SSIS and SSIS tools installed on them and not the sql server DB.
SSIS packages and configuration files are deployed on a NAS. We run the SSIS packages through DTEXEC by logging in to the server.
We want to allow developers to run their packages on their own on the server, but at the same time we dont want to give them physical access on the server i.e we do not want to add them into RDP users list on server properties. We want them to allow running their packages remotely on the server.
One way We could think of is by using powershell remoting and we are working on that. But is there any other way or any tool already present for the same.
Hi there,
Here we have got a
asp.net application that was developed when database was
sitting on SQL server 6.5. Now client has moved all of their databases
to SQL server 2000. When the database was on 6.5 the previous
development team has used oledb connections all over. As the databases
have been moved to SQL server 2000 now i am in process of changing the
database connection part. As part of the process i have a login
authorization code.
Private Function Authenticate(ByVal username As String, ByVal password As String, ByRef results As NorisSetupLib.AuthorizationResult) As Boolean
Dim conn As IDbConnection = GetConnection()
Try
Dim cmd As IDbCommand = conn.CreateCommand()
Dim sql As String = "EDSConfirmUpdate" '"EDSConfirmUpdate""PswdConfirmation"
'Dim cmd As SqlCommand = New SqlCommand("sql", conn)
cmd.CommandText = sql
cmd.CommandType = CommandType.StoredProcedure
NorisHelpers.DBHelpers.AddParam(cmd, "@logon", username)
NorisHelpers.DBHelpers.AddParam(cmd, "@password", password)
conn.Open()
'Get string for return values
Dim ReturnValue As String = cmd.ExecuteScalar.ToString
'Split string into array
Dim Values() As String = ReturnValue.Split(";~".ToCharArray)
'If the return code is CONTINUE, all is well. Otherwise, collect the
'reason why the result failed and let the user know
If Values(0) = "CONTINUE" Then
Return True
Else
results.Result = Values(0)
'Make sure there is a message being returned
If Values.Length > 1 Then
results.Message = Values(2)
End If
Return False
End If
Catch ex As Exception
Throw ex
Finally
If (Not conn Is Nothing AndAlso conn.State = ConnectionState.Open) Then
conn.Close()
End If
End Try
End Function
''' -----------------------------------------------------------------------------
''' <summary>
''' Getting the Connection from the config file
''' </summary>
''' <returns>A connection object</returns>
''' <remarks>
''' This is the same for all of the data classes.
''' Reads a specific
connection string from the web.config file for the service, creates a
connection object and returns it as an IDbConnection.
''' </remarks>
''' -----------------------------------------------------------------------------
Private Function GetConnection() As IDbConnection
'Dim conn As IDbConnection = New System.Data.OleDb.OleDbConnection
Dim conn As IDbConnection = New System.Data.SqlClient.SqlConnection
conn.ConnectionString = NorisHelpers.DBHelpers.GetConnectionString(NorisHelpers.DBHelpers.COMMON)
Return conn
End Function
in the above GetConnection() method i
have commented out the .net dataprovider for oledb and changed it to
.net dataprovider for SQLconnection. this function works fine. But in
the authenticate method above at the line
Dim ReturnValue As String = cmd.ExecuteScalar.ToString
for some reason its throwing the below error.
Run-time exception thrown : System.Data.SqlClient.SqlException - @password is not a parameter for procedure EDSConfirmUpdate.
If i comment out the
Dim conn As IDbConnection = New System.Data.SqlClient.SqlConnection
and uncomment the .net oledb provider,
Dim conn As IDbConnection = New System.Data.OleDb.OleDbConnection
then it works fine.
I also have changed the webconfig file as below.
<!--<add
key="Common" value='User ID=**secret**;pwd=**secret**;Data
Source="ESMALLDB2K";Initial Catalog=cj_common;Auto
Translate=True;Persist Security Info=False;Provider="SQLOLEDB.1";'
/>-->
<add key="Common" value='User ID=**secret**;pwd=**secret**;Data Source="ESMALLDB2K";Initial Catalog=cj_common;' />
Please help. Thanks in advance.
Hi all
Our data management system currently runs DTS packages using DTSPKG.dll.
I am currently looking at the possibliity of replacing the DTS packages and SQL 2000 with DTSX packages using SSIS in SQL 2005.
Do I need a new dll? or will the current dtspkg.dll handle the new DTSX packages?
Many thanks in advance!
Just a quick question about connection management. My application willnever need more than 1 or 2 connections about at any given time. Also, I donot expect many users to be connected at any given time. For efficiency, Iwould like to keep connections alive throughout the lifetime of the objectsrequiring them, rather than opening a new connection, executing code andthen closing it again. What is the most efficient way of doing this?Should I perform the open/close or just one open when I create the objectand a close when I dispose of it?
View 1 Replies View RelatedHi I have some simple questions of for MS SQL. Say I have a database with a table Called Company. In this table I have Employee's with these columns(EmployeeID<PK>, FirstName, LastName) Now I am inserting some data like this.EmployeeID FirstName, LastName------------------------------------------------------- 1 Bob Smith2 Joe Mitter Now Say I have this situation(this will look weird for this example but for another examples and the stuff I am working on it make sense). Say I have a new Employee Called Jessica White. Jessica must be the second record. So I need to insert it between the 1st and 2nd record. Like I said I am very noob at doing this stuff so I am not sure how I would do it. I first was going to my table and then went to modify. This brought up a blank grid with all my column names. I then started to add all my data in. I found out later to make my life easier I need to enter in certain spots null rows in it to help with formating(otherwise I would have had to figure out a way to do make null rows with c# code). I then went back to this grid and I tried to add a record before the data just like you if you used excel. I quickly found out you can not do that. This resulted me having to recreate the table and reput the all the data back in(this told me I was doing something very very wrong). I was then thinking of writing it with a query and I been playing around with it and still running into problems like say I had a table called test with a coloum called id<PK> and testss(yes bad names just playing around though).So if I did something like this:INSERT INTO testVALUES(2)INSERT INTO testVALUES(1)SELECT * FROM testNow I am trying to figure out how to insert something between rows 1 and 2. Would I have to drop the table or delete all the data and then run a saved copy of my script with the added change or what?My second question is what is a good site that has examples of all the commands(I don't want to read a book on it I have had a oracle sql course and well it was just boring and I did not learn too much.). I think the best way for me to learn is just continue doing my site and picking it up on the file and when I got a problem read about it and ask on the forums. Thanks
View 11 Replies View RelatedVery strange event. Installed new machine into NT 4 network, which has a PDC and a BDC. We have copies of all SQL databases on both the PDC and the BDC. I installed SQL 7 on new workstation. Throught Control Panel/ODBC connections I can reach the SQL files on bith the PDC and BDC. Howver when I try to use Enterprise Manager, I can connect to the tables on the PDC, but not the BDC. SQL lists both machines in the server group, but only allows me to access tables on PDC! Here is the Event information that I recived on the BDC:
The computer IVR1 tried to connect to the server NTBACKUPSERVER using the trust relationship established by the MASSCOM domain. However, the computer lost the correct security identifier (SID) when the domain was reconfigured.
How do I fix this?
HI all,
I have a problem here. I am having two computers both are loaded windows NT 4.0 AND SERVICE PACK 4 AND ALSO I INSTALLED SQL SERVER 7.0.
Now i want to connect those two servers , so pls anyone suggest me how i have to do.I connected both servers thru HUB.
Pls suggest me..
thank u..
--ram
I figure anyone who has done VB and SQL Server might have run into this.
>
> I am set up as a DBO on SQL Server with access to db1 and db2. My default
> is set to db2. From my local machine, I can log through VB(DSN-less) in
> to
> either one without a problem. BUT, if I go to another machine and try to
> log in through VB(DSN-less) to db1, it rejects my login. Here is my
> connect
> string :
>
> UID=xxxxxxx;PWD=pedro;DATABASE=db2;DRIVER={SQL
> Server};SERVER=Athens;DSN=``;
>
> The only thing that changes when I log into db1 is the database parameter-
> "DATABASE=db1".
>
> Am I doing something wrong here? Is there something that needs to be
> setup
> differently in SQL Server? Do I need to put something else into the
> connect
> string?
>
> This happens for the other developers as well, not just me.
>
> Any help would be GREATLY appreciated.
I am looking for some ideas on the following DTS challenge: I need to import the data from an Access 97 database into a set of tables(about 25). The location of the database can be anywhere on the users drive(s) and I will need to get the location of the database from a registry setting and use it to define the connection.
Thanks
We have a case where a user is using Excel sheet to connect the database on SQL2K and generally his connection runs in the upper 150 in numbers. He generally gets more than 150 connections open.
I know that it's not a good thing, but is there a way to limit it or even kill it after it reaches max?
What are the disadvantages of opening too many connections?
I'm getting the classic message "The timeout period elapsed prior to obtaining a connection from the pool" etc when connecting to my SQL Server 2005 Express from a .Net application.
Then I try connecting, simultaneously, from a simple ASP.net thing I wrote just for testing and this works fine. So, then the connection pool can't be full, can it? Or, does each application have its own pool??
Ok, yeah, I'm being lazy...
Any comments?
Hi
I have heard that SQL Server can support upto 10,000 users,
What if my application , connects to the sql & does not do antything then , why does the SQL server slow down with just 700 users.
I mean just the connection is established & NOTHING further is done.
Thanks
hi,
have been looking for ages for somewhere that might be able to answer my question so thought i'd try here.
anyhows, my place of work have goldmine 6.7 running on a server. the problem is that some of the client machines seem to be taking 2 connections to the server when logged into goldmine and some only 1.
when looking at the open sessions it shows a second for some users accessing pipesqlquery. all users show this but only some show it as a second connection.
please any ideas
Is there any type of performance gain to using DSN connections? Or any real advantage to using them over DSN-Less connections?
--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia
As most of you are aware, when you close a VB.NET connectrion to a SQLserver, the connection doesn't actually drop right then and there.From my tests, using VB.NET 2003 and SQL2K, the connection doesn'ttimeout and drop off for 6 1/2 minutes. How can one force thisconnection to immediately drop off with code?
View 12 Replies View RelatedHiI am a dba for sql server recently we are facing problems with the no ofconnections.we have a database called ( x ) every day almost million users is using thatdatabse after some time the cpu showing 100 percent utilization an errorthrowing like odbc error connection so in that case no one is able to connectto the server.when we pause it then restart the server for some time it looksgood then again it will go to 100 % utilization.but there is no blockingoccuringif anyone knows this problem please helpyour help would be appreciated.Thanks--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200510/1
View 4 Replies View RelatedHi I would like to know about connections of sa, where SQL store thenumber of connections
View 1 Replies View Relatedim writing a service that will be on a app serverthis is one of many service's to process Recordsets from Sql Server...1 service may have 20 Processes to Completeexamplemain class PreProcess()method CleanNames();method updateNames();methodr ValidateContracts();Various things must be completedSuedo Example belowMy question isin the main class make my connection and keep it for all sub membersor connect in each memberA. This Way opens connection...closes connectmethod cleanNames()1 get connection2 get recordset3. Close Connection4 process Recordset5. Open Connection4 Commit Changes6. Close Connection()finished cleanames()ormethod cleanNames()1 get connection2 get recordset3 process Recordset4 Commit Changes5. Close Connection()finished cleanames()orthis final way is in the parent Class or Calling Functionsends in the connections holds it open till the service iscompleted of all jobs its Suppose to dothen releases the connectionmethod cleanNames(oconn) connection as parameter1 get recordset2 process Recordset3 Commit Changesfinished cleanames()ThanksDaveP
View 1 Replies View RelatedI have a c# winapp and use SqlHelper to CRUD operation wint an sql server 2005 database, every time mi data access layer opens an connections, I do Close() and Dispose() after the transaction, and I execute SP_WHO and the database server shows me that my winapp has generated a lot of connections, I dont understand why since i clean every connection, Also, I m not using pooling since my connection string. Has someone has faced this before??
Best Regards
Joseph
I have .net 2.0 framework and SQL V 8.0 on my web server.
I am trying to create a DSN and I am not given the OLEDB provider for SQL as an option for my list of providers. Is there a download that will add this ? What do I do to get it????
Hi everybody,
I need a little help...
I created several packages, that after their creation should not be changed again; that's why I created a Package Configuration File in which I set all the Connections I need, so that this is the only file I have to modify.
I set there also the path of the connections for my log file and for my error files (which are both text files), but if I change their paths in the configuration file this change doesn't affect the package, and the path of the log remains the same.
That's an image of one of those packages....
The fact is that I cannot change the ConnectionString of the connection called "log_pkg", but it always remains the same...
How can I resolve this problem??
Thanks everyone
Hello,
This might not be the right forum, but I'll go ahead and ask anyways -
How does SQL server's per seat/user licensing work ? What I'm hoping to find out is that - if some one has procured a 2 user/seat license - will the number of connections that the two users are allowed to open be limited to 2 ?
Or can the two users open any number (reasonable number) of connections ? I'm asking this in the context of the possibility of having multiple connections service our desktop application. Or am I tied to having a maximum of one connection per client/user by virtue of the licensing restrictions ?
Thanks,
Avinash
Forgive me if this is the wrong forum, but it does have to do with data access. Just a quick question: When using an ADO Component /Object to connect to a database, C# or whatever, that connection that is established - How likely is it that data being retrieved from the database is intercepted, is that channel encrypted/secured, and if not for either questions, how do i secure that channel?
View 1 Replies View RelatedHiI have written the following function public System.Data.SqlClient.SqlDataReader ExcuteReader(string strQuery, string strConnection) { System.Data.SqlClient.SqlCommand myCommand; System.Data.SqlClient.SqlDataReader myReader; myCommand = new System.Data.SqlClient.SqlCommand(strQuery, new System.Data.SqlClient.SqlConnection(strConnection)); myCommand.Connection.Open(); myReader = myCommand.ExecuteReader(); return myReader; }to return a datareader for me but right now I am facing the problem that it seems this function isn't closing the connection by itself which is resulting in an error saying "Timeout expired. The timeout period elapsed prior to
obtaining a connection from the pool. This may have occurred because
all pooled connections were in use and max pool size was reached" and if I close it manually I will lose the data in the readerso are there any suggestions of what I should do?thank you
I am getting an error like this
View 5 Replies View Related