DTS schedult problem

I created a DTS package to transfer a table to a text file. Every time when
I click start manually, it'll run and transfer the data to a local txt file
with no problem. But when I schedule this DTS package and run it from the
SQL Agent - Jobs. It always fail to run. I don't know why. Anybody can help
me?|
| I created a DTS package to transfer a table to a text file. Every time
when
| I click start manually, it'll run and transfer the data to a local txt
file
| with no problem. But when I schedule this DTS package and run it from the
| SQL Agent - Jobs. It always fail to run. I don't know why. Anybody can
help
| me?
--
We need more information to help you. What errors are returned when the job
fails? Post it and we'll give it a shot.
Thanks,
--
Eric Cardenas
SQL Server support|||JL
I think that you cerated DTS package on the workstation not on the server ,
then SQL Agent on your wokstation is running under differnent account than
server.
Check it out.
"JL" <ljmagzine@.hotmail.com> wrote in message
news:eGGt1AgvDHA.2304@.TK2MSFTNGP12.phx.gbl...
> I created a DTS package to transfer a table to a text file. Every time
when
> I click start manually, it'll run and transfer the data to a local txt
file
> with no problem. But when I schedule this DTS package and run it from the
> SQL Agent - Jobs. It always fail to run. I don't know why. Anybody can
help
> me?
>|||VXJpIGlzIGNvcnJlY3QuICBJIHJhbiBpbnRvIHRoaXMgcHJvYmxlbSBhbmQgaXQgaGFkIG1lIHN0
dW1wZWQgZm9yIGEgd2hpbGUuDQoNClRoZSBzb2x1dGlvbiBpcyB0byBjcmVhdGUgdGhlIERUUyBw
YWNrYWdlIGFuZCBKb2Igd2hpbGUgcGh5c2ljYWxseSBhdCB0aGUgU1FMIFNlcnZlciBib3guDQoN
Cg0KIkpMIiA8bGptYWd6aW5lQGhvdG1haWwuY29tPiB3cm90ZSBpbiBtZXNzYWdlIG5ld3M6ZUdH
dDFBZ3ZESEEuMjMwNEBUSzJNU0ZUTkdQMTIucGh4LmdibC4uLg0KPiBJIGNyZWF0ZWQgYSBEVFMg
cGFja2FnZSB0byB0cmFuc2ZlciBhIHRhYmxlIHRvIGEgdGV4dCBmaWxlLiBFdmVyeSB0aW1lIHdo
ZW4NCj4gSSBjbGljayBzdGFydCBtYW51YWxseSwgaXQnbGwgcnVuIGFuZCB0cmFuc2ZlciB0aGUg
ZGF0YSB0byBhIGxvY2FsIHR4dCBmaWxlDQo+IHdpdGggbm8gcHJvYmxlbS4gQnV0IHdoZW4gSSBz
Y2hlZHVsZSB0aGlzIERUUyBwYWNrYWdlIGFuZCBydW4gaXQgZnJvbSB0aGUNCj4gU1FMIEFnZW50
IC0gSm9icy4gSXQgYWx3YXlzIGZhaWwgdG8gcnVuLiBJIGRvbid0IGtub3cgd2h5LiBBbnlib2R5
IGNhbiBoZWxwDQo+IG1lPw0KPiANCj4gsqlsql

DTS Scheduled Time

How can I tell when a DTS package is scheduled to run?
If I right click on the name, and select "Schedule Package", it brings up
default data (12:00 am Daily), and I KNOW that's not the scheduled time.
ThanksThat is the mechanism by which you set up a scheduled job via SQL Agent. By alter the parameters of the screen to the schedule you desire and clicking on OK, the GIU will build a scheduled job under the AGent with the same name as the DTS package.|||Now I see it. Thanks

dts prob

