March 15, 2010 at 6:26 am
Hello, I need to create a dynammic build connection objects for Microsoft Access databases.
I´m using ssis 2005 and, i am reading a table in sql server 2005 that contains the path for the mdb file.
I already created a variable with that sql query, and that works, but when i´m using it on the expression of the connection ("Data Source=" + @[User::path] + ";Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Password=;") i get an error when i´m running the package: TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Data Flow Task 1 [OLE DB Source [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ACERTO_OBJ_2010NEW" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error at Data Flow Task 1 [DTS.Pipeline]: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
Error at Data Flow Task 1 [DTS.Pipeline]: One or more component failed validation.
Error at Data Flow Task 1: There were errors during task validation.
Error at importa_acertos_obj [Connection manager "ACERTO_OBJ_2010NEW"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.
.
I try to put in the misc\connectionString Data Source=@[User::path];User ID=Admin;Provider=Microsoft.Jet.OLEDB.4.0;
and in the ServerName: @[User::path] but the error was:
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Data Flow Task 1 [OLE DB Source [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ACERTO_OBJ_2010NEW" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error at Data Flow Task 1 [DTS.Pipeline]: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
Error at Data Flow Task 1 [DTS.Pipeline]: One or more component failed validation.
Error at Data Flow Task 1: There were errors during task validation.
Error at importa_acertos_obj [Connection manager "ACERTO_OBJ_2010NEW"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Could not find file 'C:\Documents and Settings\TT1143\My Documents\Visual Studio 2005\Projects\importa_acertos_obj\@[User::path]'.".
(Microsoft.DataTransformationServices.VsIntegration)
Can someone help me, please
March 15, 2010 at 7:52 am
If I understand what you have done..
Here is what I think needs to be done.
1. Make sure the variable contains a value at design-time.
2. Set the connection to point at a known file.
3. Set Delay validation on the connection to True.
4. Set the expression using the variable.
It think you have done some of these steps but I'm not sure exactly which..
CEWII
March 15, 2010 at 8:21 am
I made this:
1. I have two variables - see attachment 1
2. Then my first step is a sql task that fill the variable with the path that i have in sql table (and it works, because i see it fill in in debug mode)- see aattachment 2
3. Then i have a data flow to transfer the data, but you can see in attachment 3 the connection mannager of the access database and it
properties with the expression fill in (when i fill the expression , i lost the connectionstring in misc properties ).
But i get this error:
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Data Flow Task 1 [OLE DB Source [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ACERTO_OBJ_2010NEW" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error at Data Flow Task 1 [DTS.Pipeline]: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
Error at Data Flow Task 1 [DTS.Pipeline]: One or more component failed validation.
Error at Data Flow Task 1: There were errors during task validation.
Error at importa_acertos_obj [Connection manager "ACERTO_OBJ_2010NEW"]: 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".
(Microsoft.DataTransformationServices.VsIntegration)
------------------------------
BUTTONS:
OK
------------------------------
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply