Importing a DTS from Sql 2000 to Sql 2005

Discussion:

(too old to reply)

2009-03-11 15:47:01 UTC

Hi,

I have a SSIS package that contains a DTS 2000 package in it.
The DTS 2000 package imports data from an accpac file into several tables
from an ODBC data source.
When I execute the package through BIDS or through Microsoft Sql Server
Management Studio, no problems.
Everything works great. I am now trying to execute the SSIS package via a
job and it gives me the following error:

Started: 11:15:15 AM

Error: 2009-03-11 11:15:16.44
Code: 0x00000000
Source: AMC_COPY_OE61BTXT_FROM_ACCPAC_TO_SQLSERVER
Description: System.Runtime.InteropServices.COMException (0x80040427)
Execution was canceled by user.
at DTS.PackageClass.Execute()
at
Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()
End Error

DTExec: The package execution returned DTSER_FAILURE (1).
Started: 11:15:15 AM
Finished: 11:15:16 AM
Elapsed: 1.14 seconds.
The package execution failed.
The step failed.

In my OBDC Data source i dont use a map drive but a UNC path
The accpac file that is read by the DTS is on another server
Also the owner of the SSIS is da and the user than run the Sql Server Agent
is also da
da is a domain administrator
For now I use this user because he should have rights to go every where on
the domain
Once everything will work I will change the user by another NT User with the
proper rights

Those somebody have an idea of the problem and how to solve it?

Thanks in advance

Todd C

2009-03-11 21:22:11 UTC

Permalink

What is the Service Account running under? When scheduled as a job, even
though the job was created by 'da', and the SSIS package was desinged by 'da'
and 'da' is all over the place, it is still the Service Account rights that
are applied to the external resource.

If you need to, create a Credentials, and a Proxy to use those Credentials.
Then run your SQL Agent job under the rights of the Proxy account.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

Post by GC
Hi,
I have a SSIS package that contains a DTS 2000 package in it.
The DTS 2000 package imports data from an accpac file into several tables
from an ODBC data source.
When I execute the package through BIDS or through Microsoft Sql Server
Management Studio, no problems.
Everything works great. I am now trying to execute the SSIS package via a
Started: 11:15:15 AM
Error: 2009-03-11 11:15:16.44
Code: 0x00000000
Source: AMC_COPY_OE61BTXT_FROM_ACCPAC_TO_SQLSERVER
Description: System.Runtime.InteropServices.COMException (0x80040427)
Execution was canceled by user.
at DTS.PackageClass.Execute()
at
Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 11:15:15 AM
Finished: 11:15:16 AM
Elapsed: 1.14 seconds.
The package execution failed.
The step failed.
In my OBDC Data source i dont use a map drive but a UNC path
The accpac file that is read by the DTS is on another server
Also the owner of the SSIS is da and the user than run the Sql Server Agent
is also da
da is a domain administrator
For now I use this user because he should have rights to go every where on
the domain
Once everything will work I will change the user by another NT User with the
proper rights
Those somebody have an idea of the problem and how to solve it?
Thanks in advance

2009-03-12 13:25:02 UTC

Permalink

Hi,

Thanks for the reply.

The service account I mean the Sql Server Agent account is da which is a NT
administrator account of the domaine. The job is also created by da and when
I created the job I was logging on as da on the computer.

I realise that when I import with the wizard my SQL 2000 Server DTS in SQl
Server 2005 my DTS is embedded in a package. Yesterday I read that Sql
Server 2005 need some files from sql2000 Server to run those packages coming
from Sql 2000 server

This is the article I read
http://support.microsoft.com/kb/904796

Is that could be my problem?
Because I also read 4 different post from 4 different person that install
those files and it did not resolve the problem.

In fact I am in a study case to evaluate how much time it would take to
convert all the DTS of one of our customer from SQl Server 2000 to Sql
Server 2005.
We have 30 DTS to convert.
I already convert one of them. Everything work except when I schedule the
SSIS in a job. It is the only problem I have.

But it seems that a lot of programmers have this problem when we import DTS
from SQL Server 2000 to Sql Server 2005.

What do you think?

