dts2000 migration, activex script and odbc connections help needed

Apologies if this has been covered before or if i am in the wrong place.

I am migrating some DTS2000 packages to SSIS via the migration wizard. However there is an activex script in each DTS package that connects to an AS400 to retrieve teh record count of a table for integrity checking. Under SQL server 2000 this worked fine but in SSIS it errors with "The OLE DB provider "MSADSQL" has not been registered" how do i get this to work i suspect its because this is a 32 bit dll and my new system is a 64bit sql server 05 on windows 2003. The odbc driver for the as400 has been installed also.

The activex script is as follows:

dim cn 'sql connection
dim rs 'sql recordset for the insertion of new as400 records
dim insertstr 'sql insert string
dim rs1 'sql recordset for the insertion of new as400 records
dim insertstr1 'sql insert string

set cn = createobject("ADODB.Connection")
set rs = createobject("ADODB.Recordset")

Function Main()
'set up connection for sql
cn.provider="sqloledb"
cn.commandtimeout = 3600 'timeout in seconds
cn.open %sqlserver05%, %user%, %password%

'populate temporary table in sql with recordset from as400 odbc
insertstr1 = "Insert into db.dbo.%table% select 'table name',a.* from openrowset ('MSDASQL','DRIVER={Client Access ODBC Driver (32-bit)};SYSTEM=%system name%;UID=%username%;PWD=%password%','SELECT count(*) from %table%') as a"

set rs1 = cn.execute (insertstr1)

'close all objects
cn.close
Main = DTSTaskExecResult_Success
End Function

I have since changed MSDASQL to SQLNCLI but (after errors re ad hoc distributed queries which i have enabled) i now get errors re linked servers:

ole db provider "sqlncli" for linked server "(null)" returned message "invalid connection string attrbute"

Does anyone know how i change this to work under sql server 2005. i need to resolve this quickly in order to deliver a project on time. Many thanks

Chris

I am contacting the SSIS team but I suspect that you need to run the 32-bit version of SSIS since - as you pointed out - the ODBC driver is a 32-bit DLL, somehwat similar to the case discussed at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=167907&SiteID=1.

|||

I confirmed: if there is only 32-bit ODBC driver, you need to run the package using 32-bit runtime.

DTS/SSIS too many tables warning and subsequent error

I need to bring over a large number of tables' records (200+ tables) with the Import/Export Wizard. The tables are being imported from MS Access. A separate script run previously will create the tables, so the DTS wizard is only to bring over the data from the Access tables into the empty SQL ones.

First, I get the warning that indicates "a large number of tables are selected for copying, and the wizard may not be able to copy all the tables in a session. Select no to go back and unselect some tables, or select Yes to attempt to copy all the currently selected tables at one time".

Well, I proceed with the DTS and it tries to validate and takes a fair bit, but then it errors indicating:

"Error 0xc0202009: {2F0FABA0-5F4B-4310-97C0-76EA19893547}: 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)"

Can anyone shed any light on why I receive an "unspecified error" when tring to DTS a larger number of tables. It does not error, if I import 40 or so tables.

This was never an issue with SQL 2000 DTS.

Thanks

"a large number of tables are selected for copying, and the wizard may not be able to copy all the tables in a session. Select no to go back and unselect some tables, or select Yes to attempt to copy all the currently selected tables at one time".

The wizard told you that it may not be able to do it.

|||

Hey,

Hah! Nice man. Well I hear you on that one, but why is this an issue with the SQL 2005 DTS and not the older SQL 2000 DTS, and is there any way around it?

Thanks

DTS, overwrite excel sheet

