September 26, 2013 at 10:47 am
Ok I have the following issue & scenario
I want a ssis package that connects to a single configuration database server,on this server is a table with all the connectionstrings for all the servers we have.
The next thing is that the connectstring found for that package has to be used for bot a second oledb source and a ole db destination (if needed).
Last week I made a package in which it worked no problem & converted into a template.
This week whenever I try and test it I get an error that it can not do that (can't really get the errormessage atm since I'm not on the machine running the packages)
I followed the explantions on the following link
http://kevine323.blogspot.be/2012/04/dynamic-connection-strings-in-ssis.html
Anyone who can tell me this possible and how I can do it
September 26, 2013 at 11:43 am
Yes, it is possible. The concept of dynamically setting the connection string is best practice.
The link you provided seems to show all the necessary steps for this to work.
Just a few questions for you to ponder:
You mention only one configuration server, how do you distinguish between different environments like test and production? Do you pad the ConnectionName in your sample?
How do you connect to your configuration server? Normally you would use package configuration, parameter or environment variable.
September 26, 2013 at 1:48 pm
Production isn't up yet for the system
Not sure what you mean with padding
I have a separate ole db that points to the config server
September 26, 2013 at 2:04 pm
Resender (9/26/2013)
Last week I made a package in which it worked no problem & converted into a template.
This week whenever I try and test it I get an error that it can not do that
Is the package executing? I'm wondering if you have an issue with the connections or with the encryption level of the package.
Resender (9/26/2013)
can't really get the errormessage atm since I'm not on the machine running the packages
Any chance you could enable logging on the package? It would help to know the actual error message.
September 26, 2013 at 2:10 pm
I could but like I said when I typed the original post I was no longer @ the workstation so I'll do that tomorrow
SO maybe a new problem maybe a hint in the direction of what I'm doing wrong.
I make my ole db connection point it to the server/database I want for the tables I need
I set in the expression @Connectstring to the variable I'm filling up with the connectionstring
I make my objects that have to do with the tables,so the 'Execute sql task' & 'ole db destination',map to the correct fields
No errors
Save the package & I get errors like
Error loading 'package name'.dtsx:The connection "{...} is not found.
When i look to the properties of the Connection manage I see it's changed to the value if the variable I'm using for my connectionstring.
Makes sense but that should point to the same place I originally pointed it at when I made the manager
September 27, 2013 at 9:08 am
You mentioned that "I set in the expression @Connectstring to the variable I'm filling up with the connectionstring"
Why don't you just set the variable value to your development connection string?
You are likely dealing with SSIS validation of your destination and source data flows. By default "DelayValidation" is set to False, i.e. SSIS is trying to validate your metadata when you open the project, but the connection stored with SSIS is wrong.
If you change DelayValidation, you can still run the package.
September 30, 2013 at 8:46 am
larskandersen (9/27/2013)
You mentioned that "I set in the expression @Connectstring to the variable I'm filling up with the connectionstring"Why don't you just set the variable value to your development connection string?
You are likely dealing with SSIS validation of your destination and source data flows. By default "DelayValidation" is set to False, i.e. SSIS is trying to validate your metadata when you open the project, but the connection stored with SSIS is wrong.
If you change DelayValidation, you can still run the package.
That's indeed the solution I'm seeing mostly...
I'm getting the errors when desgning the package,but I should think it should run.
October 2, 2013 at 2:17 am
Ok these are some of the errors I've been getting despite having followed the explanations to the letter
Error 1 Validation error. Data Flow Task: Test: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". Test.dtsx 0 0
Error 2 Validation error. Data Flow Task: Data Flow Task: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "..." failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. Test.dtsx 0 0
Error 3 Validation error. Test Connection manager "...": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". Test.dtsx 0 0
Error 4 Validation error. Data Flow Task OLE DB Source [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "..." failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. Test.dtsx 0 0
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply