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 into SQL Server Oracle Database from Export File (SQL*Loader)

I need to import an Oracle Database into SQL Server 2000.
I know this can be done easily using DTS but I do not have access to the
Oracle Database, I only have an Oracle Database Export File.
The Export file has been generated using SQL*Loader.
Oracle Export File is created with a proprietery format that can only be
read by the importer. This is the same problem if you take a SQL backup file
and try to load it into Oracle without access to a SQLServer.
-oj
"K Kelly" <kkelly@.nospam.com> wrote in message
news:eryCZe8$EHA.2608@.TK2MSFTNGP10.phx.gbl...
>I need to import an Oracle Database into SQL Server 2000.
> I know this can be done easily using DTS but I do not have access to the
> Oracle Database, I only have an Oracle Database Export File.
> The Export file has been generated using SQL*Loader.
>
|||On Fri, 21 Jan 2005 14:42:22 -0000, "K Kelly" <kkelly@.nospam.com> wrote:

>I need to import an Oracle Database into SQL Server 2000.
>I know this can be done easily using DTS but I do not have access to the
>Oracle Database, I only have an Oracle Database Export File.
>The Export file has been generated using SQL*Loader.
>
While it may not be strictly legal from a licensing standpoint, there is an approach that may work. First, Oracle
makes copies of their older software available for downloading from their website. Therefore, you could download a
copy of Oracle and install it on a suitable system. Next, import the Oracle database from the export file. Be
warned -- the export file also includes information on directory locations for database files, rollback segments,
and so on. In order for the import to work properly, you need to make sure that you replicate the file system
environment exactly. Oracle will not re-create it on the fly, so you may spend quite a bit of time reading error
logs before you can get it right. In the end, though, it should work correctly.
Once you have the Oracle database up and running, use DTS from SQL Server to cross over the data you need,
remembering to deal with issues such as data type conversions, etc. (pardon me for straying into "I know that!!"
areas). After you get it crossed over, then kill the Oracle installation before the license police catch you.
I once did a similar thing years ago in the opposite direction. I needed to move a DocsOpen SQL 4.0 database into
Oracle 7.2, but the Docs application no longer supported SQL 4, only SQL 6.5 and above. However, Microsoft had a
120-day demonstration version of SQL 6.5 on their website. So I downloaded that, used it to upgrade the SQL 4
database to SQL 6.5, then ran DocsOpen to move it from SQL Server to Oracle 7.2. Worked a treat. Crazily enough, I
have to now move that same database from Oracle 8.1 to SQL Server 2000 in the next couple of weeks.
Good luck.
|||Clever! ;-)
-oj
"Norm Powroz" <npowroz.delete.this.part@.and.this.part.rogers.com > wrote in
message news:1a45v09n8p0j4ud4ep57cdu91vgav6qv2r@.4ax.com...
> On Fri, 21 Jan 2005 14:42:22 -0000, "K Kelly" <kkelly@.nospam.com> wrote:
>
> While it may not be strictly legal from a licensing standpoint, there is
> an approach that may work. First, Oracle
> makes copies of their older software available for downloading from their
> website. Therefore, you could download a
> copy of Oracle and install it on a suitable system. Next, import the
> Oracle database from the export file. Be
> warned -- the export file also includes information on directory locations
> for database files, rollback segments,
> and so on. In order for the import to work properly, you need to make sure
> that you replicate the file system
> environment exactly. Oracle will not re-create it on the fly, so you may
> spend quite a bit of time reading error
> logs before you can get it right. In the end, though, it should work
> correctly.
> Once you have the Oracle database up and running, use DTS from SQL Server
> to cross over the data you need,
> remembering to deal with issues such as data type conversions, etc.
> (pardon me for straying into "I know that!!"
> areas). After you get it crossed over, then kill the Oracle installation
> before the license police catch you.
> I once did a similar thing years ago in the opposite direction. I needed
> to move a DocsOpen SQL 4.0 database into
> Oracle 7.2, but the Docs application no longer supported SQL 4, only SQL
> 6.5 and above. However, Microsoft had a
> 120-day demonstration version of SQL 6.5 on their website. So I downloaded
> that, used it to upgrade the SQL 4
> database to SQL 6.5, then ran DocsOpen to move it from SQL Server to
> Oracle 7.2. Worked a treat. Crazily enough, I
> have to now move that same database from Oracle 8.1 to SQL Server 2000 in
> the next couple of weeks.
> Good luck.
>
sql

Import into SQL Server Oracle Database from Export File (SQL*Loader)

