Import Oracle CLOB to SQL Server

I am trying to import an Oracle table with CLOB datatype. The ODBC and OLEDB
drivers does not provide this functionality. Is there any way to import the
CLOB data? Are there any ODBC drivers(preferably free) that can provide this
functionality?
Thanks in advance.
Pradip,
What kind of ODBC driver did you try? We have no problem with export BLOBs
from Oracle and to import it into MS SQL.
P> I am trying to import an Oracle table with CLOB datatype. The ODBC
P> and OLEDB drivers does not provide this functionality.
Igor Shekalev, http://www.sqledit.com, powerful database tools
sql

Import Oracle CLOB to SQL Server

I am trying to import an Oracle table with CLOB datatype. The ODBC and OLEDB
drivers does not provide this functionality. Is there any way to import the
CLOB data? Are there any ODBC drivers(preferably free) that can provide this
functionality?
Thanks in advance.Pradip,
What kind of ODBC driver did you try? We have no problem with export BLOBs
from Oracle and to import it into MS SQL.
P> I am trying to import an Oracle table with CLOB datatype. The ODBC
P> and OLEDB drivers does not provide this functionality.
Igor Shekalev, http://www.sqledit.com, powerful database tools

Import ODBC Drivers

Is there a way to import or copy the odbc drivers from my server to my client
without having to go through the process of setting up a new driver, defining
the format, etc... on each client machine.
Yes, I searched first
Hi
No. Windows drivers are intended to be installed with a setup program.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Kmistic" <Kmistic@.discussions.microsoft.com> wrote in message
news:5D4392DD-19E7-4C99-94DC-CAC577991954@.microsoft.com...
> Is there a way to import or copy the odbc drivers from my server to my
client
> without having to go through the process of setting up a new driver,
defining
> the format, etc... on each client machine.
> --
> Yes, I searched first

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 large field from SSIS

Hi,

I am making a SSIS package that imports data from a application using a custom ODBC driver. The field in the application is set to be a "longvarchar" type field and can be from 2 characters to 2MB of data.

I've created a ODBC data connection in the SSIS package and use a "DataReader Source" to read the data I need. The sql statement is very simple

Select log from tablename

When I try to run the SSIS package with that statement it just goes to yellow on the DataReader Source and stops. It stays like that until I stop it. If I select other fields except for that field it works fine. Also I've been able to get it to succeed getting the log field if I select a log record that's not too big. The largest one I've been able to get is 800 characters, but I got one with 2500 characters that just stops on yellow.

In the Progress log the last line says:

[DTS.Pipeline] Information: Execute phase is beginning.

Does anyone have any ideas on how to resolve this?
Have you check the ValidateMetadeta properties of data source Is there any warning message appear in you data reader ?|||I've tried both with having the ValidateMetaData option to false and true but it doesn't make any difference. There is no error/warning messages in the progress log and I don't know of any other places to look for error messages.

This is really starting to annoy me, but this is the only way we can get the data out of that system so I need this to work...
|||

Hi,

DataReaderSrc is not particularly efficient about dealing with BLOB data, such as DT_NTEXT or DT_IMAGE columns. It may be that it is just being slow...

Unfortunately, DataReaderSrc does not utilize the perf counters for BLOB bytes read -- this is a known issue that is planned to be fixed in a future release. If you look at perfmon while the package is running, what's happening with the CPU and memory usage?

mark

|||Thanks for your answer. When looking at the perfmon and the task manager while running the package I was a bit surprised.
First of in the perfmon the Memory object is steady on 0, the Physical Disk object is going up and down from 0 to 20 and then there is the occasional spike up to 100.
Then there is the processor object which stays at around 50 constantly. Looking at the task manager the process: "DtsDebugHost.exe" is staying at 50% CPU and using 30.976 K memory. Is this normal when executing a package?

I was thinking too that it might just be slow. But I changed my query to only select 1 record based on the id of the record and it still stays on for 20 min+ (I stopped it after that). If I select a record that got less data in the BLOB field then it completes within 10 seconds.

My development server is a Intel Xeon dual 3.6 GHz with 3 GB memory so I don't think our server is good enough

If the future fix will fix this issue that will be good enough, because I sort of told people that we have to find a different way to create the reports.
|||

