Changing connections at run time
-
I'm having problems finding any useful information about my problem and I thought I would try the experts here. The problem at hand is this.
I created a SSIS package that has two connections; MyDatabaseConn and MyConfigConn. Both connections are pointing to the same database in my local SQL Express database. I have package configuration turned on and I have the configuration data being stored in my database, which is where the MyConfigConn is pointing to. The properties stored in the configuration table is the connection information for the MyDatabaseConn connection. Granted, this seems a little redundant, but consider I may have a ton more properties I will be configuring.
When I test my package locally, everything works great. I saved the package to my SQL Server 2005 server and I created a job schedule for it. I'm executing the package using the DTEXEC utility. Here is what the command looks like.
dtexec /SQL "\ssisTest\ssisMyTest" /SERVER My2005Server /CONNECTION MyConfigConn;"Data Source=My2005Server;Initial Catalog=TestSSIS;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF
Now, based on how I understand things to work, the /CONNECTION switch is suppose to change the specified connection at execution time to point where I have indicated. However, it doesn't. How I know this is that if I go back to my package and change the connection string for the MyConfigConn connection to point to My2005Server.TestSSIS, redeploy it to the server, and run the job, it works just fine. But if I save the package to the server with the MyConfigConn connection pointing to my SQL Express database (MyComputer\SQLExpress.MyTestDB), the package fails with the following error information, which leads me to believe the connection is being made to my Express database and not what I specified in the DTEXEC /CONNECTION.
Executed as user: *****. ...9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 4:44:49 PM Error: 2006-10-26 16:45:06.25 Code: 0xC0202009 Source: ssisMyTest Connection manager "MyConfigConn " Description: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login timeout expired". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "SQL Network Interfaces: Error Locating Server/Instance Specified... Process Exit Code 1. The step failed.
Does anyone have any ideas as to why my problem is occurring? Thanks in advance for your help.
-
Try creating the config file without any of the connection information being exported to it. Then, when running dtexec, use the /SET \Package.Connections[MyConfigConn].ServerName;My2005Server format for setting the connection information.
Also, if you have domains you may have to use SQL logins and you would pass them in using the /SET switch.
Hope that helps.
Kindest Regards,JimAtWork
-
J.L.,
Thank you very much for replying. I'm not sure I follow your suggestion. The part I don't understand is creating a "empty" config file. But, I did try setting my dtexec command to the following and I got the same results.
dtexec /SQL "\ssisTest\ssisMyTest" /SERVER My2005Server /SET \Package.Connections[MyConfigConn].ServerName;My2005Server /SET \Package.Connections[MyConfigConn].InitialCatalog;TestSSIS /CHECKPOINTING OFF
Maybe I wasn't clear on my first post, but the point is that I'm storing my package configuration data in a SQL table. In order for package configuration to work in this manner, I must have a connection (MyConfigConn) in the package that it uses. Since my package will go through the DEV, Test, Prod phases, I don't want to open the package to change the connection (MyConfigConn) for the package configuration stuff to work. So, I thought I could change the MyConfigConn at run time using the Job.
Any more ideas?
-
First I would take the error message at face value and determine whether something running on the server can connect to your local SQLExpress. It may indeed not be configured to allow remote connections.
Next I would wonder about the timing of command-line options vs package configuration file settings. The documentation says that if the same property is set more than once on the command line, or in one or more config files, the last setting wins. I couldn't find it spelled out, but I believe the command line options are supposed to override config file settings. This means the command line settings are processed after the package configurations, so you can't set the config file connection on the command line (if my chain of assumptions is correct).
How about putting the package configuration in XML files, and use command line settings to pick the config file? That would remove one layer of ambiguity.
-
Hey Scott,
Thank you for replying.
It is indeed true that connections are not allowed to my local SQLExpress database. When the job runs the package on the server, I don't want it to connect to my Express database, I want it to connect to my server's database. I have the connection object in my package set to point to my Express database to prove the changing of their connection properties is working or not working; which in this case is not working.
Keep in mind that my package contains two connection objects; MyDatabaseConn and MyConfigConn. My package configuration data is changing the MyDatabaseConn string. I'm trying to get the job to change the MyConfigConn at run time. So, I don't think your idea about the timing of the command-line vs PC is the issue.
I'm willing to bet that if I did store the configuration data for the MyDatabaseConn connection in an XML file and specified that in my job step, it would work like a champ. But our intent here is to store the configuration data in SQL and not in an XML file. So, i must have at least one connection object in my package to retrieve the configuration data. And given what is suppose to be available to us in DTEXEC or using the SSIS subsystem, I should be able to change a connection object's properties at run time.
I think I'm ready for a beer now!
-
I work in the same type of environment (DEV/TEST/PROD) and do NOT use SQL Server to store any part of the package, only files (eg: .dtsx, .dtsConfig) checked in to VSS. You do not need to reference the connection when you are building the config file. DEV, TEST, and PROD have their own configenviro dos command file that set environment variables for the various servers, DB's, logins, etc. The package is run by executing dtexec in the format I submitted previously. When you create the configuration file (if you really feel you need one) don't output anything for the connections (if that's all you're outputting then don't use a configuration file). Set the connection properties the way I gave you in my first post and sub the server name, db name, etc with environment variables (eg: %SourceServer%, %SourceDB%, %DestServer%, etc.). If you are using native SQL Server drivers to connect only with SQL Servers, I'm not sure why you're using a connection string. I don't use one. My packages are run in many environments and may be run from MSBatch (ugh!). A cmd file to run could look like:
REM Set the environment variables
@CALL Enviro.cmd
REM The following is on one line:
DTEXEC /FILE PackageName.dtsx /De PackagePassword /SET \Package.Connections[SourceConn].ServerName;%SourceSrv% /SET \Package.Connections[SourceConn].UserName;%SourceUser% /SET \Package.Connections[SourceConn].Password;%SourcePassword% /SET \Package.Connections[SourceConn].InitialCatalog;%SourceDB% /SET \Package.Connections[HRTESTMSCOM.Careers].ServerName;%DestSrv% /SET \Package.Connections[HRTESTMSCOM.Careers].UserName;%DestUser% /SET \Package.Connections[HRTESTMSCOM.Careers].Password;%DestPassword% /SET \Package.Connections[HRTESTMSCOM.Careers].InitialCatalog;%DestDB%
Kindest Regards,JimAtWork
-
-
One last thing before I go home. If you create a new job and create a step and you set the Type = SQL Server Integration Services Package, what is the Data Sources tab suppose to do for you?
-
I think I understand what you're trying to do. What I was suggesting (and I haven't proven that this is the case) is that the package configuration is read in and processed before the command line settings, so changing the configuration connection setting on the command line may be pointless.
-
Scott,
Thank you again for the reply. I see what you are saying and I can see this could be the case. But, if the configuration setting is only affecting one connection object and the command line setting is affecting another, then you would think this works. I suppose what I could do to test this is to create another package that only has one connection object. I will have that connection object point to my local Express database. I will publish the package to the server and then create a job for it. I will use the command line setting to change the connection to my server (not my Express database) and run the package. If this works, then I will create a second connection object and setup package configuration settings based on the first connection object and have the second connection object set by configuration settings. This should reflect my current scenario that I’m trying to get to work.
Have you seen any documentation talking about the order in which configuration settings are applied?
Thanks!
-
The only thing I found related to timing was that if a property or variable was set more than once, the last setting would be in effect when the package starts. I didn't find anything under package configurations or dtexec command-line options that spelled out the order, but I am guessing that command-line settings are processed after configuration files so that a command-line setting would take precedence. Unless it is a configuration file listed in the command line, where it would be processed in the order it appears relative to other command-line settings.
-
To follow up on this thread, we ended up putting configuration settings in an XML file and then in the job we specified the XML file. In this case, things work just fine. I find it odd that things behaved the way they did with our original concept. Oh well...
Thanks again to Scott and JimAtWork for taking the time to reply to my post.
-
Lee,
You replied to my post and I'm going to now reply to yours. Several days after I originally posted, I found the problem with our situation & config files.
When you create your configuration files in BIDS, SQL Server applies the configuration file information in the order in which the config files are specified in BIDS. It does this as soon as you SAVE the SSIS package, not when you move it up to SSMS. So, any config files you have with connection information pointing to specific DBs, including login-in information, is applied at the time the package is saved.
Any configuration files specified when you set up a job to run the package, manually EXECUTE the package from the SSMS-IS connection or run the package via a command line are applied at Execution Time, but they do NOT overwrite the connections specified previously. Which really sucks if you're using a SQL table to keep the database info in, because it's already pulled the DB connections from the table... etc, etc.
Anyway, the only way we've been able to manage so far is to hard code our connections and change the info when we promote. Soon we'll be getting a middletier app server and placing SSIS on that, which should resolve this problem and it'll be back to the config files again.
-
There appear to be several misconceptions in that post.
When you create a package configuration, the current property value is written. If you later change that value in design mode, the new value is saved in the package dtsx file but it is not automatically copied to the package configuration files/tables. You have to rerun the Package Configuration editor to change the external package configuration from the package designer. If you manually change the configuration data outside of the package designer by editing the XML file or updating the tables, the new value will be in effect when the package runs but the value saved in the dtsx file hasn't changed. When you load the package again in the designer it will read the configuration data and any new (externally-modified) values will become the new design-time values.
When you run a package, configuration data from all sources is processed in the order specified in the Package Configuration Organizer, followed by command line options that are processed in order. Any of these items can specify a value that overrides previous settings, and the final value will be in effect when the package runs. This includes runtime connection properties, but changing a connection on the command line will be too late to affect the source of the already-processed SQL configuration.
If you want to keep config data in a database AND choose the source on the command line, the best way I can think of is to use an Execute SQL step to read the config data and save the result set in variables. The connection used by the Execute SQL step could get its values from variables, which could be set on the command line. If you are determined to use the Package Configuration to set all properties at once instead of adding Expressions all over the place, the Execute SQL step that loads variables based on a command-line server name could be followed by a Run Package step, where the child package gets all its configuration info from the parent package variables.
-
That's a VERY good question and nobody answered. I found that packages worked just fine with or without them... those and data source views. One of the big advantages to ssis over dts was that they would be more portable - and this was partially because the connection information is not stored in the package. We didn't find them to be quite as portable as MS said... and we SCRAPPED the project... the ssis piece, at least... did everything we had to do with Sybase replication, and an architecture that included a lot of triggers and stored procedures between our staging DB and reporting DB. Our product was delivered to our European customers, on time, and to the delight of our VP of R&D. Someday maybe I will get the chance to try SSIS again... now that there are a lot of good people working with it maybe there are ideas to be shared that aren't prejudiced by MS.
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply