Import script

Hello All,
I need import some data from one db in one server to another db in another
server. I can do it by import/export wizard, but I need do that by script
for deployment time. Do you know how to do that?
Thanks,
HUwhich database server are you using? 2000 or 2005|||2000
"Ajit - The Scorpio" <ajitscorpio@.gmail.com> wrote in message
news:1176215374.790446.12340@.p77g2000hsh.googlegroups.com...
> which database server are you using? 2000 or 2005
>|||Hello,
Vysa's have some good utility procedures to generate insert statements, its
all pretty handy.
http://vyaskn.tripod.com/code.htm#inserts
Thanks
Hari
"italic" <hugur@.hotmail.com> wrote in message
news:%23eFv013eHHA.4136@.TK2MSFTNGP02.phx.gbl...
> 2000
> "Ajit - The Scorpio" <ajitscorpio@.gmail.com> wrote in message
> news:1176215374.790446.12340@.p77g2000hsh.googlegroups.com...
>

Import script

Hello All,
I need import some data from one db in one server to another db in another
server. I can do it by import/export wizard, but I need do that by script
for deployment time. Do you know how to do that?
Thanks,
HUwhich database server are you using? 2000 or 2005|||2000
"Ajit - The Scorpio" <ajitscorpio@.gmail.com> wrote in message
news:1176215374.790446.12340@.p77g2000hsh.googlegroups.com...
> which database server are you using? 2000 or 2005
>|||Hello,
Vysa's have some good utility procedures to generate insert statements, its
all pretty handy.
http://vyaskn.tripod.com/code.htm#inserts
Thanks
Hari
"italic" <hugur@.hotmail.com> wrote in message
news:%23eFv013eHHA.4136@.TK2MSFTNGP02.phx.gbl...
> 2000
> "Ajit - The Scorpio" <ajitscorpio@.gmail.com> wrote in message
> news:1176215374.790446.12340@.p77g2000hsh.googlegroups.com...
>> which database server are you using? 2000 or 2005
>sql

import ODBC data to SQL Server 2005

I can't find the ODBC selection in the Data source list of the Import Wizard, so how can I import data from an ODBC data source to SQL Server 2005?

have you found a solution to this yet? if so, please share. thank you.sql

import ODBC data to SQL Server 2005

I can't find the ODBC selection in the Data source list of the Import Wizard, so how can I import data from an ODBC data source to SQL Server 2005?

have you found a solution to this yet? if so, please share. thank you.

import multiple text files?

Hello,

I am kind of new to Sql Server 2005.

I figured out how to use the import data wizard to import a delimited text file.

But I need to find a way to import many delimited text files at once.

does anybody know if this can be done in Sql Server 2005? and how?
thanks in advance,Hi,

you should probably use integration services for that. SSIS has a special task for that which will loop though a directory and inmport all text files which fit into the filter specified before.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Import from INFORMIX (Cannot get the supported data types)

Hi!

I'm trying to import data from an Informix Database to my SQL Server 2005 database with the import/export wizard.
I have an ODBC-connection that works, Access fetches data with no errors. From the wizard I can only choose to use the IBM Informix OLE DB Provider as datasource. As fare as I know the OLE DB Provider is using settings from the ODBC-driver?

In SQL Server 2000 there is a posibillity to choose Other(ODBC provider) as datasource which works against this database. Any ideas how i can use an ODBC-datasource?
If I try to use the IBM Informix OLE DB Provider I get an error message after the first step of the wizard:

Cannot get the supported data types from the database connection "Provider=Ifxoledbc;Password=;Persist Security Info=True;User ID=uid;Data Source=db@.server".

ADDITIONAL INFORMATION:

IErrorInfo.GetDescription failed with E_NOINTERFACE(0x80004002).
IErrorInfo.GetDescription failed with E_NOINTERFACE(0x80004002). (System.Data)

Anyone have any experience with this problem? I can't find any information that will solve this, but I have seen several questions about this.

I would be very grateful for some help!

Best Regards

Hello

Did you ever find the solution to this problem? I'm having similar errors and don't know what if wrong.

Thanks

|||Hi!

I could not find any solution. I had to uninstall SQL server 2005 and use SQL server 2000.

I think the problem has something with the SQL 2005 server's ODBC support. I read in a forum that Microsoft spent their time on OLE-DB, and that the OLE-DB driver from Informix has some problems. But i gave up trying to figure it out.

I'm sorry!

Trond U|||

Thanks for the information. That really bites. This is a huge disappointment. I guess I will have to find a machine with SQL Server 2000 installed on it. I don't have the option of uninstalling my SQL Server 2005.

Thank you for getting back to me,

Matt

Import from INFORMIX (Cannot get the supported data types)

Hi!

I'm trying to import data from an Informix Database to my SQL Server 2005 database with the import/export wizard.
I have an ODBC-connection that works, Access fetches data with no errors. From the wizard I can only choose to use the IBM Informix OLE DB Provider as datasource. As fare as I know the OLE DB Provider is using settings from the ODBC-driver?

In SQL Server 2000 there is a posibillity to choose Other(ODBC provider) as datasource which works against this database. Any ideas how i can use an ODBC-datasource?
If I try to use the IBM Informix OLE DB Provider I get an error message after the first step of the wizard:

Cannot get the supported data types from the database connection "Provider=Ifxoledbc;Password=;Persist Security Info=True;User ID=uid;Data Source=db@.server".

ADDITIONAL INFORMATION:

IErrorInfo.GetDescription failed with E_NOINTERFACE(0x80004002).
IErrorInfo.GetDescription failed with E_NOINTERFACE(0x80004002). (System.Data)

Anyone have any experience with this problem? I can't find any information that will solve this, but I have seen several questions about this.

I would be very grateful for some help!

Best Regards

Hello

Did you ever find the solution to this problem? I'm having similar errors and don't know what if wrong.

Thanks

|||Hi!

I could not find any solution. I had to uninstall SQL server 2005 and use SQL server 2000.

I think the problem has something with the SQL 2005 server's ODBC support. I read in a forum that Microsoft spent their time on OLE-DB, and that the OLE-DB driver from Informix has some problems. But i gave up trying to figure it out.

I'm sorry!

Trond U|||

Thanks for the information. That really bites. This is a huge disappointment. I guess I will have to find a machine with SQL Server 2000 installed on it. I don't have the option of uninstalling my SQL Server 2005.

Thank you for getting back to me,

Matt

import from Excel error

While attempting to import data from Excel using SSMS and the import/export wizard, I received the following error:

TITLE: SQL Server Import and Export Wizard
An error occurred which the SQL Server Integration Services Wizard was not prepared to handle.

ADDITIONAL INFORMATION:

Exception has been thrown by the target of an invocation. (mscorlib)

The connection type "EXCEL" specified for connection manager "{11CD789E-0DCD-48C8-81F9-1065D87B5ADF}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
({BB3EBEA7-7F0E-4346-A8A5-60E176732365})

Any clues as what causes this and how to resolve the problem. I am using Office XP.

Do you have Microsoft Jet OLE DB provider installed? It should be there by default. Check whether you are missing msjetoledb40.dll, or whether it is correctly registered may help.

HTH

wenyang

|||The dll is installed and I ran regsvr32; it registered successfully. However, the problem still exists.

Import from Data from Microsoft Access

In SQL Server 2005, Im trying to append data to tables which already exist. Im importing the data through the import wizard.

The source from is Microsoft Access with no username and password.

The source to is SQL Server 2005 using OLE DB Provider SQL Server with the login information of the schema I wish to use.

I click through and the tables appear in the source. When I select all, they appear in the destination but they appear with the dbo. prefix which would regard them as new tables since the tables dont exist under that schema. I can click on the first destination table drop down text box and see all the tables under the schema there suppose to be under but its not the default. There are a lot of tables and I don't feel like using the drop down text box hundreds of times. Is there a solution to this problem?

It worked in Sql Server 200

Thanks

Scott

use dts or ssis|||

But why is it defaulting to dbo. when the table doesnt even exist and I can dropdown and see the proper table. Im even connected as the user I want to the destination database and the user is a db_owner. Creating a package wont work because we;re constantly adding tables and DTS may work but the preferred method is just to be able to import data into the proper schema

|||

dbo is the default schema.

how about qualifying the destination table with shcemaname.tablename in

the import process

|||

But if Im loggin in as Another user I would figure it would default to that user. When you say qualify in the import process do you mean change the [dbo]. to [proper schema owner].

I tried to create a package and then took the file and cut and paste dbo with proper name however since the table never existed its trying to create the table and it already exists so I get an error when I run it. When I use the drop down text box and change the table to the proper table with the proper owner it changes the option to append which is correct.

Im kinda at a loss as I feel there is nothing I Can do but hit the drop down text box for 200+ tables every time.

What I really need is a solution in the import/export wizard to show up with the destination tables as the proper schema owner?

Scott

|||

For my sake and everyone else's, Im not crazy. In SQL Server 2005 SP1 Microsoft has fixed this issue and now allows you to choose a destination schema. woooohoooo!!!!

However ... I am now getting the following error on appending data. The table structure exists and Im trying to append all data from Access tables into SQL Server tables. Not all Access tables have data. If I do one individual table it works. If I do 200 I get the following error:

- Prepare for Execute (Error)

Messages

Error 0xc0202009: {8DD4F4CE-2DD7-4856-A251-71D4206EC6DC}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
(SQL Server Import and Export Wizard)

Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.
(SQL Server Import and Export Wizard)

Error 0xc004701a: Data Flow Task: component "Source 64 - DP_ROUTE_JURISDICTION" (6998)failed the pre-execute phase and returned error code 0xC020801C.
(SQL Server Import and Export Wizard)

Does anyone have a solution or can point me in the right direction. Does it have something to do with the Access buffer size? Ive see some posts for this error but no solid solutions. Any help would be greatly appreciated.

Thanks

Scott

|||

Can you publish the Access database anywhere so that we can reproduce the problem?

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

Import from Data from Microsoft Access

In SQL Server 2005, Im trying to append data to tables which already exist. Im importing the data through the import wizard.

The source from is Microsoft Access with no username and password.

The source to is SQL Server 2005 using OLE DB Provider SQL Server with the login information of the schema I wish to use.

I click through and the tables appear in the source. When I select all, they appear in the destination but they appear with the dbo. prefix which would regard them as new tables since the tables dont exist under that schema. I can click on the first destination table drop down text box and see all the tables under the schema there suppose to be under but its not the default. There are a lot of tables and I don't feel like using the drop down text box hundreds of times. Is there a solution to this problem?

It worked in Sql Server 200

Thanks

Scott

use dts or ssis|||

But why is it defaulting to dbo. when the table doesnt even exist and I can dropdown and see the proper table. Im even connected as the user I want to the destination database and the user is a db_owner. Creating a package wont work because we;re constantly adding tables and DTS may work but the preferred method is just to be able to import data into the proper schema

|||

dbo is the default schema.

how about qualifying the destination table with shcemaname.tablename in

the import process

|||

But if Im loggin in as Another user I would figure it would default to that user. When you say qualify in the import process do you mean change the [dbo]. to [proper schema owner].

I tried to create a package and then took the file and cut and paste dbo with proper name however since the table never existed its trying to create the table and it already exists so I get an error when I run it. When I use the drop down text box and change the table to the proper table with the proper owner it changes the option to append which is correct.

Im kinda at a loss as I feel there is nothing I Can do but hit the drop down text box for 200+ tables every time.

What I really need is a solution in the import/export wizard to show up with the destination tables as the proper schema owner?

Scott

|||

For my sake and everyone else's, Im not crazy. In SQL Server 2005 SP1 Microsoft has fixed this issue and now allows you to choose a destination schema. woooohoooo!!!!

However ... I am now getting the following error on appending data. The table structure exists and Im trying to append all data from Access tables into SQL Server tables. Not all Access tables have data. If I do one individual table it works. If I do 200 I get the following error:

- Prepare for Execute (Error)

Messages

Error 0xc0202009: {8DD4F4CE-2DD7-4856-A251-71D4206EC6DC}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
(SQL Server Import and Export Wizard)

Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.
(SQL Server Import and Export Wizard)

Error 0xc004701a: Data Flow Task: component "Source 64 - DP_ROUTE_JURISDICTION" (6998)failed the pre-execute phase and returned error code 0xC020801C.
(SQL Server Import and Export Wizard)

Does anyone have a solution or can point me in the right direction. Does it have something to do with the Access buffer size? Ive see some posts for this error but no solid solutions. Any help would be greatly appreciated.

Thanks

Scott

|||

Can you publish the Access database anywhere so that we can reproduce the problem?

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

Import from a file does not load data

I am using sql server 2000 on my Desktop (Windows 2000). I exported a
table to a .txt file, When I imported table using DTS wizard. I do
not get any errors. Yet, no data is loaded in the table. How can I
troubleshoot the problem. Which logs I can look into to find the
problem.
I am new to SQL SERVER, I am an Oracle DBA trying to learn SQL SERVER
2000.Hi
I assume you are using the import wizard in which case it should have shown
a preview of the data. If you chose the run immediately option it should have
shown the number if records inserted once the copy data step has competed.
How many rows are you expecting from the file?
Are you loading this into a new table?
If the import summary showed no rows but you know the file has data then you
may want to change the deliminators you have specified!
John
"zigzagdna@.yahoo.com" wrote:
> I am using sql server 2000 on my Desktop (Windows 2000). I exported a
> table to a .txt file, When I imported table using DTS wizard. I do
> not get any errors. Yet, no data is loaded in the table. How can I
> troubleshoot the problem. Which logs I can look into to find the
> problem.
> I am new to SQL SERVER, I am an Oracle DBA trying to learn SQL SERVER
> 2000.
>|||Import summary showed 2 rows were loaded, yet nothing was loaded.
When I did export from the wizard, it put 2 rows in the file, no create
table staement. My table employee alerady exists in the table, it has no
rows before import. I was hpoing it will have 2 rows after import, but it did
not. Is there anyway to turn on some tracing to see where the import had
problems.
"John Bell" wrote:
> Hi
> I assume you are using the import wizard in which case it should have shown
> a preview of the data. If you chose the run immediately option it should have
> shown the number if records inserted once the copy data step has competed.
> How many rows are you expecting from the file?
> Are you loading this into a new table?
> If the import summary showed no rows but you know the file has data then you
> may want to change the deliminators you have specified!
> John
>
> "zigzagdna@.yahoo.com" wrote:
> > I am using sql server 2000 on my Desktop (Windows 2000). I exported a
> > table to a .txt file, When I imported table using DTS wizard. I do
> > not get any errors. Yet, no data is loaded in the table. How can I
> > troubleshoot the problem. Which logs I can look into to find the
> > problem.
> >
> > I am new to SQL SERVER, I am an Oracle DBA trying to learn SQL SERVER
> > 2000.
> >
> >|||Hi
When you export using the Export wizard it will only create a data file, you
can script the table creation script through Enterprise Manager or Query
Analyser.
Have you tables under a different schema with the same name?
If you create a DTS package from the Import/Export Wizard you can then
modify the package and use the DTS to log information and handle errors.
John
"Prem Mehrotra" wrote:
> Import summary showed 2 rows were loaded, yet nothing was loaded.
> When I did export from the wizard, it put 2 rows in the file, no create
> table staement. My table employee alerady exists in the table, it has no
> rows before import. I was hpoing it will have 2 rows after import, but it did
> not. Is there anyway to turn on some tracing to see where the import had
> problems.
> "John Bell" wrote:
> > Hi
> >
> > I assume you are using the import wizard in which case it should have shown
> > a preview of the data. If you chose the run immediately option it should have
> > shown the number if records inserted once the copy data step has competed.
> > How many rows are you expecting from the file?
> > Are you loading this into a new table?
> > If the import summary showed no rows but you know the file has data then you
> > may want to change the deliminators you have specified!
> >
> > John
> >
> >
> >
> > "zigzagdna@.yahoo.com" wrote:
> >
> > > I am using sql server 2000 on my Desktop (Windows 2000). I exported a
> > > table to a .txt file, When I imported table using DTS wizard. I do
> > > not get any errors. Yet, no data is loaded in the table. How can I
> > > troubleshoot the problem. Which logs I can look into to find the
> > > problem.
> > >
> > > I am new to SQL SERVER, I am an Oracle DBA trying to learn SQL SERVER
> > > 2000.
> > >
> > >|||I finally figured the problem. Name of the export file has to match with
table being exported/imported. So when importing, it created a new table and
was putting rows
there. Also while in Enterpirse Manager, I did not see these tables
until today.
It seems to see new tables screated by import, one must disconnect from
database and reconnect to it, that's why I had no idea what was going on.
Thanks a lot for all your help.
"John Bell" wrote:
> Hi
> When you export using the Export wizard it will only create a data file, you
> can script the table creation script through Enterprise Manager or Query
> Analyser.
> Have you tables under a different schema with the same name?
> If you create a DTS package from the Import/Export Wizard you can then
> modify the package and use the DTS to log information and handle errors.
> John
> "Prem Mehrotra" wrote:
> > Import summary showed 2 rows were loaded, yet nothing was loaded.
> > When I did export from the wizard, it put 2 rows in the file, no create
> > table staement. My table employee alerady exists in the table, it has no
> > rows before import. I was hpoing it will have 2 rows after import, but it did
> > not. Is there anyway to turn on some tracing to see where the import had
> > problems.
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > I assume you are using the import wizard in which case it should have shown
> > > a preview of the data. If you chose the run immediately option it should have
> > > shown the number if records inserted once the copy data step has competed.
> > > How many rows are you expecting from the file?
> > > Are you loading this into a new table?
> > > If the import summary showed no rows but you know the file has data then you
> > > may want to change the deliminators you have specified!
> > >
> > > John
> > >
> > >
> > >
> > > "zigzagdna@.yahoo.com" wrote:
> > >
> > > > I am using sql server 2000 on my Desktop (Windows 2000). I exported a
> > > > table to a .txt file, When I imported table using DTS wizard. I do
> > > > not get any errors. Yet, no data is loaded in the table. How can I
> > > > troubleshoot the problem. Which logs I can look into to find the
> > > > problem.
> > > >
> > > > I am new to SQL SERVER, I am an Oracle DBA trying to learn SQL SERVER
> > > > 2000.
> > > >
> > > >|||Hi
The wizard will default the name of the table, but you do have the option to
change it.
John
"Prem Mehrotra" wrote:
> I finally figured the problem. Name of the export file has to match with
> table being exported/imported. So when importing, it created a new table and
> was putting rows
> there. Also while in Enterpirse Manager, I did not see these tables
> until today.
> It seems to see new tables screated by import, one must disconnect from
> database and reconnect to it, that's why I had no idea what was going on.
> Thanks a lot for all your help.
> "John Bell" wrote:
> > Hi
> >
> > When you export using the Export wizard it will only create a data file, you
> > can script the table creation script through Enterprise Manager or Query
> > Analyser.
> >
> > Have you tables under a different schema with the same name?
> >
> > If you create a DTS package from the Import/Export Wizard you can then
> > modify the package and use the DTS to log information and handle errors.
> >
> > John
> > "Prem Mehrotra" wrote:
> >
> > > Import summary showed 2 rows were loaded, yet nothing was loaded.
> > > When I did export from the wizard, it put 2 rows in the file, no create
> > > table staement. My table employee alerady exists in the table, it has no
> > > rows before import. I was hpoing it will have 2 rows after import, but it did
> > > not. Is there anyway to turn on some tracing to see where the import had
> > > problems.
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > I assume you are using the import wizard in which case it should have shown
> > > > a preview of the data. If you chose the run immediately option it should have
> > > > shown the number if records inserted once the copy data step has competed.
> > > > How many rows are you expecting from the file?
> > > > Are you loading this into a new table?
> > > > If the import summary showed no rows but you know the file has data then you
> > > > may want to change the deliminators you have specified!
> > > >
> > > > John
> > > >
> > > >
> > > >
> > > > "zigzagdna@.yahoo.com" wrote:
> > > >
> > > > > I am using sql server 2000 on my Desktop (Windows 2000). I exported a
> > > > > table to a .txt file, When I imported table using DTS wizard. I do
> > > > > not get any errors. Yet, no data is loaded in the table. How can I
> > > > > troubleshoot the problem. Which logs I can look into to find the
> > > > > problem.
> > > > >
> > > > > I am new to SQL SERVER, I am an Oracle DBA trying to learn SQL SERVER
> > > > > 2000.
> > > > >
> > > > >|||THe import wizard allows you to specify the name of the table being
created, or to specify an existing table. For an existing table you
can specify if the table is to be truncated first, or the new data
appended to the old. When you get to the window in the wizard with
three columns - Source, Destination, Transform - you can click on
Desination to make changes to the name or select an existing table
from the list. Click on Transform to specify append or replace, and
other options such as controlling the names and data types of the
columns in the table being created.
There is more to the import wizard, and I suggest it is worth your
time playing with it for a while and exploring the options so you are
familiar with what is there. The most important feature not mentioned
so far is that you can save it as a DTS package and then edit it in
the DTS interface - good for minor tweaking.
Roy Harvey
Beacon Falls, CT
On Mon, 20 Aug 2007 18:48:02 -0700, Prem Mehrotra
<PremMehrotra@.discussions.microsoft.com> wrote:
>I finally figured the problem. Name of the export file has to match with
>table being exported/imported. So when importing, it created a new table and
>was putting rows
>there. Also while in Enterpirse Manager, I did not see these tables
>until today.
>It seems to see new tables screated by import, one must disconnect from
>database and reconnect to it, that's why I had no idea what was going on.
>Thanks a lot for all your help.
>"John Bell" wrote:
>> Hi
>> When you export using the Export wizard it will only create a data file, you
>> can script the table creation script through Enterprise Manager or Query
>> Analyser.
>> Have you tables under a different schema with the same name?
>> If you create a DTS package from the Import/Export Wizard you can then
>> modify the package and use the DTS to log information and handle errors.
>> John
>> "Prem Mehrotra" wrote:
>> > Import summary showed 2 rows were loaded, yet nothing was loaded.
>> > When I did export from the wizard, it put 2 rows in the file, no create
>> > table staement. My table employee alerady exists in the table, it has no
>> > rows before import. I was hpoing it will have 2 rows after import, but it did
>> > not. Is there anyway to turn on some tracing to see where the import had
>> > problems.
>> >
>> > "John Bell" wrote:
>> >
>> > > Hi
>> > >
>> > > I assume you are using the import wizard in which case it should have shown
>> > > a preview of the data. If you chose the run immediately option it should have
>> > > shown the number if records inserted once the copy data step has competed.
>> > > How many rows are you expecting from the file?
>> > > Are you loading this into a new table?
>> > > If the import summary showed no rows but you know the file has data then you
>> > > may want to change the deliminators you have specified!
>> > >
>> > > John
>> > >
>> > >
>> > >
>> > > "zigzagdna@.yahoo.com" wrote:
>> > >
>> > > > I am using sql server 2000 on my Desktop (Windows 2000). I exported a
>> > > > table to a .txt file, When I imported table using DTS wizard. I do
>> > > > not get any errors. Yet, no data is loaded in the table. How can I
>> > > > troubleshoot the problem. Which logs I can look into to find the
>> > > > problem.
>> > > >
>> > > > I am new to SQL SERVER, I am an Oracle DBA trying to learn SQL SERVER
>> > > > 2000.
>> > > >
>> > > >|||I looked at DTS Wizard. Is there any way to export all the
tables/views/packages etc using one "command". I find DTS is table based, so
it only exports schema of a table and its data. How ablout views? I want to
export all the tables at the same time and then selectively import. Oracle
lets you do that. I am sure sql server also allows that, but how?
"Roy Harvey" wrote:
> THe import wizard allows you to specify the name of the table being
> created, or to specify an existing table. For an existing table you
> can specify if the table is to be truncated first, or the new data
> appended to the old. When you get to the window in the wizard with
> three columns - Source, Destination, Transform - you can click on
> Desination to make changes to the name or select an existing table
> from the list. Click on Transform to specify append or replace, and
> other options such as controlling the names and data types of the
> columns in the table being created.
> There is more to the import wizard, and I suggest it is worth your
> time playing with it for a while and exploring the options so you are
> familiar with what is there. The most important feature not mentioned
> so far is that you can save it as a DTS package and then edit it in
> the DTS interface - good for minor tweaking.
> Roy Harvey
> Beacon Falls, CT
> On Mon, 20 Aug 2007 18:48:02 -0700, Prem Mehrotra
> <PremMehrotra@.discussions.microsoft.com> wrote:
> >I finally figured the problem. Name of the export file has to match with
> >table being exported/imported. So when importing, it created a new table and
> >was putting rows
> >there. Also while in Enterpirse Manager, I did not see these tables
> >until today.
> >
> >It seems to see new tables screated by import, one must disconnect from
> >database and reconnect to it, that's why I had no idea what was going on.
> >
> >Thanks a lot for all your help.
> >
> >"John Bell" wrote:
> >
> >> Hi
> >>
> >> When you export using the Export wizard it will only create a data file, you
> >> can script the table creation script through Enterprise Manager or Query
> >> Analyser.
> >>
> >> Have you tables under a different schema with the same name?
> >>
> >> If you create a DTS package from the Import/Export Wizard you can then
> >> modify the package and use the DTS to log information and handle errors.
> >>
> >> John
> >> "Prem Mehrotra" wrote:
> >>
> >> > Import summary showed 2 rows were loaded, yet nothing was loaded.
> >> > When I did export from the wizard, it put 2 rows in the file, no create
> >> > table staement. My table employee alerady exists in the table, it has no
> >> > rows before import. I was hpoing it will have 2 rows after import, but it did
> >> > not. Is there anyway to turn on some tracing to see where the import had
> >> > problems.
> >> >
> >> > "John Bell" wrote:
> >> >
> >> > > Hi
> >> > >
> >> > > I assume you are using the import wizard in which case it should have shown
> >> > > a preview of the data. If you chose the run immediately option it should have
> >> > > shown the number if records inserted once the copy data step has competed.
> >> > > How many rows are you expecting from the file?
> >> > > Are you loading this into a new table?
> >> > > If the import summary showed no rows but you know the file has data then you
> >> > > may want to change the deliminators you have specified!
> >> > >
> >> > > John
> >> > >
> >> > >
> >> > >
> >> > > "zigzagdna@.yahoo.com" wrote:
> >> > >
> >> > > > I am using sql server 2000 on my Desktop (Windows 2000). I exported a
> >> > > > table to a .txt file, When I imported table using DTS wizard. I do
> >> > > > not get any errors. Yet, no data is loaded in the table. How can I
> >> > > > troubleshoot the problem. Which logs I can look into to find the
> >> > > > problem.
> >> > > >
> >> > > > I am new to SQL SERVER, I am an Oracle DBA trying to learn SQL SERVER
> >> > > > 2000.
> >> > > >
> >> > > >
>|||You need to use the "Transfer Objects and Data between SQL Server Databases" DTS task. This exports
the schemas information to a set of text files, exports the data to files, created the objects at
the other end and then imports the data.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Prem Mehrotra" <PremMehrotra@.discussions.microsoft.com> wrote in message
news:C1314416-D22B-4C62-8AEE-49935AED8A01@.microsoft.com...
>I looked at DTS Wizard. Is there any way to export all the
> tables/views/packages etc using one "command". I find DTS is table based, so
> it only exports schema of a table and its data. How ablout views? I want to
> export all the tables at the same time and then selectively import. Oracle
> lets you do that. I am sure sql server also allows that, but how?
> "Roy Harvey" wrote:
>> THe import wizard allows you to specify the name of the table being
>> created, or to specify an existing table. For an existing table you
>> can specify if the table is to be truncated first, or the new data
>> appended to the old. When you get to the window in the wizard with
>> three columns - Source, Destination, Transform - you can click on
>> Desination to make changes to the name or select an existing table
>> from the list. Click on Transform to specify append or replace, and
>> other options such as controlling the names and data types of the
>> columns in the table being created.
>> There is more to the import wizard, and I suggest it is worth your
>> time playing with it for a while and exploring the options so you are
>> familiar with what is there. The most important feature not mentioned
>> so far is that you can save it as a DTS package and then edit it in
>> the DTS interface - good for minor tweaking.
>> Roy Harvey
>> Beacon Falls, CT
>> On Mon, 20 Aug 2007 18:48:02 -0700, Prem Mehrotra
>> <PremMehrotra@.discussions.microsoft.com> wrote:
>> >I finally figured the problem. Name of the export file has to match with
>> >table being exported/imported. So when importing, it created a new table and
>> >was putting rows
>> >there. Also while in Enterpirse Manager, I did not see these tables
>> >until today.
>> >
>> >It seems to see new tables screated by import, one must disconnect from
>> >database and reconnect to it, that's why I had no idea what was going on.
>> >
>> >Thanks a lot for all your help.
>> >
>> >"John Bell" wrote:
>> >
>> >> Hi
>> >>
>> >> When you export using the Export wizard it will only create a data file, you
>> >> can script the table creation script through Enterprise Manager or Query
>> >> Analyser.
>> >>
>> >> Have you tables under a different schema with the same name?
>> >>
>> >> If you create a DTS package from the Import/Export Wizard you can then
>> >> modify the package and use the DTS to log information and handle errors.
>> >>
>> >> John
>> >> "Prem Mehrotra" wrote:
>> >>
>> >> > Import summary showed 2 rows were loaded, yet nothing was loaded.
>> >> > When I did export from the wizard, it put 2 rows in the file, no create
>> >> > table staement. My table employee alerady exists in the table, it has no
>> >> > rows before import. I was hpoing it will have 2 rows after import, but it did
>> >> > not. Is there anyway to turn on some tracing to see where the import had
>> >> > problems.
>> >> >
>> >> > "John Bell" wrote:
>> >> >
>> >> > > Hi
>> >> > >
>> >> > > I assume you are using the import wizard in which case it should have shown
>> >> > > a preview of the data. If you chose the run immediately option it should have
>> >> > > shown the number if records inserted once the copy data step has competed.
>> >> > > How many rows are you expecting from the file?
>> >> > > Are you loading this into a new table?
>> >> > > If the import summary showed no rows but you know the file has data then you
>> >> > > may want to change the deliminators you have specified!
>> >> > >
>> >> > > John
>> >> > >
>> >> > >
>> >> > >
>> >> > > "zigzagdna@.yahoo.com" wrote:
>> >> > >
>> >> > > > I am using sql server 2000 on my Desktop (Windows 2000). I exported a
>> >> > > > table to a .txt file, When I imported table using DTS wizard. I do
>> >> > > > not get any errors. Yet, no data is loaded in the table. How can I
>> >> > > > troubleshoot the problem. Which logs I can look into to find the
>> >> > > > problem.
>> >> > > >
>> >> > > > I am new to SQL SERVER, I am an Oracle DBA trying to learn SQL SERVER
>> >> > > > 2000.
>> >> > > >
>> >> > > >