The "future fix" I spoke of was just to add the performance counters to DataReaderSrc, which would help diagnose issues like this one.

I will try to set up a similar scenario here to see if I can reproduce the behaviour you are seeing.

thanks

Mark

|||

Hi Josh,

I created a package with a datareader source, and used connections of the type:

.NET Providers\SqlClient Data Provider

.NET Providers\Odbc Data Provider

In both cases, i was able to read 20 Mb of TEXT data in a second or two.

Is it possible for you to try using a different driver/provider?

Can you try using some other application with your driver/provider to see if you can read the data or if you have the same problem?

thanks

Mark

import large field from SSIS

Hi,

I am making a SSIS package that imports data from a application using a custom ODBC driver. The field in the application is set to be a "longvarchar" type field and can be from 2 characters to 2MB of data.

I've created a ODBC data connection in the SSIS package and use a "DataReader Source" to read the data I need. The sql statement is very simple

Select log from tablename

When I try to run the SSIS package with that statement it just goes to yellow on the DataReader Source and stops. It stays like that until I stop it. If I select other fields except for that field it works fine. Also I've been able to get it to succeed getting the log field if I select a log record that's not too big. The largest one I've been able to get is 800 characters, but I got one with 2500 characters that just stops on yellow.

In the Progress log the last line says:

[DTS.Pipeline] Information: Execute phase is beginning.

Does anyone have any ideas on how to resolve this?
Have you check the ValidateMetadeta properties of data source Is there any warning message appear in you data reader ?|||I've tried both with having the ValidateMetaData option to false and true but it doesn't make any difference. There is no error/warning messages in the progress log and I don't know of any other places to look for error messages.

This is really starting to annoy me, but this is the only way we can get the data out of that system so I need this to work...
|||

Hi,

DataReaderSrc is not particularly efficient about dealing with BLOB data, such as DT_NTEXT or DT_IMAGE columns. It may be that it is just being slow...

Unfortunately, DataReaderSrc does not utilize the perf counters for BLOB bytes read -- this is a known issue that is planned to be fixed in a future release. If you look at perfmon while the package is running, what's happening with the CPU and memory usage?

mark

|||Thanks for your answer. When looking at the perfmon and the task manager while running the package I was a bit surprised.
First of in the perfmon the Memory object is steady on 0, the Physical Disk object is going up and down from 0 to 20 and then there is the occasional spike up to 100.
Then there is the processor object which stays at around 50 constantly. Looking at the task manager the process: "DtsDebugHost.exe" is staying at 50% CPU and using 30.976 K memory. Is this normal when executing a package?

I was thinking too that it might just be slow. But I changed my query to only select 1 record based on the id of the record and it still stays on for 20 min+ (I stopped it after that). If I select a record that got less data in the BLOB field then it completes within 10 seconds.

My development server is a Intel Xeon dual 3.6 GHz with 3 GB memory so I don't think our server is good enough

If the future fix will fix this issue that will be good enough, because I sort of told people that we have to find a different way to create the reports.
|||

The "future fix" I spoke of was just to add the performance counters to DataReaderSrc, which would help diagnose issues like this one.

I will try to set up a similar scenario here to see if I can reproduce the behaviour you are seeing.

thanks

Mark

|||

Hi Josh,

I created a package with a datareader source, and used connections of the type:

.NET Providers\SqlClient Data Provider

.NET Providers\Odbc Data Provider

In both cases, i was able to read 20 Mb of TEXT data in a second or two.

Is it possible for you to try using a different driver/provider?

Can you try using some other application with your driver/provider to see if you can read the data or if you have the same problem?

thanks

Mark

Import from an ODBC data source into SQL Server

Hi,

I am trying to import tables from an ODBC data source into an SQL Server 2005 database. I presume that one way to achieve this is to create an Integration Services package, via Business Intelligence Studio (I already used DTS in SQL Server 2000, but not Integration Services) ?

Or is there a simpler way ? For instance, is there an import wizard that woult include an ODBC Data source ?

Thanks i advance.

http://groups.google.de/group/microsoft.public.sqlserver.dts/browse_frm/thread/2d0b1220a73e2894/f9adfb6af01a8306?hl=de#f9adfb6af01a8306

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 data from ODBC to SQL2005