Hi,
I have a MS SQL 2000 SP4. I need to export a table to Excel sheet every
hour. I created DTS (Data Transformation Services) packages by wizard.
It works fine first time but when DTS runs next time it ends with error:
table 'xxxx' already exist. How to manage the overwrite of this Excel
Sheet 'xxxx' every hour automatically?
Thanks,
Martin.
Add a Execute SQL task like this
drop table `xxxx`
go
CREATE TABLE `xxxx` (
`FieldName1` FieldType1,
`FieldName2` FieldType2
)
Francesco Anti
"martin" <martin@.server.sk> wrote in message
news:eS0sc1shFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a MS SQL 2000 SP4. I need to export a table to Excel sheet every
> hour. I created DTS (Data Transformation Services) packages by wizard.
> It works fine first time but when DTS runs next time it ends with error:
> table 'xxxx' already exist. How to manage the overwrite of this Excel
> Sheet 'xxxx' every hour automatically?
>
> Thanks,
> Martin.
>
|||It works fine,
Thank you
Martin
"Francesco Anti" <fanti_@._sicosbt.it> napsal v sprve
news:%233EejCthFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Add a Execute SQL task like this
> drop table `xxxx`
> go
> CREATE TABLE `xxxx` (
> `FieldName1` FieldType1,
> `FieldName2` FieldType2
> )
> Francesco Anti
> "martin" <martin@.server.sk> wrote in message
> news:eS0sc1shFHA.1412@.TK2MSFTNGP09.phx.gbl...
>

DTS, overwrite excel sheet

Hi,
I have a MS SQL 2000 SP4. I need to export a table to Excel sheet every
hour. I created DTS (Data Transformation Services) packages by wizard.
It works fine first time but when DTS runs next time it ends with error:
table 'xxxx' already exist. How to manage the overwrite of this Excel
Sheet 'xxxx' every hour automatically?
Thanks,
Martin.Add a Execute SQL task like this
drop table `xxxx`
go
CREATE TABLE `xxxx` (
`FieldName1` FieldType1,
`FieldName2` FieldType2
)
Francesco Anti
"martin" <martin@.server.sk> wrote in message
news:eS0sc1shFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a MS SQL 2000 SP4. I need to export a table to Excel sheet every
> hour. I created DTS (Data Transformation Services) packages by wizard.
> It works fine first time but when DTS runs next time it ends with error:
> table 'xxxx' already exist. How to manage the overwrite of this Excel
> Sheet 'xxxx' every hour automatically?
>
> Thanks,
> Martin.
>|||It works fine,
Thank you
Martin
"Francesco Anti" <fanti_@._sicosbt.it> napsal v sprve
news:%233EejCthFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Add a Execute SQL task like this
> drop table `xxxx`
> go
> CREATE TABLE `xxxx` (
> `FieldName1` FieldType1,
> `FieldName2` FieldType2
> )
> Francesco Anti
> "martin" <martin@.server.sk> wrote in message
> news:eS0sc1shFHA.1412@.TK2MSFTNGP09.phx.gbl...
>

DTS, overwrite excel sheet

Hi,
I have a MS SQL 2000 SP4. I need to export a table to Excel sheet every
hour. I created DTS (Data Transformation Services) packages by wizard.
It works fine first time but when DTS runs next time it ends with error:
table 'xxxx' already exist. How to manage the overwrite of this Excel
Sheet 'xxxx' every hour automatically?
Thanks,
Martin.Add a Execute SQL task like this
drop table `xxxx`
go
CREATE TABLE `xxxx` (
`FieldName1` FieldType1,
`FieldName2` FieldType2
)
Francesco Anti
"martin" <martin@.server.sk> wrote in message
news:eS0sc1shFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a MS SQL 2000 SP4. I need to export a table to Excel sheet every
> hour. I created DTS (Data Transformation Services) packages by wizard.
> It works fine first time but when DTS runs next time it ends with error:
> table 'xxxx' already exist. How to manage the overwrite of this Excel
> Sheet 'xxxx' every hour automatically?
>
> Thanks,
> Martin.
>|||It works fine,
Thank you
Martin
"Francesco Anti" <fanti_@._sicosbt.it> napísal v správe
news:%233EejCthFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Add a Execute SQL task like this
> drop table `xxxx`
> go
> CREATE TABLE `xxxx` (
> `FieldName1` FieldType1,
> `FieldName2` FieldType2
> )
> Francesco Anti
> "martin" <martin@.server.sk> wrote in message
> news:eS0sc1shFHA.1412@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> I have a MS SQL 2000 SP4. I need to export a table to Excel sheet every
>> hour. I created DTS (Data Transformation Services) packages by wizard.
>> It works fine first time but when DTS runs next time it ends with error:
>> table 'xxxx' already exist. How to manage the overwrite of this Excel
>> Sheet 'xxxx' every hour automatically?
>>
>> Thanks,
>> Martin.
>>
>

DTS woes

I've created a DTS package with the data-export wizard and customized it in visual basic (to do some transformations) When I run the code, No errors are logged, but I'm loosing a massive amount of rows on some of my tables. I'm just moving data from one db to another on the same server. When I do this twice with the same source, I get different results. I'm completely lost here :-(Duh! Nothing to to with my DTS-Script. A lot to do with me confusing backup names on my test server. Stupid me.

DTS Wizard...help

I have downloaded SQL Server Express Service Pack 1, SQL Express Management, SQL Express Toolkit, as well at the DTS Wizard and installed all, looked for Program files\Microsoft SQL Server\90\DTS\Binn and it isn't tere, did a search on my cjomputer for DTSWizard.exe and cannot find it. Any suggestions? Thanks

How to get it is covered in the thread below by another user and yes sometimes the wizard will not install with the service pack. Hope this helps.

http://forums.asp.net/thread/1407898.aspx

DTS Wizard SQL 2005 - Enable Identity Insert

I have a large number of Access tables that I need to periodically bring to SQL using the DTS Wizard in SQL 2000 (via Ent. Man). I am only interested in bring over the data from the MS Access tables as I had a separate script (application drive) that would create the SQL tables.

The problem is this: with SQL 2000 when bringing over the tables, the check box for 'Enable Identity Insert' for each of the tables was checked, this worked fine.

With SQL 2005, this checkbox is not checked hence, I have to click 'edit' for each of the table data being brought over and check the 'Enable Identity Insert' box manualy.

My question is whether there is a way to have SQL 2005 have this 'Enable Identity Insert Box' checked by default as it did with SQL 2000.

Thank you,

Ben

We had to turn off the Enable Identity Insert to make it consistent with the default value for Keep Identity property on OLE DB Source.

Unfortunately, there is no way to change this value for more than one table at the time, in the released Yukon version. We already fixed it for SP1 where you would be able to select multiple tables and do some bulk edits on their transfer settings.

Thanks.

|||Thank you for your prompt reply Bob!

Just to confirm, with SP1 for SQL 2005, we will be able to do bulk edits and change the transfer settings, include the 'Enable Identity Insert'?

Thanks again.|||Yes, you will be able to set Enable Identity Insert for more than one table at once, and plus bulk edit a few other parameters (Drop and Recreate Table, Truncate Table and destination schemas).|||Thank you very much for your response Bob!

Any ideas as to when the service pack will be available?

Thanks!|||I am not sure if an official date is published yet, but it should be in the first half of the next year.|||Appreciate it Bob!

Cheers.|||

I am having a very strange problem using the DTS Wizard. When I set Enable Identity Insert, the actual ID's on the source server are being changed by the target table. It is my understanding that when we set identity insert to on that the identity column on the target database will accept whatever ID is being passed to it.

It is not working that way with SP1 applied. But it used to work. Am I doing something wrong?

Any help would be apprieciated.

Thanks.

|||I'm having the same problem exporting from SQL 2005 to SQL 2000. It doesn't matter whether "Optimize for Many Tables" is checked or not, even if I am only copying 1 table- the identity values are not preserved.|||Just checking whether you have updated to the latest 2005 SP1. This should let you select batch tables and change the Identity insert properties for the DTS.|||Yes, I am using SQL Studio with SP1.|||i have also the same problem, is any one know how to resolve that issue ?|||

It doesn't seem to work. It isn't changing my source tables, but it definately won't insert the appropriate value into the target table. I'm using sp 1 as well.

Does anyone from Microsoft plan to address this?

DTS Wizard SQL 2005 - Enable Identity Insert

I have a large number of Access tables that I need to periodically bring to SQL using the DTS Wizard in SQL 2000 (via Ent. Man). I am only interested in bring over the data from the MS Access tables as I had a separate script (application drive) that would create the SQL tables.

The problem is this: with SQL 2000 when bringing over the tables, the check box for 'Enable Identity Insert' for each of the tables was checked, this worked fine.

With SQL 2005, this checkbox is not checked hence, I have to click 'edit' for each of the table data being brought over and check the 'Enable Identity Insert' box manualy.

My question is whether there is a way to have SQL 2005 have this 'Enable Identity Insert Box' checked by default as it did with SQL 2000.

Thank you,

Ben

We had to turn off the Enable Identity Insert to make it consistent with the default value for Keep Identity property on OLE DB Source.

Unfortunately, there is no way to change this value for more than one table at the time, in the released Yukon version. We already fixed it for SP1 where you would be able to select multiple tables and do some bulk edits on their transfer settings.

Thanks.

|||Thank you for your prompt reply Bob!

Just to confirm, with SP1 for SQL 2005, we will be able to do bulk edits and change the transfer settings, include the 'Enable Identity Insert'?

Thanks again.|||Yes, you will be able to set Enable Identity Insert for more than one table at once, and plus bulk edit a few other parameters (Drop and Recreate Table, Truncate Table and destination schemas).|||Thank you very much for your response Bob!

Any ideas as to when the service pack will be available?

Thanks!

|||I am not sure if an official date is published yet, but it should be in the first half of the next year.|||Appreciate it Bob!

Cheers.|||

I am having a very strange problem using the DTS Wizard. When I set Enable Identity Insert, the actual ID's on the source server are being changed by the target table. It is my understanding that when we set identity insert to on that the identity column on the target database will accept whatever ID is being passed to it.

It is not working that way with SP1 applied. But it used to work. Am I doing something wrong?

Any help would be apprieciated.

Thanks.

|||I'm having the same problem exporting from SQL 2005 to SQL 2000. It doesn't matter whether "Optimize for Many Tables" is checked or not, even if I am only copying 1 table- the identity values are not preserved.
|||Just checking whether you have updated to the latest 2005 SP1. This should let you select batch tables and change the Identity insert properties for the DTS.
|||Yes, I am using SQL Studio with SP1.|||i have also the same problem, is any one know how to resolve that issue ?|||

It doesn't seem to work. It isn't changing my source tables, but it definately won't insert the appropriate value into the target table. I'm using sp 1 as well.

Does anyone from Microsoft plan to address this?

|||Enable Identity Insert Still does not work when you set it for multiple tables! When will this be fixed?

DTS Wizard SQL 2005 - Enable Identity Insert

I have a large number of Access tables that I need to periodically bring to SQL using the DTS Wizard in SQL 2000 (via Ent. Man). I am only interested in bring over the data from the MS Access tables as I had a separate script (application drive) that would create the SQL tables.

The problem is this: with SQL 2000 when bringing over the tables, the check box for 'Enable Identity Insert' for each of the tables was checked, this worked fine.

With SQL 2005, this checkbox is not checked hence, I have to click 'edit' for each of the table data being brought over and check the 'Enable Identity Insert' box manualy.

My question is whether there is a way to have SQL 2005 have this 'Enable Identity Insert Box' checked by default as it did with SQL 2000.

Thank you,

Ben

We had to turn off the Enable Identity Insert to make it consistent with the default value for Keep Identity property on OLE DB Source.

Unfortunately, there is no way to change this value for more than one table at the time, in the released Yukon version. We already fixed it for SP1 where you would be able to select multiple tables and do some bulk edits on their transfer settings.

Thanks.

|||Thank you for your prompt reply Bob!

Just to confirm, with SP1 for SQL 2005, we will be able to do bulk edits and change the transfer settings, include the 'Enable Identity Insert'?

Thanks again.|||Yes, you will be able to set Enable Identity Insert for more than one table at once, and plus bulk edit a few other parameters (Drop and Recreate Table, Truncate Table and destination schemas).|||Thank you very much for your response Bob!

Any ideas as to when the service pack will be available?

Thanks!|||I am not sure if an official date is published yet, but it should be in the first half of the next year.|||Appreciate it Bob!

Cheers.|||

I am having a very strange problem using the DTS Wizard. When I set Enable Identity Insert, the actual ID's on the source server are being changed by the target table. It is my understanding that when we set identity insert to on that the identity column on the target database will accept whatever ID is being passed to it.

It is not working that way with SP1 applied. But it used to work. Am I doing something wrong?

Any help would be apprieciated.

Thanks.

|||I'm having the same problem exporting from SQL 2005 to SQL 2000. It doesn't matter whether "Optimize for Many Tables" is checked or not, even if I am only copying 1 table- the identity values are not preserved.|||Just checking whether you have updated to the latest 2005 SP1. This should let you select batch tables and change the Identity insert properties for the DTS.|||Yes, I am using SQL Studio with SP1.|||i have also the same problem, is any one know how to resolve that issue ?|||

It doesn't seem to work. It isn't changing my source tables, but it definately won't insert the appropriate value into the target table. I'm using sp 1 as well.

Does anyone from Microsoft plan to address this?

DTS Wizard SQL 2005 - Enable Identity Insert

I have a large number of Access tables that I need to periodically bring to SQL using the DTS Wizard in SQL 2000 (via Ent. Man). I am only interested in bring over the data from the MS Access tables as I had a separate script (application drive) that would create the SQL tables.

The problem is this: with SQL 2000 when bringing over the tables, the check box for 'Enable Identity Insert' for each of the tables was checked, this worked fine.

With SQL 2005, this checkbox is not checked hence, I have to click 'edit' for each of the table data being brought over and check the 'Enable Identity Insert' box manualy.

My question is whether there is a way to have SQL 2005 have this 'Enable Identity Insert Box' checked by default as it did with SQL 2000.

Thank you,

Ben

We had to turn off the Enable Identity Insert to make it consistent with the default value for Keep Identity property on OLE DB Source.

Unfortunately, there is no way to change this value for more than one table at the time, in the released Yukon version. We already fixed it for SP1 where you would be able to select multiple tables and do some bulk edits on their transfer settings.

Thanks.

|||Thank you for your prompt reply Bob!

Just to confirm, with SP1 for SQL 2005, we will be able to do bulk edits and change the transfer settings, include the 'Enable Identity Insert'?

Thanks again.|||Yes, you will be able to set Enable Identity Insert for more than one table at once, and plus bulk edit a few other parameters (Drop and Recreate Table, Truncate Table and destination schemas).|||Thank you very much for your response Bob!

Any ideas as to when the service pack will be available?

Thanks!|||I am not sure if an official date is published yet, but it should be in the first half of the next year.|||Appreciate it Bob!

Cheers.|||

I am having a very strange problem using the DTS Wizard. When I set Enable Identity Insert, the actual ID's on the source server are being changed by the target table. It is my understanding that when we set identity insert to on that the identity column on the target database will accept whatever ID is being passed to it.

It is not working that way with SP1 applied. But it used to work. Am I doing something wrong?

Any help would be apprieciated.

Thanks.

|||I'm having the same problem exporting from SQL 2005 to SQL 2000. It doesn't matter whether "Optimize for Many Tables" is checked or not, even if I am only copying 1 table- the identity values are not preserved.|||Just checking whether you have updated to the latest 2005 SP1. This should let you select batch tables and change the Identity insert properties for the DTS.|||Yes, I am using SQL Studio with SP1.|||i have also the same problem, is any one know how to resolve that issue ?|||

It doesn't seem to work. It isn't changing my source tables, but it definately won't insert the appropriate value into the target table. I'm using sp 1 as well.

Does anyone from Microsoft plan to address this?

DTS Wizard SQL 2005 - Enable Identity Insert

I have a large number of Access tables that I need to periodically bring to SQL using the DTS Wizard in SQL 2000 (via Ent. Man). I am only interested in bring over the data from the MS Access tables as I had a separate script (application drive) that would create the SQL tables.

The problem is this: with SQL 2000 when bringing over the tables, the check box for 'Enable Identity Insert' for each of the tables was checked, this worked fine.

With SQL 2005, this checkbox is not checked hence, I have to click 'edit' for each of the table data being brought over and check the 'Enable Identity Insert' box manualy.

My question is whether there is a way to have SQL 2005 have this 'Enable Identity Insert Box' checked by default as it did with SQL 2000.

Thank you,

Ben

We had to turn off the Enable Identity Insert to make it consistent with the default value for Keep Identity property on OLE DB Source.

Unfortunately, there is no way to change this value for more than one table at the time, in the released Yukon version. We already fixed it for SP1 where you would be able to select multiple tables and do some bulk edits on their transfer settings.

Thanks.

|||Thank you for your prompt reply Bob!

Just to confirm, with SP1 for SQL 2005, we will be able to do bulk edits and change the transfer settings, include the 'Enable Identity Insert'?

Thanks again.|||Yes, you will be able to set Enable Identity Insert for more than one table at once, and plus bulk edit a few other parameters (Drop and Recreate Table, Truncate Table and destination schemas).|||Thank you very much for your response Bob!

Any ideas as to when the service pack will be available?

Thanks!

|||I am not sure if an official date is published yet, but it should be in the first half of the next year.|||Appreciate it Bob!

Cheers.|||

I am having a very strange problem using the DTS Wizard. When I set Enable Identity Insert, the actual ID's on the source server are being changed by the target table. It is my understanding that when we set identity insert to on that the identity column on the target database will accept whatever ID is being passed to it.

It is not working that way with SP1 applied. But it used to work. Am I doing something wrong?

Any help would be apprieciated.

Thanks.

|||I'm having the same problem exporting from SQL 2005 to SQL 2000. It doesn't matter whether "Optimize for Many Tables" is checked or not, even if I am only copying 1 table- the identity values are not preserved.
|||Just checking whether you have updated to the latest 2005 SP1. This should let you select batch tables and change the Identity insert properties for the DTS.
|||Yes, I am using SQL Studio with SP1.|||i have also the same problem, is any one know how to resolve that issue ?|||

It doesn't seem to work. It isn't changing my source tables, but it definately won't insert the appropriate value into the target table. I'm using sp 1 as well.

Does anyone from Microsoft plan to address this?

DTS Wizard question

Hi. We're trying to use the DTSWizard.exe that comes with the SQL Server 2005 client tools to move a variety of database objects from one db to another. However, it seems to be limited to only allowing tables or running a SQL script. The SQL 2000 DTS wizard enabled the copying of all database objects. Are we missing something or is this limitation in the 2005 DTS Wizard by intent? Thanks.

You're correct. DTSWizard is primarily for data import/export.

DTS was not upgraded for the new SQL 2005 objects and file definitions. You will have to use SSIS (the replacement for DTS) withSQL 2005 for advanced ETL and database migrations.

|||

arc_dev wrote:

Hi. We're trying to use the DTSWizard.exe that comes with the SQL Server 2005 client tools to move a variety of database objects from one db to another. However, it seems to be limited to only allowing tables or running a SQL script. The SQL 2000 DTS wizard enabled the copying of all database objects. Are we missing something or is this limitation in the 2005 DTS Wizard by intent? Thanks.

I believe it is not there any more...

http://www.databasejournal.com/features/mssql/article.php/3594201

|||

Mahammed,

The DTS Wizard is alive and well. However, it only imports/exports data. It does not import/export and schema objects -as the article you referenced also indicated.

You 'might' want to check using DTSWizard.exe, located in the folder:

{installLocation}\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe

DTS Wizard fails to retrieve long data

We are trying to import data into SQL Server 2005 from MySQL 5.0.x using DTS Wizard via MySQLOLE DB Provider developed by Cherry City Software .

We can read BLOB data from MySQL via ADO 2.7 and ADO.NET 2.0. We can also use DTS Wizard to read the data on Preview. For the test, we defined a table as tBlob (lText VARCHAR(9000)) and inserted one record with a few characters. However, DTS Wizard always fails at the executing step with the following error messages:

Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80040E21.
(SQL Server Import and Export Wizard)

Error 0xc0208265: Data Flow Task: Failed to retrieve long data for column "lText".
(SQL Server Import and Export Wizard)

Error 0xc020901c: Data Flow Task: There was an error with output column "lText" (17) on output "OLE DB Source Output" (11). The column status returned was: "DBSTATUS_UNAVAILABLE".
(SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task: The "output column "lText" (17)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "lText" (17)" specifies failure on error. An error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - tblob" (1) returned error code 0xC0209029. 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.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
(SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: 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.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
(SQL Server Import and Export Wizard)

This could be a defect in the DTS Wizard. How do we workaround this problem? Any help will be greatly appreciated.

Moving to the Is forum|||Unfortunately, the OLE DB error information available doesn't provide enough information to pinpoint the problem. Since you were able to use ADO.Net, you should trying using the DataReader Source adapter (it uses ADO.Net) instead of the OLE DB Source adapter.|||I should have said that I was able to use OleDb.Net and ADO 2.7+ as well to read the data back. With OleDb.Net, I was able to the data via OleDbDataReader.The weird thing is that the DTS Wizard can read the data on the preview. Why can't it read data on executing step?

DTS Wizard fails to retrieve long data

We are trying to import data into SQL Server 2005 from MySQL 5.0.x using DTS Wizard via MySQLOLE DB Provider developed by Cherry City Software .

We can read BLOB data from MySQL via ADO 2.7 and ADO.NET 2.0. We can also use DTS Wizard to read the data on Preview. For the test, we defined a table as tBlob (lText VARCHAR(9000)) and inserted one record with a few characters. However, DTS Wizard always fails at the executing step with the following error messages:

Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80040E21.
(SQL Server Import and Export Wizard)

Error 0xc0208265: Data Flow Task: Failed to retrieve long data for column "lText".
(SQL Server Import and Export Wizard)

Error 0xc020901c: Data Flow Task: There was an error with output column "lText" (17) on output "OLE DB Source Output" (11). The column status returned was: "DBSTATUS_UNAVAILABLE".
(SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task: The "output column "lText" (17)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "lText" (17)" specifies failure on error. An error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - tblob" (1) returned error code 0xC0209029. 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.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
(SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: 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.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
(SQL Server Import and Export Wizard)

This could be a defect in the DTS Wizard. How do we workaround this problem? Any help will be greatly appreciated.

Moving to the Is forum|||Unfortunately, the OLE DB error information available doesn't provide enough information to pinpoint the problem. Since you were able to use ADO.Net, you should trying using the DataReader Source adapter (it uses ADO.Net) instead of the OLE DB Source adapter.|||I should have said that I was able to use OleDb.Net and ADO 2.7+ as well to read the data back. With OleDb.Net, I was able to the data via OleDbDataReader.The weird thing is that the DTS Wizard can read the data on the preview. Why can't it read data on executing step?

DTS Wizard - Buggy?

Greetings,
I have been trying to use the DTS Import Wizard to import data, but SQL Server seems to have a bug that Microsoft has not yet addressed (or have they?)
Basically, when I import data from a text file into one of my existing databases that contains a field with an identity, I run into problems. Basically, SQL gives an error and refuses to import my data if the column is set to having an identity. In the Column Mappings, I set that row to "ignore." I also tried checking Enable Identity Insert, and I tried unchecking it. Nothing works. I receive this error message: "...The statement has been terminated. Cannot insert the value NULL into column 'MovieID', table 'myproject.dbo.MovieStars'; column does not allow nulls. INSERT fails."
I thought that checking "Enable Identity Insert" would automatically tell DTS to insert an incremental identity value.
When I use this transformation code, it doesn't work either:
Function Main()
DTSDestination("MovieName") = DTSSource("Col001")
DTSDestination("MovieStar") = DTSSource("Col002")
Main = DTSTransformStat_OK
End Function
This gives me a different but similar error: "Insert error, column 1, ('MovieID', DBTYPE_I4), status 10: Integrity violation; attempt insert NULL data or data which violates constraints. Unspecified error"
I think this is a SQL Server bug. My google searches have revealed MANY people with this similar problem, but their problem almost never gets answered. If anyone could please offer assistance, that would be great, as my head is turning black and blue from banging it on my desk for the past 3 days.
ThanksI don't think DTS is buggy you are using text file which have empty columns but IDENTITY is a primary key which cannot accept NULL, so I would import the data to a table without IDENTITY column and no key before moving it with an INSERT INTO statement to your destination table. The integrity violation is also primary key related because ANSI SQL rule primary key cannot accept NULL value but foreign key can. Yes your table will be denormalized but INSERT INTO will move the data to your destination table. Hope this helps.|||Thank you, your post is solely responsible for me getting around the problem. I put it into a temp table first, then into the main table. Thanks again!|||I am glad you got it working.

DTS to .net

You know how the DTS wizard will allow one to save as VB...is there some
sort of upgrade that will generate .net code (heaven forbid, c#)?
Second question. Does one generated this code and, assumedly, compiled
it in order to batch file or otherwise script it and is there a
performance degradation by going outside the SQL Server engine?
thx
md
*** Sent via Developersdex http://www.codecomments.com ***
> You know how the DTS wizard will allow one to save as VB...is there some
> sort of upgrade that will generate .net code (heaven forbid, c#)?
AFAIK not. VS.NET uprades VB6 code to VB.NET, but you won't like the code.
You can check this guide how to use DTS in .NET:
http://sqldev.net/dts/DotNETCookBook.htm.

> Second question. Does one generated this code and, assumedly, compiled
> it in order to batch file or otherwise script it and is there a
> performance degradation by going outside the SQL Server engine?
>
DTS uses own objects, the execution is independent of SQL engine, so it
really does not matter where you execute it.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com