On Fri, 21 Jan 2005 14:42:22 -0000, "K Kelly" <kkelly@.nospam.com> wrote:

>I need to import an Oracle Database into SQL Server 2000.
>I know this can be done easily using DTS but I do not have access to the
>Oracle Database, I only have an Oracle Database Export File.
>The Export file has been generated using SQL*Loader.
>
While it may not be strictly legal from a licensing standpoint, there is an
approach that may work. First, Oracle
makes copies of their older software available for downloading from their we
bsite. Therefore, you could download a
copy of Oracle and install it on a suitable system. Next, import the Oracle
database from the export file. Be
warned -- the export file also includes information on directory locations f
or database files, rollback segments,
and so on. In order for the import to work properly, you need to make sure t
hat you replicate the file system
environment exactly. Oracle will not re-create it on the fly, so you may spe
nd quite a bit of time reading error
logs before you can get it right. In the end, though, it should work correct
ly.
Once you have the Oracle database up and running, use DTS from SQL Server to
cross over the data you need,
remembering to deal with issues such as data type conversions, etc. (pardon
me for straying into "I know that!!"
areas). After you get it crossed over, then kill the Oracle installation bef
ore the license police catch you.
I once did a similar thing years ago in the opposite direction. I needed to
move a DocsOpen SQL 4.0 database into
Oracle 7.2, but the Docs application no longer supported SQL 4, only SQL 6.5
and above. However, Microsoft had a
120-day demonstration version of SQL 6.5 on their website. So I downloaded t
hat, used it to upgrade the SQL 4
database to SQL 6.5, then ran DocsOpen to move it from SQL Server to Oracle
7.2. Worked a treat. Crazily enough, I
have to now move that same database from Oracle 8.1 to SQL Server 2000 in th
e next couple of ws.
Good luck.Clever! ;-)
-oj
"Norm Powroz" <npowroz.delete.this.part@.and.this.part.rogers.com> wrote in
message news:1a45v09n8p0j4ud4ep57cdu91vgav6qv2r@.
4ax.com...
> On Fri, 21 Jan 2005 14:42:22 -0000, "K Kelly" <kkelly@.nospam.com> wrote:
>
> While it may not be strictly legal from a licensing standpoint, there is
> an approach that may work. First, Oracle
> makes copies of their older software available for downloading from their
> website. Therefore, you could download a
> copy of Oracle and install it on a suitable system. Next, import the
> Oracle database from the export file. Be
> warned -- the export file also includes information on directory locations
> for database files, rollback segments,
> and so on. In order for the import to work properly, you need to make sure
> that you replicate the file system
> environment exactly. Oracle will not re-create it on the fly, so you may
> spend quite a bit of time reading error
> logs before you can get it right. In the end, though, it should work
> correctly.
> Once you have the Oracle database up and running, use DTS from SQL Server
> to cross over the data you need,
> remembering to deal with issues such as data type conversions, etc.
> (pardon me for straying into "I know that!!"
> areas). After you get it crossed over, then kill the Oracle installation
> before the license police catch you.
> I once did a similar thing years ago in the opposite direction. I needed
> to move a DocsOpen SQL 4.0 database into
> Oracle 7.2, but the Docs application no longer supported SQL 4, only SQL
> 6.5 and above. However, Microsoft had a
> 120-day demonstration version of SQL 6.5 on their website. So I downloaded
> that, used it to upgrade the SQL 4
> database to SQL 6.5, then ran DocsOpen to move it from SQL Server to
> Oracle 7.2. Worked a treat. Crazily enough, I
> have to now move that same database from Oracle 8.1 to SQL Server 2000 in
> the next couple of ws.
> Good luck.
>

Import index and constain

Dear
I export the database table,SP,view,use..etc to script
file. After i run the script to another server to rebuild
the database. Only the table(index and constain) can't
import to new database but i have choice the option(index
and constain) when i export the database to script. What
is ths problem?
Many Thanks
JohnJohn
How did you export your objects?
Try using DTS , there is an optino "Transfer Objects" so you will find that
you can export indexes and keys.
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:066301c3c476$0eb20a80$a501280a@.phx.gbl...
> Dear
> I export the database table,SP,view,use..etc to script
> file. After i run the script to another server to rebuild
> the database. Only the table(index and constain) can't
> import to new database but i have choice the option(index
> and constain) when i export the database to script. What
> is ths problem?
> Many Thanks
> John

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 2005 into 2000

