I am connecting to a MsAccess-database using ODBC. While developing the package we have changed mappings for this database. The ODBC was changed accordingly and the old definitions were deleted. However SSIS is still using the old ODBC-links even when deleting all existing connections and adding a new connection. Somehow the old settings have been saved and are being reused in the DataReader Source. If so where are they saved and how can I change/delete them ? Note: I suspect the Server Explorer because every time I add a data connection using the ODBC, the Datareader Source starts using the wrong definition (even when Server Explorer uses the correct one).
Hi Johndahl,
A quick question -- when you refer to "changed mappings", are you referring to the connection string used to connect to your Access database, or to column metadata (column names, data types, etc.)? If the latter, you'll want to fix (or even delete and recreate) your source adapters in your data flow components, as that is where column and table metadata are stored.
Also, slightly unrelated to this question, but I wonder if you've considered using the OLE DB Connection Manager/Source to connect to your Access database instead of ADO.Net through the ODBC bridge. The OLE DB provider for Jet should give you better performance and datatype fidelity overall.
-David
|||Hi David
Thanks for the reply.
The changed mapping refers to the use of another Access-database and a changed network drive mapping. When I setup the connection and test it, the connection tests ok. However when retrieving data (whether in Server Explorer or DataReader Source) it returns an error message: refering to the database-connection no longer in use.
Furthermore, as you suggested, I tried the use of OLEDB but here also I get an error message when trying to retrieve data: "Object reference not set to an instance of an object". Again the connection tests ok.
John
|||It would help if you could describe step-by-step how you setup your connections and source adapters.
Thanks,
Bob
|||First the ODBC was setup on the server (System DSN). Then in SSIS a connection was established using the Connection Manager. Provider is the ADO.NET for ODBC. The DSN which was setup in the ODBC Administration is chosen en tested if it can connect. Tests are always succesfull. Also setup is a connection in the Server Explorer.
For retrieving the data a datareader source was used connecting through the connection manager. It collected the data as required and the package could be developed.
However then the network drive mapping and the name of the Access-database changed (the old network drive mapping was deleted !!!). This change was executed in de ODBC Administration using the same DSN. Expected was that this change was enough for SSIS to connect to the correct, new database. However then the troubles began...
Testing the connections resulted in ok results. However upon collecting data through the datareader source we received HY024 errors stating that a connection using the OLD network drive mapping and database name could no longer be established. The question here is of course how it still knows what this old network drive mapping and database name is because the only reference to it (ODBC) is no longer available.
Afterwards we setup a new DSN in ODBC, we worked in a new package and even started a new project. In all cases the problem endured. It looks like SSIS/Visual Studio has somehow, somewhere saved the old network drive mapping and database name and is using it as default ODBC connection not using the definition of the DSN setup in ODBC Administration.
Questions for me are: is SSIS/Visual Studio saving this connection and if so where is it saved and can it be changed ?
|||Could you check the connection string assigned to your ADO.NET connection manager?
I guess the server explorer does some caching. Here is something that might help clean it out:
when you create a new connection do not choose one from the list, instead delete that one (press delete key) and create a new one by clicking on the New... button.
HTH,
Bob
|||
I simply use the settings from the ODBC Administration, thus the DSN-name. Setting up a connection with that DSN in Server Explorer translates in the following connection string:
- Dsn=dsnname;dbq=Z:\path\currentdb.mdb;driverid=25;fil
Both the dbq as the systemdb point to the correct paths and databases. Testing the connection results in an OK-status. However when accessing the tables in Server Explorer an error message is generated:
"SQL Execution Error
Executed SQL Statement: SELECT * from table
Error Source: odbcjt32.dll
Error Message: ERROR [HY024][Microsoft][ODBC Microsoft Access Driver] 'H:\path\previousdb.mdb' is not a valid path. Make sure that the correct pathname is spelled correctly and that you are connected to the server on which the file resides"
In the ODBC Administration/Server Explorer/SSIS/Visual Studio there is no mention of this last path. So where does it come from ?
|||
Probably cached in the server explorer. Try deleting them and creating new ones.
When you create new connections there is a list that shows all the connections you previously created. Delete those old connections from that list by selecting them and clicking on the Delete button.
HTH,
Bob