Whenever i right click on an open dts and
choose "disconnect edit" i get this error:
Error Souce: Microsoft OLE DB Service Components
Error Description: Class not registered
Class not registered
Can anyone tell me why? How to i fix this?
Thanxs
> Whenever i right click on an open dts and
> choose "disconnect edit" i get this error:
> Error Souce: Microsoft OLE DB Service Components
> Error Description: Class not registered
> Class not registered
> Can anyone tell me why? How to i fix this?
> Thanxs
Reinstall MDAC and see how that goes. If that fails, get a regmon and
filemon capture to determine what dll is missing/unregistered.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

dts prob

Whenever i right click on an open dts and
choose "disconnect edit" i get this error:
Error Souce: Microsoft OLE DB Service Components
Error Description: Class not registered
Class not registered
Can anyone tell me why? How to i fix this'
Thanxs> Whenever i right click on an open dts and
> choose "disconnect edit" i get this error:
> Error Souce: Microsoft OLE DB Service Components
> Error Description: Class not registered
> Class not registered
> Can anyone tell me why? How to i fix this'
> Thanxs
--
Reinstall MDAC and see how that goes. If that fails, get a regmon and
filemon capture to determine what dll is missing/unregistered.
Hope this helps,
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.sqlsql

dts prob

Whenever i right click on an open dts and
choose "disconnect edit" i get this error:
Error Souce: Microsoft OLE DB Service Components
Error Description: Class not registered
Class not registered
Can anyone tell me why? How to i fix this'
Thanxs> Whenever i right click on an open dts and
> choose "disconnect edit" i get this error:
> Error Souce: Microsoft OLE DB Service Components
> Error Description: Class not registered
> Class not registered
> Can anyone tell me why? How to i fix this'
> Thanxs
--
Reinstall MDAC and see how that goes. If that fails, get a regmon and
filemon capture to determine what dll is missing/unregistered.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

DTS Permissions

Assuming you can open the package, simply click on
package in the upper left and save as...
>--Original Message--
>We have a DTS package that was created and saved by
>someone who is no longer with the company. How can we
>convert this DTS package to a valid ID? Thanks
>.
>try sp_reassign_dtspackageowner
Olu Adedeji
"Peter Cwik" <anonymous@.discussions.microsoft.com> wrote in message
news:155bb01c3fa49$ecf965f0$a601280a@.phx
.gbl...
> Assuming you can open the package, simply click on
> package in the upper left and save as...

DTS migration wizard, where does it put migrated version?

From SQL Server Management Studio I right click on Management/Legacy/DTS and select Migration Wizard and enter a SQL Server Source and Destination. A list of DTS packages from the source server is displayed and I select one of the DTS packages and give the destination a unique name. The wizard runs and says it's successful. I can't find the new SSIS version of the DTS package with the new name that I've given it, however. Where does it put the new migrated copy of the DTS package.

Thanks,

John

In Sql Management Studio, under the Object Explorer window in the Connect drop down choose Integration Services, and it will show you all stored packages on that instance.|||Thanks, this helps. Now I'd like to find out how to edit the migrated version. How to I get to it from BI Dev Studio?|||

All of the stored packages will usually be in ~\Program Files\Microsoft SQL Server\90\DTS\Packages

DTS migration from ss2000 --> ss2005

Pablo
Right Click on Data Transformation Services and choose Migration Wizard
"Pablo Cognito" <pcognito@.gmail.com> wrote in message
news:uXs7OOJDHHA.3660@.TK2MSFTNGP02.phx.gbl...
> hi,
> I'm planning to move my existing DTS that I've made in ss2000 to ss2005.
> At this moment I'm not planning to migrate existing DTS to SSIS, I just
> want to use backward compatibility for now.
> What's the "best practice" technique for moving ss2000 DTS --> ss2005?
> Thanks
Sorry, firstly save the DTS as stucture storage file
secondly Right Click on Data Transformation Services and choose Import
Package File option
choose Import option
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OMMOegJDHHA.3492@.TK2MSFTNGP06.phx.gbl...
> Pablo
> Right Click on Data Transformation Services and choose Migration Wizard
>
> "Pablo Cognito" <pcognito@.gmail.com> wrote in message
> news:uXs7OOJDHHA.3660@.TK2MSFTNGP02.phx.gbl...
>

DTS migration 2000 to 2005

There are two options to move from 2000 sql server to 2005 sql server
database engine.
1. Double click on the DTS package to open in the designer.
Click file--> save as and change the destination server to SQL server
2005. Then save.
Finally the package will be right clicked in 2005 database
engine and migrated to integration services.
2. Save the DTS package as a structured storage file and import
it in sql server 2005 database engine under management --> legacy -->
Data transformation services.
Finally the package will be right clicked in 2005 database
engine and migrated to integration services.
What is the recommended method out of these two? Please illustrate.
My recommendation would be the second. I would also be careful in testing
what the Migration Wizard does when moving it to an SSIS package. If your DTS
package contains a lot of script components, "odd" things can happen. Since
you can still run and maintain your DTS packages under the Migration section
(just not create new ones), depending on the volume of use of DTS, I often
recommend to not migrate and slowly create new ones in SSIS. SSIS is a
daunting tool compared to the DTS designer, so even maintaining once
converted could be time consuming.
My 2 cents - hope that helps.
"sudesh.withanage@.gmail.com" wrote:

> There are two options to move from 2000 sql server to 2005 sql server
> database engine.
> 1. Double click on the DTS package to open in the designer.
> Click file--> save as and change the destination server to SQL server
> 2005. Then save.
> Finally the package will be right clicked in 2005 database
> engine and migrated to integration services.
>
> 2. Save the DTS package as a structured storage file and import
> it in sql server 2005 database engine under management --> legacy -->
> Data transformation services.
> Finally the package will be right clicked in 2005 database
> engine and migrated to integration services.
>
> What is the recommended method out of these two? Please illustrate.
>

DTS migration 2000 to 2005

There are two options to move from 2000 sql server to 2005 sql server
database engine.
1. Double click on the DTS package to open in the designer.
Click file--> save as and change the destination server to SQL server
2005. Then save.
Finally the package will be right clicked in 2005 database
engine and migrated to integration services.
2. Save the DTS package as a structured storage file and import
it in sql server 2005 database engine under management --> legacy -->
Data transformation services.
Finally the package will be right clicked in 2005 database
engine and migrated to integration services.
What is the recommended method out of these two? Please illustrate.My recommendation would be the second. I would also be careful in testing
what the Migration Wizard does when moving it to an SSIS package. If your DTS
package contains a lot of script components, "odd" things can happen. Since
you can still run and maintain your DTS packages under the Migration section
(just not create new ones), depending on the volume of use of DTS, I often
recommend to not migrate and slowly create new ones in SSIS. SSIS is a
daunting tool compared to the DTS designer, so even maintaining once
converted could be time consuming.
My 2 cents - hope that helps.
"sudesh.withanage@.gmail.com" wrote:
> There are two options to move from 2000 sql server to 2005 sql server
> database engine.
> 1. Double click on the DTS package to open in the designer.
> Click file--> save as and change the destination server to SQL server
> 2005. Then save.
> Finally the package will be right clicked in 2005 database
> engine and migrated to integration services.
>
> 2. Save the DTS package as a structured storage file and import
> it in sql server 2005 database engine under management --> legacy -->
> Data transformation services.
> Finally the package will be right clicked in 2005 database
> engine and migrated to integration services.
>
> What is the recommended method out of these two? Please illustrate.
>|||I agree with James as in my experience the conversion in most cases seems to
be unsuccessful. If you have UDL connections or you use a lot of scripting,
especially scripting of the DTS object model, then all that you can convert
to is a placeholder to execute the DTS 2000 package, so you'll be in pretty
much the same situation either way.
I'd recommend refactoring the DTS packages one by one. We're doing this to
take advantage of the new functionality. There's not much on migration
strategies in BOL, but my 2Ps worth is to spend time creating a generic
template that can be used for all your DTS packages. This can include
centralised event handling and centralised configurations. If you get this
right, the package will work in Dev, Test and Live without any
reconfiguration at all, and you'll be able to monitor all packages
centrally.
Cheers,
Paul Ibison

DTS migration 2000 to 2005

There are two options to move from 2000 sql server to 2005 sql server
database engine.
1. Double click on the DTS package to open in the designer.
Click file--> save as and change the destination server to SQL server
2005. Then save.
Finally the package will be right clicked in 2005 database
engine and migrated to integration services.
2. Save the DTS package as a structured storage file and import
it in sql server 2005 database engine under management --> legacy -->
Data transformation services.
Finally the package will be right clicked in 2005 database
engine and migrated to integration services.
What is the recommended method out of these two? Please illustrate.My recommendation would be the second. I would also be careful in testing
what the Migration Wizard does when moving it to an SSIS package. If your DT
S
package contains a lot of script components, "odd" things can happen. Since
you can still run and maintain your DTS packages under the Migration section
(just not create new ones), depending on the volume of use of DTS, I often
recommend to not migrate and slowly create new ones in SSIS. SSIS is a
daunting tool compared to the DTS designer, so even maintaining once
converted could be time consuming.
My 2 cents - hope that helps.
"sudesh.withanage@.gmail.com" wrote:

> There are two options to move from 2000 sql server to 2005 sql server
> database engine.
> 1. Double click on the DTS package to open in the designer.
> Click file--> save as and change the destination server to SQL server
> 2005. Then save.
> Finally the package will be right clicked in 2005 database
> engine and migrated to integration services.
>
> 2. Save the DTS package as a structured storage file and import
> it in sql server 2005 database engine under management --> legacy -->
> Data transformation services.
> Finally the package will be right clicked in 2005 database
> engine and migrated to integration services.
>
> What is the recommended method out of these two? Please illustrate.
>|||I agree with James as in my experience the conversion in most cases seems to
be unsuccessful. If you have UDL connections or you use a lot of scripting,
especially scripting of the DTS object model, then all that you can convert
to is a placeholder to execute the DTS 2000 package, so you'll be in pretty
much the same situation either way.
I'd recommend refactoring the DTS packages one by one. We're doing this to
take advantage of the new functionality. There's not much on migration
strategies in BOL, but my 2Ps worth is to spend time creating a generic
template that can be used for all your DTS packages. This can include
centralised event handling and centralised configurations. If you get this
right, the package will work in Dev, Test and Live without any
reconfiguration at all, and you'll be able to monitor all packages
centrally.
Cheers,
Paul Ibison

DTS Job fails when scheduled

I have a DTS local package that transfer data from the host SQL server to another. If I run the package directly (i.e. Right click - Execute Package) everything works as it should.

However, if I try and execute this as a Server Agent job it fails, the job history step details shows the following error:

Executed as user: MYSERVERNAME\SYSTEM. ...: Drop table customer Step DTSRun OnError: Drop table customer Step, Error = -2147467259 (80004005) Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 17 (11) Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: Drop table customer Step DTSRun OnStart: Create Table customer Step DTSRun OnError: Create Table customer Step, Error = -2147467259 (80004005) Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Error source: Microsoft OLE DB Provider for SQL Server... Process Exit Code 2. The step failed.

Whilst this is obviously a connection issue, I dont understand how this can work if executed directly and not if executed as an agent job.

Can anybody shed any light on this?

If it helps this is the sequence of events my DTS package uses

1) Connect to remote DB
2) Drop table customers
3) Create new remote customers table
4) Connect to to local DB (hosting the DTS package)
5) Select * from local customers table
6) Dump data from local select into remote DBIt's a frequently asked question. When you open a DTS package from your workstation, the environment is that of your workstation.

When you execute a DTS package from a scheduled job, the environment is that of the server.

If there are differences between the server environment and the environment of your workstation, then the executed package will fail.

For example, if you have Oracle drivers configured on your PC (but not on the server) you will be able to execute the DTS package from your PC, but not from the server.

Try using Terminal Services to remote in to the DB Server. Then open the package and execute it from the server desktop. The errors become more apparent at that point.