Post by Todd C
What is the Service Account running under? When scheduled as a job, even
though the job was created by 'da', and the SSIS package was desinged by 'da'
and 'da' is all over the place, it is still the Service Account rights that
are applied to the external resource.
If you need to, create a Credentials, and a Proxy to use those Credentials.
Then run your SQL Agent job under the rights of the Proxy account.
HTH
--
Todd C
[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

Todd C

2009-03-12 19:19:03 UTC

Permalink

Considering that DTS will not be supported in SQL much longer, and that SSIS
is such a drasticaly improved product, I would suggest taking the plunge and
trying to convert your DTS packages to SSIS. (But then again, I don't know
how complex the DTS stuff is that you have.)
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

Post by GC
Hi,
Thanks for the reply.
The service account I mean the Sql Server Agent account is da which is a NT
administrator account of the domaine. The job is also created by da and when
I created the job I was logging on as da on the computer.
I realise that when I import with the wizard my SQL 2000 Server DTS in SQl
Server 2005 my DTS is embedded in a package. Yesterday I read that Sql
Server 2005 need some files from sql2000 Server to run those packages coming
from Sql 2000 server
This is the article I read
http://support.microsoft.com/kb/904796
Is that could be my problem?
Because I also read 4 different post from 4 different person that install
those files and it did not resolve the problem.
In fact I am in a study case to evaluate how much time it would take to
convert all the DTS of one of our customer from SQl Server 2000 to Sql
Server 2005.
We have 30 DTS to convert.
I already convert one of them. Everything work except when I schedule the
SSIS in a job. It is the only problem I have.
But it seems that a lot of programmers have this problem when we import DTS
from SQL Server 2000 to Sql Server 2005.
What do you think?

itsdeshpande

2009-03-18 14:34:20 UTC

Permalink

HI GC,

I have the same problem too. Still in the process of investigating it though,
did you get through this issue??

Did you try checking the "Execute on main package thread DTS Package" in the
DTS 2000 Package's WorkFlow Properties?

Also, if your servers' security is configured for SQL Server, can you try
changing it to Windows Authentication?

Let me know if this works, since I am also in the process of solving this.. :-
)

Thanks,
Manoj Deshpande.

Post by Todd C
Considering that DTS will not be supported in SQL much longer, and that SSIS
is such a drasticaly improved product, I would suggest taking the plunge and
trying to convert your DTS packages to SSIS. (But then again, I don't know
how complex the DTS stuff is that you have.)

Hi,

[quoted text clipped - 80 lines]

Post by GC
Thanks in advance

2009-04-03 14:39:01 UTC

Permalink

Hi Manoj,

Sorry for the delay for answering to your message but I stop for a while
working on this issue. I decide to learn more about security and context to
understand what is exactly happening.

Thanks for the hint.
I tried both of your suggestion and still does not work.

I am searching a way to log everything of what is executed by the job to see
exactly where it fails and for what reason.

So I'm still working on my problem.
I am not an expert so thats is why it is long to find the problem :)

What about you did you find something on your side?

Thanks in advance and have a nice day!

Post by itsdeshpande
HI GC,
I have the same problem too. Still in the process of investigating it though,
did you get through this issue??
Did you try checking the "Execute on main package thread DTS Package" in the
DTS 2000 Package's WorkFlow Properties?
Also, if your servers' security is configured for SQL Server, can you try
changing it to Windows Authentication?
Let me know if this works, since I am also in the process of solving this.. :-
)
Thanks,
Manoj Deshpande.

Hi,

[quoted text clipped - 80 lines]

Post by GC
Thanks in advance

itsdeshpande via SQLMonster.com

2009-04-07 08:55:14 UTC

Permalink

Hi DC,

Thanks for the reply.

Well, my problem solved; but I think its just one of the cause of this kind
of error. I found that the TempDB and the user DB that I was working on were
not set to Autogrow and also there was a bit of disk space issue too. I set
them to autogrow and cleared some space on the data and log drives of these
DB's and I could get the package working.

In another environment, they fixed it by adding the service account running
the package into the SQL Logins explicitly (meaning, though this account was
a local admin; inspite of that) of that server to which this 2000 DTS package
was connecting.

You may want to try these..

Thanks,
Manoj Deshpande.

Post by GC
Hi Manoj,
Sorry for the delay for answering to your message but I stop for a while
working on this issue. I decide to learn more about security and context to
understand what is exactly happening.
Thanks for the hint.
I tried both of your suggestion and still does not work.
I am searching a way to log everything of what is executed by the job to see
exactly where it fails and for what reason.
So I'm still working on my problem.
I am not an expert so thats is why it is long to find the problem :)
What about you did you find something on your side?
Thanks in advance and have a nice day!

Post by itsdeshpande
HI GC,

[quoted text clipped - 22 lines]

Post by itsdeshpande

Post by GC
Thanks in advance

--
Message posted via http://www.sqlmonster.com

Continue reading on narkive:

Search results for 'Importing a DTS from Sql 2000 to Sql 2005' (Questions and Answers)

replies

Can someone help me with some basic SQL code?

started 2007-03-14 07:54:31 UTC

programming & design

replies