Import Export Wizard and Unicode columns

Hello Folks,

This is my first real exposure to using SSIS's Import Export Tool.

I am trying to create a package to move the data from a number of tables in SQL Server into a duplicate database in Oracle. I am invoking the Import/Export WIzard from within the Management Studio. I am using the SQL Server native client on the SQL Server side and the Microsoft OLE DB driver for Oracle for the Oracle database. Both of my tables have unicode data types. On the SQL Server side I have columns defined as NVARCHAR and on Oracle the same column is defined as NVARCHAR2.

When I initially select the table for export the wizard assumes that I want to create a new table. The new table has the correct column name, column order and data types (NVARCHAR2). I cannot tell it to append the data. If I add the table owner to the detination table name or use the GUI to pick it with the table owner...the data types for the NVARCHAR2 columns go away. I cannot edit the data types at this point and if I continue, SSIS barks at me that it doesn't know the data types of those columns. The problem also occurs if you use the wizard from inside of the BIDS. It seems to be OK with the data type unless the table pre-exists. This seems un-useful to me.

Is there a way to avoid this in the Import/Export Wizard? Am I doing something wrong?

Any help would be appreciated.

Thanks, Mark

Hi Mark,

could you double check the metadata of your preexisting destination table matches the incoming metadata? Also, could you post the error the wizard reports?

Thanks,

Bob

|||Hi Bob,

Well, as I mentioned before if I try to continue SSIS barks and says:

TITLE: SQL Server Import and Export Wizard


Column information for the source and the destination data could not be retrieved, or the data types of source columns were not mapped correctly to those available on the destination provider.


[sql_dev_slove2].[dbo].[ACCESSPROFILE] -> "MARKT"."ACCESSPROFILE":

- The data type could not be assigned to the column "PROFILENM" in "Microsoft OLE DB Provider for Oracle".
- The data type could not be assigned to the column "PROFILEDSC" in "Microsoft OLE DB Provider for Oracle".

After this point, I can go no farther so (I think) I cannot see the actual meta data. If I do not change the owner (let SSIS think it needs to create the table) the input data type is DT_WSTR. The tables are defined as follows with the PROFILENM and PROFILEDSC columns being the troublesome ones:

SQL Server:
===========

CREATE TABLE ACCESSPROFILE
(
ACCESSPROFID INTEGER NOT NULL ,
PROFILENM NVARCHAR(50) NOT NULL ,
PROFILEDSC NVARCHAR(250) NULL ,
DEFAULTSW INTEGER DEFAULT 0 NULL ,
UPDATEDBYUSRACCTID INTEGER NOT NULL ,
UPDATEDTM DATETIME DEFAULT GETDATE() NOT NULL ,
VERSIONCNT INTEGER DEFAULT 1 NOT NULL ,
ALLOWALLSW INTEGER DEFAULT 0 NOT NULL
)
;

Oracle:
=======

CREATE TABLE ACCESSPROFILE
(
ACCESSPROFID NUMBER(10) NOT NULL ,
PROFILENM NVARCHAR2(50) NOT NULL ,
PROFILEDSC NVARCHAR2(250) NULL ,
DEFAULTSW NUMBER(10) DEFAULT 0 NULL ,
UPDATEDBYUSRACCTID NUMBER(10) NOT NULL ,
UPDATEDTM DATE DEFAULT SYSDATE NOT NULL ,
VERSIONCNT NUMBER(10) DEFAULT 1 NOT NULL ,
ALLOWALLSW NUMBER(10) DEFAULT 0 NOT NULL
)
/

Does this help?|||

Mark,

could you try the same thing using the Oracle's own OLE DB provider? Microsoft OLE DB provider for Oracle is pretty old one and I suspect it does not even know about nvarchar2 data type (I am not able to check this at the moment though).

There is a fundamental difference between transfering data into an existing or a new table. The existing table has the metadata already defined while the wizard generates new tables.

HTH,

Bob

Import Export Wizard and Unicode columns

Hello Folks,

This is my first real exposure to using SSIS's Import Export Tool.

I am trying to create a package to move the data from a number of tables in SQL Server into a duplicate database in Oracle. I am invoking the Import/Export WIzard from within the Management Studio. I am using the SQL Server native client on the SQL Server side and the Microsoft OLE DB driver for Oracle for the Oracle database. Both of my tables have unicode data types. On the SQL Server side I have columns defined as NVARCHAR and on Oracle the same column is defined as NVARCHAR2.

When I initially select the table for export the wizard assumes that I want to create a new table. The new table has the correct column name, column order and data types (NVARCHAR2). I cannot tell it to append the data. If I add the table owner to the detination table name or use the GUI to pick it with the table owner...the data types for the NVARCHAR2 columns go away. I cannot edit the data types at this point and if I continue, SSIS barks at me that it doesn't know the data types of those columns. The problem also occurs if you use the wizard from inside of the BIDS. It seems to be OK with the data type unless the table pre-exists. This seems un-useful to me.

Is there a way to avoid this in the Import/Export Wizard? Am I doing something wrong?

Any help would be appreciated.

Thanks, Mark

Hi Mark,

could you double check the metadata of your preexisting destination table matches the incoming metadata? Also, could you post the error the wizard reports?

Thanks,

Bob

|||Hi Bob,

Well, as I mentioned before if I try to continue SSIS barks and says:

TITLE: SQL Server Import and Export Wizard


Column information for the source and the destination data could not be retrieved, or the data types of source columns were not mapped correctly to those available on the destination provider.


[sql_dev_slove2].[dbo].[ACCESSPROFILE] -> "MARKT"."ACCESSPROFILE":

- The data type could not be assigned to the column "PROFILENM" in "Microsoft OLE DB Provider for Oracle".
- The data type could not be assigned to the column "PROFILEDSC" in "Microsoft OLE DB Provider for Oracle".

After this point, I can go no farther so (I think) I cannot see the actual meta data. If I do not change the owner (let SSIS think it needs to create the table) the input data type is DT_WSTR. The tables are defined as follows with the PROFILENM and PROFILEDSC columns being the troublesome ones:

SQL Server:
===========

CREATE TABLE ACCESSPROFILE
(
ACCESSPROFID INTEGER NOT NULL ,
PROFILENM NVARCHAR(50) NOT NULL ,
PROFILEDSC NVARCHAR(250) NULL ,
DEFAULTSW INTEGER DEFAULT 0 NULL ,
UPDATEDBYUSRACCTID INTEGER NOT NULL ,
UPDATEDTM DATETIME DEFAULT GETDATE() NOT NULL ,
VERSIONCNT INTEGER DEFAULT 1 NOT NULL ,
ALLOWALLSW INTEGER DEFAULT 0 NOT NULL
)
;

Oracle:
=======