Hi, we have a 2005 database which we want to export information from
back into a legacy 2000 database.
What is the best way to do this?
Thanks
CatherineOn Jan 29, 8:44=A0am, cather...@.myoddjobs.info wrote:
> Hi, we have a 2005 database which we want to export information from
> back into a legacy 2000 database.
> What is the best way to do this?
> Thanks
> Catherine
If the compatibility level is set to 2000 then I don't see any problem
here. you can use DTS and all...
Thanks
Ajay|||It depends. To transfer data only once it is easy to use the Export and
Import Wizard. To transfer data periodically you could create SSIS packages.
Or you can use SQL Server replication.
Hope this helps,
Ben Nevarez
"catherine@.myoddjobs.info" wrote:
> Hi, we have a 2005 database which we want to export information from
> back into a legacy 2000 database.
> What is the best way to do this?
> Thanks
> Catherine
>

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 Export in SQL Server 2005

In case of Sql Server 2000, we have the option for import / Export data using DTS by creating objects.

Doing this we are getting the default value of colms of tables int the destination database.

But in case of SQL Server 2005 i am not getting this feature.

If i am using the Import /Export I am not getting the default values.

So first i am generating the script for table and create table in the destination database and then using import/export option to copy data.

Is there any other option to do this?

DEFAULT values are elements of the table schema. If you are not getting the default values, there are two possible explanitions.

1. Your script that creates the tables does not include the DEFAULT values.

2. Your data has empty strings instead of NULL values (DEFAULT values are ONLY used if the data is missing or NULL.)

|||

Yah what you are saying is right.

But what i am asking is , by default i am not getting default values while using import/ export to a new data base.

For that i am first generating script for tables and the table syntax include the default. and then using import/export data.

Is it possible, with out generating the script for tables from the existing database will get the default value in the new database.

|||Yes it is possible -but ONLY if the missing data is [NULL], NOT empty string [''].|||

You need to use the SQL Server specific version of import/export, look here for more info;

http://blogs.msdn.com/euanga/archive/2006/07/18/668916.aspx

sql

Import Export in SQL Server 2005

In case of Sql Server 2000, we have the option for import / Export data using DTS by creating objects.

Doing this we are getting the default value of colms of tables int the destination database.

But in case of SQL Server 2005 i am not getting this feature.

If i am using the Import /Export I am not getting the default values.

So first i am generating the script for table and create table in the destination database and then using import/export option to copy data.

Is there any other option to do this?

DEFAULT values are elements of the table schema. If you are not getting the default values, there are two possible explanitions.

1. Your script that creates the tables does not include the DEFAULT values.

2. Your data has empty strings instead of NULL values (DEFAULT values are ONLY used if the data is missing or NULL.)

|||

Yah what you are saying is right.

But what i am asking is , by default i am not getting default values while using import/ export to a new data base.

For that i am first generating script for tables and the table syntax include the default. and then using import/export data.

Is it possible, with out generating the script for tables from the existing database will get the default value in the new database.

|||Yes it is possible -but ONLY if the missing data is [NULL], NOT empty string [''].|||

You need to use the SQL Server specific version of import/export, look here for more info;

http://blogs.msdn.com/euanga/archive/2006/07/18/668916.aspx

Import export failed : Data conversion failed

[Source - chn_employee_vew_test_txt [1]] Error: Data conversion failed. The data conversion for column "Column 42" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

[Source - chn_employee_vew_test_txt [1]] Error: The "output column "Column 42" (136)" failed because truncation occurred, and the truncation row disposition on "output column "Column 42" (136)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

I using Locale (People's Republic of China) and code page of 936 (Simplied Chinese GBK) with header row delimiter {CR}{LF}.

I am using flat file import method.

Whenever the server process the Column 42 with value "11,Nanjing Rd.W, China" which contain 'comma' or '.' it will hit error importing with above message. When i manually change the column value to non comma or '.' (11 Nanjing Rd W China) in the flat file it is ok.

I am using SQL server 2005.

Please advise what need to be done to avoid this error ?

Thanks in advance and any idea or suggestion is very much appreciated as i have try to solve this issue for over a week but still not able to find any answer on it.

Please help.

regards,

kong

Check the Quoted_Identifier and text_qualifier values in SSIS, if you are running from a workstation then make sure SSIS server component is installed

import excel file into dropdownlist then export to sql server 2005

i am handling a project where user can choose the excel file and the field in the excel file to export into sql server 2005. which mean there will be dropdownlist where the user can choose the field and so on. anyone know how to do this?I'm a bit uncertain how this question is Asp.Net related. Please clarify how it is.|||which mean i will need to import the excel file into a dataset and then bind the dataset to the drop down list and at last import it to the sql server 2005. it can be in vb.net or asp.net

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.
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >