Integration Services - Dynamically build connection objects for Microsoft Access data
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.VsIntegratio n)
Can someone help me, please
|