DataTable and stored Procedure insertion

hi,

How can I insert a DataTable as a whole into the Database (into an existing table) using a stored procedure?

can I just send the DataTable as a parameter to the procedure?

how can I do it?

thanx

Hello,

You need to iterate through the Rows (DataRows) of that DataTable and to insert them one by one to the database.

HTH
Regads

Datareader Source connects to no longer existing ODBC-link

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=MS Access;maxbuffersize=2048;pagetimeout=5;systemdb=Z:\path\systemdb.MDW

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

Datareader Source connects to no longer existing ODBC-link

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=MS Access;maxbuffersize=2048;pagetimeout=5;systemdb=Z:\path\systemdb.MDW

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

DATABASE= or INITIAL CATALOG=?

I'm setting up a DSN for a new SQL Server install, and had some problems with
an existing Access app. Every query resulted in an "object not found", and
after a little poking about it became clear that it was because the app was
connecting to "master" instead of our database.
The connection string in Access used "Database=[our database name]". Does
this actually work? Or did the app only work in the past because the default
database for that login was set to ours?
Is there a difference between DATABASE (which I inherited) and INITIAL
CATALOG?
Yes, Database=YourDatabase does actually work.
Initial catalog is generally used when connecting with an
OLE DB provider.
Database is generally used when connecting with an ODBC
driver.
The following is a good resource for connection strings:
http://www.able-consulting.com/ADO_Conn.htm
-Sue
On Wed, 24 Nov 2004 07:37:35 -0800, Maury Markowitz
<MauryMarkowitz@.discussions.microsoft.com> wrote:

>I'm setting up a DSN for a new SQL Server install, and had some problems with
>an existing Access app. Every query resulted in an "object not found", and
>after a little poking about it became clear that it was because the app was
>connecting to "master" instead of our database.
>The connection string in Access used "Database=[our database name]". Does
>this actually work? Or did the app only work in the past because the default
>database for that login was set to ours?
>Is there a difference between DATABASE (which I inherited) and INITIAL
>CATALOG?
|||"Sue Hoegemeier" wrote:

> Yes, Database=YourDatabase does actually work.
> Initial catalog is generally used when connecting with an
> OLE DB provider.
Hmmm. That being the case can you suggest any other reason for this problem?
The application logs into the correct server and attaches to master, even
though I say database=mydatabase in the connection string.
|||Using DSNs and connections strings is kind of a waste in my
opinion and can make the issues more convoluted. I have no
idea how the DSN and connection strings are being used but I
would just get rid of the DSN. You introduce maintenance
issues as well as you have your connection string in a DSN
and in code. So I'd eliminate that and just use dsn-less
connections.
Make sure the user exists in the database and can actually
access the database.
-Sue
On Wed, 24 Nov 2004 08:37:04 -0800, Maury Markowitz
<MauryMarkowitz@.discussions.microsoft.com> wrote:

>"Sue Hoegemeier" wrote:
>
>Hmmm. That being the case can you suggest any other reason for this problem?
>The application logs into the correct server and attaches to master, even
>though I say database=mydatabase in the connection string.

DATABASE= or INITIAL CATALOG=?

I'm setting up a DSN for a new SQL Server install, and had some problems wit
h
an existing Access app. Every query resulted in an "object not found", and
after a little poking about it became clear that it was because the app was
connecting to "master" instead of our database.
The connection string in Access used "Database=[our database name]". Doe
s
this actually work? Or did the app only work in the past because the default
database for that login was set to ours?
Is there a difference between DATABASE (which I inherited) and INITIAL
CATALOG?Yes, Database=YourDatabase does actually work.
Initial catalog is generally used when connecting with an
OLE DB provider.
Database is generally used when connecting with an ODBC
driver.
The following is a good resource for connection strings:
http://www.able-consulting.com/ADO_Conn.htm
-Sue
On Wed, 24 Nov 2004 07:37:35 -0800, Maury Markowitz
<MauryMarkowitz@.discussions.microsoft.com> wrote:

>I'm setting up a DSN for a new SQL Server install, and had some problems wi
th
>an existing Access app. Every query resulted in an "object not found", and
>after a little poking about it became clear that it was because the app was
>connecting to "master" instead of our database.
>The connection string in Access used "Database=[our database name]". Do
es
>this actually work? Or did the app only work in the past because the defaul
t
>database for that login was set to ours?
>Is there a difference between DATABASE (which I inherited) and INITIAL
>CATALOG?|||"Sue Hoegemeier" wrote:

> Yes, Database=YourDatabase does actually work.
> Initial catalog is generally used when connecting with an
> OLE DB provider.
Hmmm. That being the case can you suggest any other reason for this problem?
The application logs into the correct server and attaches to master, even
though I say database=mydatabase in the connection string.|||Using DSNs and connections strings is kind of a waste in my
opinion and can make the issues more convoluted. I have no
idea how the DSN and connection strings are being used but I
would just get rid of the DSN. You introduce maintenance
issues as well as you have your connection string in a DSN
and in code. So I'd eliminate that and just use dsn-less
connections.
Make sure the user exists in the database and can actually
access the database.
-Sue
On Wed, 24 Nov 2004 08:37:04 -0800, Maury Markowitz
<MauryMarkowitz@.discussions.microsoft.com> wrote:

>"Sue Hoegemeier" wrote:
>
>Hmmm. That being the case can you suggest any other reason for this problem
?
>The application logs into the correct server and attaches to master, even
>though I say database=mydatabase in the connection string.