Regards,

hmscott|||If you allow mixed mode authentication, create an login on your target server for use as a service account, then define credentials for your individual connections in the "Connection Properties" dialog for each specific server connection you intend to use.

Alternately, you can setup your target server as a linked server and specify which credentials it is to use when operating on the remote server in the "Security -> Linked Servers" node in Enterpise Manager.

DTS job error

Hi,
I scheduled a DTS, with right click above it, for every 2
hours. A job is automatically created to control the
execution that a specified. I save the DTS within SQL
Server with dba user. If i change DBA password, my job
execution reports an error saying, login failed for user
dba ..., i don't have this user within any step regarding
the DTS, why this happens ? if i delete the job and
recreate it using right click above the DTS, then the job
start working fine. Any ideas ?
Thanks a lot
Mike
I would guess that you may have scheduled the packages using
the DTS schedule package functionality - you right click on
the package on select Schedule Package. In this case, the
job will create an encrypted string for the dtsrun utility -
the command in the job step looks like:
DTSRUN /~Zxxxxxxxxxxxx.
The login and password that is encrypted in this string is
based upon what login and password was used in Enterprise
Manager to register the server. If you change the password
that was used when scheduling the package, the password
embedded in the encrypted string is no longer valid.
You can either change the dtsrun commands in the jobs, just
entering the dtsrun parameters yourself.
Or you can use the dtsrunui utility to generate a new
encrypted command. From the advanced button on the dtsrunui
utility, there is an option to generate a command line. It
will be based on whatever login you use when the dtsrunui
utility starts.
-Sue
On Thu, 1 Apr 2004 06:48:24 -0800, "Mike"
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>I scheduled a DTS, with right click above it, for every 2
>hours. A job is automatically created to control the
>execution that a specified. I save the DTS within SQL
>Server with dba user. If i change DBA password, my job
>execution reports an error saying, login failed for user
>dba ..., i don't have this user within any step regarding
>the DTS, why this happens ? if i delete the job and
>recreate it using right click above the DTS, then the job
>start working fine. Any ideas ?
>Thanks a lot
>Mike
|||I guess you are saving the package in msdb.
It uses the username and password to load the package not to run it.
You can create the job yourself and code the dtsrun command so that you can change it. It means that the password will be in clear though.
I prefer to save packages to storage files and load them from there - it nmakes them easier to handle.
|||Thanks a lot Sue, that makes sense, but if i edit EM
properties and specify other user than the user that i
used to save the package, for instance sa, i get the error
login failed for user sa ... ?? This is strange. I've
got errors for all the users i used.
Any idea ?

>--Original Message--
>I would guess that you may have scheduled the packages
using
>the DTS schedule package functionality - you right click
on
>the package on select Schedule Package. In this case, the
>job will create an encrypted string for the dtsrun
utility -
>the command in the job step looks like:
>DTSRUN /~Zxxxxxxxxxxxx.
>The login and password that is encrypted in this string is
>based upon what login and password was used in Enterprise
>Manager to register the server. If you change the password
>that was used when scheduling the package, the password
>embedded in the encrypted string is no longer valid.
>You can either change the dtsrun commands in the jobs,
just
>entering the dtsrun parameters yourself.
>Or you can use the dtsrunui utility to generate a new
>encrypted command. From the advanced button on the
dtsrunui
>utility, there is an option to generate a command line. It
>will be based on whatever login you use when the dtsrunui
>utility starts.
>-Sue
>On Thu, 1 Apr 2004 06:48:24 -0800, "Mike"
><anonymous@.discussions.microsoft.com> wrote:
2
regarding
job
>.
>
|||Sorry, the login failed is for dba you and Nigel are
right, sql server use dba for load the DTS under msdb
database and then fails when i change the password.
Thanks a lot

>--Original Message--
>Thanks a lot Sue, that makes sense, but if i edit EM
>properties and specify other user than the user that i
>used to save the package, for instance sa, i get the
error
>login failed for user sa ... ?? This is strange.
I've
>got errors for all the users i used.
>Any idea ?
>
>using
>on
>utility -
is
password
>just
>dtsrunui
It
>2
user
>regarding
>job
>.
>