Hi, there;
I want to importing data from ODBC, I created DataReader Source which use a .NET Provide \Odbc Data Provider and connected successfully. My destination is a OLE DB Destination that points to SQL2005. I set the SQL command as "SELECT * from ....".
I also have problem to create new table in SQL2005 using SSIS Import and Export Wizard, it doesn't know the source table table schema (two date type column). So I create the new table manully and run the package, I got error:

SSIS package "Package1.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
Error: 0xC02090F5 at Data Flow Task, Source - Query [1]: The component "Source - Query" (1) was unable to process the data.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "Source - Query" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.
Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DF at Data Flow Task, Destination - PITest CMF [169]: The final commit for the data insertion has started.
Information: 0x402090E0 at Data Flow Task, Destination - PITest CMF [169]: The final commit for the data insertion has ended.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "Destination - PITest CMF" (169)" wrote 0 rows.
Task failed: Data Flow Task
SSIS package "Package1.dtsx" finished: Success.

Can any one know what's wrong here?

And it is quite pain that you have to specify the table name every time you want to create a new table in SQL2005. It is so easy in SQL2000!!!

Thanks.Did you verify that your source query worked and the column mappings metadata is correct? That's where I would start looking.

import data from MySQL to SQL Server

Hi All
I'm trying to import data from mySQL database to SQL server 2005. I installed mySQL odbc driver (3.51) and configured system DSN. However when I start SQL server import/export wizard mysql is not an option in data source dropdown.
What do I miss?
Thanks,
Vlad.Hi,
I never worked with MySQL, but there is a general approach. If you were able to create DSN, try to create a DTS package on SQL Server. I do not use SQL Server2005, but 2000. There is Other Connections icon under Connections in DTS designer. Configure this connection for MySql. The rest is yours - just configure the destination for your MySQL data.
Regards|||Hi,
I agree with dbmjs that creating a DTS package will probably be an easy way of transfering the data. Just a couple of quick notes:
1) DTS in sql server 2000 works great for smaller < 1mil records. Anything bigger than that it seems to run into some issues. (You can always make a couple of smaller packets and import them)
2) The DTS equivelant in SQL Server 2005 is named SQL Server Integration Services (SSIS), and works great no matter the size or amount of records.

Hope this helps
Good luck,
Reghardt

Import a database with .net framework data provider for ODBC

mHello all,
i have to import a database (micro focus files with idx-files) with an obdc
driver. When I use the assistant, I choose ".net framework data provider for
ODBC". In the next window I am asked for the connection string an driver. How
can I specify the driver to be used and the location of the original
databaseIn SQL 2000 it was quite easy using the ODBC Connection manager, but
in SQL 2005 I cant find the drivers. Any help or link to a documentation
appreciated. Thank you.
I have the same problem, did you ever find the fix for this?
i have to import a database (micro focus files with idx-files) with an obdc
driver. When I use the assistant, I choose ".net framework data provider for
ODBC". In the next window I am asked for the connection string an driver. How
can I specify the driver to be used and the location of the original
databaseIn SQL 2000 it was quite easy using the ODBC Connection manager, but
in SQL 2005 I cant find the drivers. Any help or link to a documentation
appreciated. Thank you.[/quote]

Import a database with .net framework data provider for ODBC

mHello all,
i have to import a database (micro focus files with idx-files) with an obdc
driver. When I use the assistant, I choose ".net framework data provider for
ODBC". In the next window I am asked for the connection string an driver. Ho
w
can I specify the driver to be used and the location of the original
databaseIn SQL 2000 it was quite easy using the ODBC Connection manager, but
in SQL 2005 I cant find the drivers. Any help or link to a documentation
appreciated. Thank you.I have the same problem, did you ever find the fix for this'
i have to import a database (micro focus files with idx-files) with an
obdc
driver. When I use the assistant, I choose ".net framework data
provider for
ODBC". In the next window I am asked for the connection string an
driver. How
can I specify the driver to be used and the location of the original
databaseIn SQL 2000 it was quite easy using the ODBC Connection
manager, but
in SQL 2005 I cant find the drivers. Any help or link to a
documentation
appreciated. Thank you.
RCranston