CREATE TABLE ACCESSPROFILE
(
ACCESSPROFID NUMBER(10) NOT NULL ,
PROFILENM NVARCHAR2(50) NOT NULL ,
PROFILEDSC NVARCHAR2(250) NULL ,
DEFAULTSW NUMBER(10) DEFAULT 0 NULL ,
UPDATEDBYUSRACCTID NUMBER(10) NOT NULL ,
UPDATEDTM DATE DEFAULT SYSDATE NOT NULL ,
VERSIONCNT NUMBER(10) DEFAULT 1 NOT NULL ,
ALLOWALLSW NUMBER(10) DEFAULT 0 NOT NULL
)
/

Does this help?|||

Mark,

could you try the same thing using the Oracle's own OLE DB provider? Microsoft OLE DB provider for Oracle is pretty old one and I suspect it does not even know about nvarchar2 data type (I am not able to check this at the moment though).

There is a fundamental difference between transfering data into an existing or a new table. The existing table has the metadata already defined while the wizard generates new tables.

HTH,

Bob

sql

Import Export Wizard - ODBC to SQL

Hi all,

we have recently purchased a server with sql 2005. One of the things we are looking to do is pull our entire informix database over onto the sql box. I have tried using the import/export wizard however when it comes to the copying I the option to copy all tables is greyed out. I have been able to do this sql to sql but not when i select the source as odbc and destination as sql.

I need to try and resolve this quite quickly and any advice/help would be appreciated. Please bear in mind that im relatively new to this stuff. I have had a look at other posts, and am getting the impression that this cannot be done simply. My initial understanding was that this could be done using the DTS in sql 2000 quite simply, but was also told that it should be even easier in 2005..

Well appreciate any help

Ian

Unfortunately, your observations are right.

We do not natively support ODBC providers in SSIS. We do support ADO .NET provider for ODBC, but ADO .NET has somewhat complicated story with fetching table/column metadata generically, and we ran out of time to focus on that. That is the reason the wizard cannot offer the table selection page when ADO .NET providers are used (except for SQL Server one). We plan to have much more focus on this in the next version of the product.

For now, your best options are either to use the DTS wizard or to programmatically build SSIS packages (similar to the one the wizard produces for a single table) on the fly.

HTH. Thanks.

|||

Hi Bob,

thanks for the quick response. It will save me hours scouring forums and such for clarification. Following your response i cannot wait for an updated version to be released (hoping against hope that this will be a service pack, and not a requirement for me to purchase and upgrade)

One thing you did mention however was to use the DTS wizard.. can you elaborate on this.. how do i run this/where do i find it.. my understanding obvioulsy incorrect was that SSIS replaced DTS

Thanks once again

Ian

|||

Hi Ian,

I ran into the exact same problem: when trying to migrate informix table to sql server, the Copy Tables / Views option is greyed out. I am struggeling on a 'table-by-table' basis; you might try oledb with a so called linked server (create a linked server through oledb with the ifxoledbc driver that is provided in the Informix Client SDK). You can write queries that allow table migrations, but it is a lot of work when migrating hundreds of tables.....

Regards,

|||

The options mentioned in Bob's post was to either use the DTS wizard you had with SQL 2K and then upg your target to SQL 2k5.

Unless this is not a one time exercise - in that case you could programmatically create an SSIS package similar to a DTS package that migrates one table and repeat it for the whole set of source tables.

Import Export Wizard - ODBC to SQL

Hi all,

we have recently purchased a server with sql 2005. One of the things we are looking to do is pull our entire informix database over onto the sql box. I have tried using the import/export wizard however when it comes to the copying I the option to copy all tables is greyed out. I have been able to do this sql to sql but not when i select the source as odbc and destination as sql.

I need to try and resolve this quite quickly and any advice/help would be appreciated. Please bear in mind that im relatively new to this stuff. I have had a look at other posts, and am getting the impression that this cannot be done simply. My initial understanding was that this could be done using the DTS in sql 2000 quite simply, but was also told that it should be even easier in 2005..

Well appreciate any help

Ian

Unfortunately, your observations are right.

We do not natively support ODBC providers in SSIS. We do support ADO .NET provider for ODBC, but ADO .NET has somewhat complicated story with fetching table/column metadata generically, and we ran out of time to focus on that. That is the reason the wizard cannot offer the table selection page when ADO .NET providers are used (except for SQL Server one). We plan to have much more focus on this in the next version of the product.

For now, your best options are either to use the DTS wizard or to programmatically build SSIS packages (similar to the one the wizard produces for a single table) on the fly.

HTH. Thanks.

|||

Hi Bob,

thanks for the quick response. It will save me hours scouring forums and such for clarification. Following your response i cannot wait for an updated version to be released (hoping against hope that this will be a service pack, and not a requirement for me to purchase and upgrade)

One thing you did mention however was to use the DTS wizard.. can you elaborate on this.. how do i run this/where do i find it.. my understanding obvioulsy incorrect was that SSIS replaced DTS

Thanks once again

Ian

|||

Hi Ian,

I ran into the exact same problem: when trying to migrate informix table to sql server, the Copy Tables / Views option is greyed out. I am struggeling on a 'table-by-table' basis; you might try oledb with a so called linked server (create a linked server through oledb with the ifxoledbc driver that is provided in the Informix Client SDK). You can write queries that allow table migrations, but it is a lot of work when migrating hundreds of tables.....

Regards,

|||

The options mentioned in Bob's post was to either use the DTS wizard you had with SQL 2K and then upg your target to SQL 2k5.

Unless this is not a one time exercise - in that case you could programmatically create an SSIS package similar to a DTS package that migrates one table and repeat it for the whole set of source tables.

Import Export wizard

Hello,

I am having alot of problems with the import export wizard. I am trying to copy over around 315 tables to a new database. When the tables are copied over to the new database they are copied without any of the original constraints. Does anyone know why this could be happening? What I am trying to accomplish is a simple database transfer with the exception of about 10 to 15 tables from the original database. I do not understand why this is such a problem. I did not create a package, as this is going to be a one time shot.

Any help would be appreciated.

Thanks,

David

My understanding is that its not supposed to copy constraints. The import wizard is all about moving data, not schema objects. Of course, if you're moving data into a database then it has to have somewhere to put it so in this case, yes, it creates some tables for you. If you want to do move other schema objects then the simple answer is to script them out in SSMS and run the scripts on the target server. This is alot easier in SQL2005 than it used to be because in SSMS you can now change the connection so you dont have to copy and paste between differernt windows.

-Jamie

Import export wizard

In sql2k i can select bunch of tables in EM and export to a different db also I can schedule this activity.

How to do the same in SQL2K5?

Right-click on the database

Point to Tasks-->Export Data

Go thru the wizard, selecting the tables that you need.

You will be prompted to save the resultant SSIS package somewhere. Once you have saved it you can schedule it thru SQL Server Agent.

-Jamie

import excel data fail in sql 2005

Thanks for your reply,
It does not work after I convert the excel file to 2007 such as 1.xlsx
The sql server 2005's import wizard can only support up to Excel 97-2005 so
that I get "File path contains invalid Excel file,Please provide file with
..xls extension" error.
I did read the article, but I can not even find the
MICROSOFT\Jet\4.0\engines\excel key in the registor.
My access 2007 does work well when import the excel file, but access 2003
and sql server 2005 does not work?
any thoughts?
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> I assume that you have seen
> http://support.microsoft.com/kb/283881/
> If you convert the file to excel 2007 can you import it? For a connection
> string you would need Extended Properties=Excel 8.0 and I assume this is
> expecting Excel 12.0 but I guess that is not going to help your current issue!
> John
>
> "med" wrote:
Hi
Have you tried creating an ODBC DSN and see if that works?
Searching for "Could not find installable ISAM" on microsoft.com
[url]http://search.microsoft.com/results.aspx?q=Could+not+find+installable+ISAM&qsc 0=0&FORM=QBME1&l=1&mkt=en-GB[/url]
turns up a large number of hits such as http://support.microsoft.com/kb/318161
John
"med" wrote:
[vbcol=seagreen]
> Thanks for your reply,
> It does not work after I convert the excel file to 2007 such as 1.xlsx
> The sql server 2005's import wizard can only support up to Excel 97-2005 so
> that I get "File path contains invalid Excel file,Please provide file with
> .xls extension" error.
> I did read the article, but I can not even find the
> MICROSOFT\Jet\4.0\engines\excel key in the registor.
> My access 2007 does work well when import the excel file, but access 2003
> and sql server 2005 does not work?
> any thoughts?
> "John Bell" wrote:
|||Thanks John,
I had done a lot this kind of search, but can not find anything helpful.
Can you reproduce my error, just install the office2003, office2007,and sql
server 2005 on XP with sp2 to see if you can get this error?
I can create link servers to access databases by using either "Jet 4.0" or
"office 12.0 access database engine ole db provider", but no one work for
Excel.
Please test it and no more assumptions.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Have you tried creating an ODBC DSN and see if that works?
> Searching for "Could not find installable ISAM" on microsoft.com
> [url]http://search.microsoft.com/results.aspx?q=Could+not+find+installable+ISAM&qsc 0=0&FORM=QBME1&l=1&mkt=en-GB[/url]
> turns up a large number of hits such as http://support.microsoft.com/kb/318161
> John
> "med" wrote:
|||Hi
Unfortunately I don't have the facilities to do that. In general a
production environment would not have office installed. If you are not able
to create your own test environment then you may want to raise a PSS incident.
John
"med" wrote:
[vbcol=seagreen]
> Thanks John,
> I had done a lot this kind of search, but can not find anything helpful.
> Can you reproduce my error, just install the office2003, office2007,and sql
> server 2005 on XP with sp2 to see if you can get this error?
> I can create link servers to access databases by using either "Jet 4.0" or
> "office 12.0 access database engine ole db provider", but no one work for
> Excel.
> Please test it and no more assumptions.
>
> "John Bell" wrote:

import excel data fail in sql 2005