DTS job error

Hi,
I scheduled a DTS, with right click above it, for every 2
hours. A job is automatically created to control the
execution that a specified. I save the DTS within SQL
Server with dba user. If i change DBA password, my job
execution reports an error saying, login failed for user
dba ..., i don't have this user within any step regarding
the DTS, why this happens ? if i delete the job and
recreate it using right click above the DTS, then the job
start working fine. Any ideas '
Thanks a lot
MikeI would guess that you may have scheduled the packages using
the DTS schedule package functionality - you right click on
the package on select Schedule Package. In this case, the
job will create an encrypted string for the dtsrun utility -
the command in the job step looks like:
DTSRUN /~Zxxxxxxxxxxxx.
The login and password that is encrypted in this string is
based upon what login and password was used in Enterprise
Manager to register the server. If you change the password
that was used when scheduling the package, the password
embedded in the encrypted string is no longer valid.
You can either change the dtsrun commands in the jobs, just
entering the dtsrun parameters yourself.
Or you can use the dtsrunui utility to generate a new
encrypted command. From the advanced button on the dtsrunui
utility, there is an option to generate a command line. It
will be based on whatever login you use when the dtsrunui
utility starts.
-Sue
On Thu, 1 Apr 2004 06:48:24 -0800, "Mike"
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>I scheduled a DTS, with right click above it, for every 2
>hours. A job is automatically created to control the
>execution that a specified. I save the DTS within SQL
>Server with dba user. If i change DBA password, my job
>execution reports an error saying, login failed for user
>dba ..., i don't have this user within any step regarding
>the DTS, why this happens ? if i delete the job and
>recreate it using right click above the DTS, then the job
>start working fine. Any ideas '
>Thanks a lot
>Mike|||I guess you are saving the package in msdb.
It uses the username and password to load the package not to run it.
You can create the job yourself and code the dtsrun command so that you can
change it. It means that the password will be in clear though.
I prefer to save packages to storage files and load them from there - it nma
kes them easier to handle.|||Thanks a lot Sue, that makes sense, but if i edit EM
properties and specify other user than the user that i
used to save the package, for instance sa, i get the error
login failed for user sa ... '? This is strange. I've
got errors for all the users i used.
Any idea ?

>--Original Message--
>I would guess that you may have scheduled the packages
using
>the DTS schedule package functionality - you right click
on
>the package on select Schedule Package. In this case, the
>job will create an encrypted string for the dtsrun
utility -
>the command in the job step looks like:
>DTSRUN /~Zxxxxxxxxxxxx.
>The login and password that is encrypted in this string is
>based upon what login and password was used in Enterprise
>Manager to register the server. If you change the password
>that was used when scheduling the package, the password
>embedded in the encrypted string is no longer valid.
>You can either change the dtsrun commands in the jobs,
just
>entering the dtsrun parameters yourself.
>Or you can use the dtsrunui utility to generate a new
>encrypted command. From the advanced button on the
dtsrunui
>utility, there is an option to generate a command line. It
>will be based on whatever login you use when the dtsrunui
>utility starts.
>-Sue
>On Thu, 1 Apr 2004 06:48:24 -0800, "Mike"
><anonymous@.discussions.microsoft.com> wrote:
>
2
regarding
job
>.
>|||Sorry, the login failed is for dba you and Nigel are
right, sql server use dba for load the DTS under msdb
database and then fails when i change the password.
Thanks a lot

>--Original Message--
>Thanks a lot Sue, that makes sense, but if i edit EM
>properties and specify other user than the user that i
>used to save the package, for instance sa, i get the
error
>login failed for user sa ... '? This is strange.
I've
>got errors for all the users i used.
>Any idea ?
>
>using
>on
>utility -
is
password
>just
>dtsrunui
It
>2
user
>regarding
>job
>.
>