Hello,
When I use the sql server 2005 import and export wizard to import an excel
file, I always get "Could not find installable ISAM.(microsoft JET database
engine)" error.
my operating system is xp with sp2, sql server 2005, office 2003 and office
2007.
all of them have latest update (through microsoft update).
the excel file type is 97-2003 work sheet.
Within the System32 folder, there are MSJET40.dll (4.0.8618.0) and
MSEXCL40.dll (4.0.8618.0)
but I can not find the
HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Jet\4.0\engines\excel even after I
reinstall the xp sp2. there is
HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Jet\4.0\IIAM Formats\Excel 8.0
Currently, both sql 2005 and access 2003 can not import the data from excel
(97-2003 worksheet), but the access 2007 works well.
So I have to import the excel file into access 2007 first and then import it
into sql 2005.
Please tell me what can I do to fix this issue, your help are really
appreciated.Hi
I assume that you have seen
http://support.microsoft.com/kb/283881/
If you convert the file to excel 2007 can you import it? For a connection
string you would need Extended Properties=Excel 8.0 and I assume this is
expecting Excel 12.0 but I guess that is not going to help your current issue!
John
"med" wrote:
> Hello,
> When I use the sql server 2005 import and export wizard to import an excel
> file, I always get "Could not find installable ISAM.(microsoft JET database
> engine)" error.
> my operating system is xp with sp2, sql server 2005, office 2003 and office
> 2007.
> all of them have latest update (through microsoft update).
> the excel file type is 97-2003 work sheet.
> Within the System32 folder, there are MSJET40.dll (4.0.8618.0) and
> MSEXCL40.dll (4.0.8618.0)
> but I can not find the
> HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Jet\4.0\engines\excel even after I
> reinstall the xp sp2. there is
> HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Jet\4.0\IIAM Formats\Excel 8.0
> Currently, both sql 2005 and access 2003 can not import the data from excel
> (97-2003 worksheet), but the access 2007 works well.
> So I have to import the excel file into access 2007 first and then import it
> into sql 2005.
> Please tell me what can I do to fix this issue, your help are really
> appreciated.
>
>
>
>
>
>|||Thanks for your reply,
It does not work after I convert the excel file to 2007 such as 1.xlsx
The sql server 2005's import wizard can only support up to Excel 97-2005 so
that I get "File path contains invalid Excel file,Please provide file with
.xls extension" error.
I did read the article, but I can not even find the
MICROSOFT\Jet\4.0\engines\excel key in the registor.
My access 2007 does work well when import the excel file, but access 2003
and sql server 2005 does not work?
any thoughts?
"John Bell" wrote:
> Hi
> I assume that you have seen
> http://support.microsoft.com/kb/283881/
> If you convert the file to excel 2007 can you import it? For a connection
> string you would need Extended Properties=Excel 8.0 and I assume this is
> expecting Excel 12.0 but I guess that is not going to help your current issue!
> John
>
> "med" wrote:
> > Hello,
> >
> > When I use the sql server 2005 import and export wizard to import an excel
> > file, I always get "Could not find installable ISAM.(microsoft JET database
> > engine)" error.
> >
> > my operating system is xp with sp2, sql server 2005, office 2003 and office
> > 2007.
> > all of them have latest update (through microsoft update).
> >
> > the excel file type is 97-2003 work sheet.
> >
> > Within the System32 folder, there are MSJET40.dll (4.0.8618.0) and
> > MSEXCL40.dll (4.0.8618.0)
> > but I can not find the
> > HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Jet\4.0\engines\excel even after I
> > reinstall the xp sp2. there is
> > HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Jet\4.0\IIAM Formats\Excel 8.0
> >
> > Currently, both sql 2005 and access 2003 can not import the data from excel
> > (97-2003 worksheet), but the access 2007 works well.
> >
> > So I have to import the excel file into access 2007 first and then import it
> > into sql 2005.
> >
> > Please tell me what can I do to fix this issue, your help are really
> > appreciated.
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >|||Hi
Have you tried creating an ODBC DSN and see if that works?
Searching for "Could not find installable ISAM" on microsoft.com
http://search.microsoft.com/results.aspx?q=Could+not+find+installable+ISAM&qsc0=0&FORM=QBME1&l=1&mkt=en-GB
turns up a large number of hits such as http://support.microsoft.com/kb/318161
John
"med" wrote:
> Thanks for your reply,
> It does not work after I convert the excel file to 2007 such as 1.xlsx
> The sql server 2005's import wizard can only support up to Excel 97-2005 so
> that I get "File path contains invalid Excel file,Please provide file with
> .xls extension" error.
> I did read the article, but I can not even find the
> MICROSOFT\Jet\4.0\engines\excel key in the registor.
> My access 2007 does work well when import the excel file, but access 2003
> and sql server 2005 does not work?
> any thoughts?
> "John Bell" wrote:
> > Hi
> >
> > I assume that you have seen
> > http://support.microsoft.com/kb/283881/
> >
> > If you convert the file to excel 2007 can you import it? For a connection
> > string you would need Extended Properties=Excel 8.0 and I assume this is
> > expecting Excel 12.0 but I guess that is not going to help your current issue!
> >
> > John
> >
> >
> > "med" wrote:
> >
> > > Hello,
> > >
> > > When I use the sql server 2005 import and export wizard to import an excel
> > > file, I always get "Could not find installable ISAM.(microsoft JET database
> > > engine)" error.
> > >
> > > my operating system is xp with sp2, sql server 2005, office 2003 and office
> > > 2007.
> > > all of them have latest update (through microsoft update).
> > >
> > > the excel file type is 97-2003 work sheet.
> > >
> > > Within the System32 folder, there are MSJET40.dll (4.0.8618.0) and
> > > MSEXCL40.dll (4.0.8618.0)
> > > but I can not find the
> > > HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Jet\4.0\engines\excel even after I
> > > reinstall the xp sp2. there is
> > > HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Jet\4.0\IIAM Formats\Excel 8.0
> > >
> > > Currently, both sql 2005 and access 2003 can not import the data from excel
> > > (97-2003 worksheet), but the access 2007 works well.
> > >
> > > So I have to import the excel file into access 2007 first and then import it
> > > into sql 2005.
> > >
> > > Please tell me what can I do to fix this issue, your help are really
> > > appreciated.
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >|||Thanks John,
I had done a lot this kind of search, but can not find anything helpful.
Can you reproduce my error, just install the office2003, office2007,and sql
server 2005 on XP with sp2 to see if you can get this error?
I can create link servers to access databases by using either "Jet 4.0" or
"office 12.0 access database engine ole db provider", but no one work for
Excel.
Please test it and no more assumptions.
"John Bell" wrote:
> Hi
> Have you tried creating an ODBC DSN and see if that works?
> Searching for "Could not find installable ISAM" on microsoft.com
> http://search.microsoft.com/results.aspx?q=Could+not+find+installable+ISAM&qsc0=0&FORM=QBME1&l=1&mkt=en-GB
> turns up a large number of hits such as http://support.microsoft.com/kb/318161
> John
> "med" wrote:
> > Thanks for your reply,
> > It does not work after I convert the excel file to 2007 such as 1.xlsx
> >
> > The sql server 2005's import wizard can only support up to Excel 97-2005 so
> > that I get "File path contains invalid Excel file,Please provide file with
> > .xls extension" error.
> >
> > I did read the article, but I can not even find the
> > MICROSOFT\Jet\4.0\engines\excel key in the registor.
> >
> > My access 2007 does work well when import the excel file, but access 2003
> > and sql server 2005 does not work?
> >
> > any thoughts?
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > I assume that you have seen
> > > http://support.microsoft.com/kb/283881/
> > >
> > > If you convert the file to excel 2007 can you import it? For a connection
> > > string you would need Extended Properties=Excel 8.0 and I assume this is
> > > expecting Excel 12.0 but I guess that is not going to help your current issue!
> > >
> > > John
> > >
> > >
> > > "med" wrote:
> > >
> > > > Hello,
> > > >
> > > > When I use the sql server 2005 import and export wizard to import an excel
> > > > file, I always get "Could not find installable ISAM.(microsoft JET database
> > > > engine)" error.
> > > >
> > > > my operating system is xp with sp2, sql server 2005, office 2003 and office
> > > > 2007.
> > > > all of them have latest update (through microsoft update).
> > > >
> > > > the excel file type is 97-2003 work sheet.
> > > >
> > > > Within the System32 folder, there are MSJET40.dll (4.0.8618.0) and
> > > > MSEXCL40.dll (4.0.8618.0)
> > > > but I can not find the
> > > > HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Jet\4.0\engines\excel even after I
> > > > reinstall the xp sp2. there is
> > > > HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Jet\4.0\IIAM Formats\Excel 8.0
> > > >
> > > > Currently, both sql 2005 and access 2003 can not import the data from excel
> > > > (97-2003 worksheet), but the access 2007 works well.
> > > >
> > > > So I have to import the excel file into access 2007 first and then import it
> > > > into sql 2005.
> > > >
> > > > Please tell me what can I do to fix this issue, your help are really
> > > > appreciated.
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >|||Hi
Unfortunately I don't have the facilities to do that. In general a
production environment would not have office installed. If you are not able
to create your own test environment then you may want to raise a PSS incident.
John
"med" wrote:
> Thanks John,
> I had done a lot this kind of search, but can not find anything helpful.
> Can you reproduce my error, just install the office2003, office2007,and sql
> server 2005 on XP with sp2 to see if you can get this error?
> I can create link servers to access databases by using either "Jet 4.0" or
> "office 12.0 access database engine ole db provider", but no one work for
> Excel.
> Please test it and no more assumptions.
>
> "John Bell" wrote:
> > Hi
> >
> > Have you tried creating an ODBC DSN and see if that works?
> >
> > Searching for "Could not find installable ISAM" on microsoft.com
> > http://search.microsoft.com/results.aspx?q=Could+not+find+installable+ISAM&qsc0=0&FORM=QBME1&l=1&mkt=en-GB
> >
> > turns up a large number of hits such as http://support.microsoft.com/kb/318161
> >
> > John
> >
> > "med" wrote:
> >
> > > Thanks for your reply,
> > > It does not work after I convert the excel file to 2007 such as 1.xlsx
> > >
> > > The sql server 2005's import wizard can only support up to Excel 97-2005 so
> > > that I get "File path contains invalid Excel file,Please provide file with
> > > .xls extension" error.
> > >
> > > I did read the article, but I can not even find the
> > > MICROSOFT\Jet\4.0\engines\excel key in the registor.
> > >
> > > My access 2007 does work well when import the excel file, but access 2003
> > > and sql server 2005 does not work?
> > >
> > > any thoughts?
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > I assume that you have seen
> > > > http://support.microsoft.com/kb/283881/
> > > >
> > > > If you convert the file to excel 2007 can you import it? For a connection
> > > > string you would need Extended Properties=Excel 8.0 and I assume this is
> > > > expecting Excel 12.0 but I guess that is not going to help your current issue!
> > > >
> > > > John
> > > >
> > > >
> > > > "med" wrote:
> > > >
> > > > > Hello,
> > > > >
> > > > > When I use the sql server 2005 import and export wizard to import an excel
> > > > > file, I always get "Could not find installable ISAM.(microsoft JET database
> > > > > engine)" error.
> > > > >
> > > > > my operating system is xp with sp2, sql server 2005, office 2003 and office
> > > > > 2007.
> > > > > all of them have latest update (through microsoft update).
> > > > >
> > > > > the excel file type is 97-2003 work sheet.
> > > > >
> > > > > Within the System32 folder, there are MSJET40.dll (4.0.8618.0) and
> > > > > MSEXCL40.dll (4.0.8618.0)
> > > > > but I can not find the
> > > > > HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Jet\4.0\engines\excel even after I
> > > > > reinstall the xp sp2. there is
> > > > > HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Jet\4.0\IIAM Formats\Excel 8.0
> > > > >
> > > > > Currently, both sql 2005 and access 2003 can not import the data from excel
> > > > > (97-2003 worksheet), but the access 2007 works well.
> > > > >
> > > > > So I have to import the excel file into access 2007 first and then import it
> > > > > into sql 2005.
> > > > >
> > > > > Please tell me what can I do to fix this issue, your help are really
> > > > > appreciated.
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >

Import Error

I receive unhelpful errors from attempting to import a ms access table into sql server 2005 using the import wizard. Here is the report.

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Error)

Messages

Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Data Conversion 1" (73).
(SQL Server Import and Export Wizard)

- Prepare for Execute (Stopped)

- Pre-execute (Stopped)

- Executing (Success)

- Copying to [Company].[dbo].[EMPLOYEE] (Stopped)

- Post-execute (Stopped)

- Cleanup (Stopped)

I received this type of error once before and got around it by removing all constraints from the table. I was able to import the table when the data types were nvarchar. I changed the data types to varchar and this occurred. How do I know what the message is specifically referring to or can I see more details of the import command. This is my first posting.

Charley,

Data conversion transformation requires SQL Server enterprise edition to be executed. see if this link provides more information

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=594086&SiteID=1