DTS execute works, Schedule fails

Hey all,

I have created a DTS task that i can right click on and execute and it works fine on the server.
However when i try to schedule the task and run the job from SQL Server Agent, i get the following error.

Error Source= Microsoft VBScript runtime error Error Description: ActiveX component can't create object: 'CuteFTPPro.TEConnection' Error on Line 31

the error occurs on the following line
Set MySite = CreateObject("CuteFTPPro.TEConnection")

How is it possible that i can execute my package but cannot schedule it? i am executing the package from physically sitting at the server.

thanks in advance,
peteI'd say it's gotta be permissions...

When you run a dts package manually, it runs under the context of the client...

When scheduled it runs under the context of the sql server service account...

Also the same thing with the drive mappings

When you run it, it uses yours...

when scheduled it sees the servers drive

screwed up, huh...|||when i was running it manually i was sitting at the server so wouldnt it be running in the context of the server?

the scheduled task is run by the /MYSERVER/SYSTEM user... shouldnt that user have all permissions.

how do i change permissions for the user /MYSERVER/SYSTEM if it doesnt have all permissions.

thanks,
peter

Originally posted by Brett Kaiser
I'd say it's gotta be permissions...

When you run a dts package manually, it runs under the context of the client...

When scheduled it runs under the context of the sql server service account...

Also the same thing with the drive mappings

When you run it, it uses yours...

when scheduled it sees the servers drive

screwed up, huh...

DTS and SQL Server

Is it possible to run Data Transformation Services (DTS) on demand by having a user click on a button in an application with SQl Server?
I am creating a project in vb.net where I want the user to be able to hit an "update" button and data will be updated to my SQL Server table by DTS.

Also is it possible to check for duplicate entries before running DTS? E.g I want to append to my table but exclude duplicate entries UNLESS the data has changed for that entry.>>Is it possible to run Data Transformation Services (DTS) on demand

Yes. http://msdn.microsoft.com/msdnmag/issues/04/01/WebQA/

>>Also is it possible to check for duplicate entries before running DTS

Make this a step in your dts package

DTS

I just downloaded and installed DTS for SQL 2005. Now the million
dollar question
How it I run it is there a button, link, click, dropdown, or setup of
external tools I need to do?
Hi
Check out the information in books online or at
http://msdn2.microsoft.com/en-us/library/ms143706.aspx, hopefully that will
point you in the right direction!
John
"frogman7" wrote:

> I just downloaded and installed DTS for SQL 2005. Now the million
> dollar question
> How it I run it is there a button, link, click, dropdown, or setup of
> external tools I need to do?
>
|||Hi
Check out the information in Books online or at
http://msdn2.microsoft.com/en-us/library/ms143706.aspx hopefully that will
point you in the right direction!
John
"frogman7" wrote:

> I just downloaded and installed DTS for SQL 2005. Now the million
> dollar question
> How it I run it is there a button, link, click, dropdown, or setup of
> external tools I need to do?
>
|||The mySQL DBA told me about DTS and that in SQL 2000 it was a button
click then the dialog box displayed asking for source and destination
of data to move. I added an external tool to this location:
C:\Program Files\Microsoft SQL
Server\90\Tools\Binn\VSShell\Common7\IDE\DTExecUI. exe
Is this the right exe file to use?
|||Hi
DTSRun.exe is the program to use to run SQL 2000 packages DTExecUI.exe
is for SSIS packages.
John
"frogman7" wrote:

> The mySQL DBA told me about DTS and that in SQL 2000 it was a button
> click then the dialog box displayed asking for source and destination
> of data to move. I added an external tool to this location:
> C:\Program Files\Microsoft SQL
> Server\90\Tools\Binn\VSShell\Common7\IDE\DTExecUI. exe
> Is this the right exe file to use?
>
|||I have done something wrong because the only DTSRun.exe just runs
command prompt window and goes away.
This is the path to the file:
C:\Program Files\Microsoft SQL Server\80\Tools\Binn
I am using SQL 2005 and downloaded the 2005 version from MS web site.
Is there a better place to get the file from.
|||Hi
DTSrun is used to run the packages, try DTSRUN /? from a command prompt to
get the command line options. If you have installed SQL Server2005_DTS.msi
then you should have a package called "SQL Server 2000 DTS Designer
Components" in Control Panel's Add/Remove Programs. You will the be able to
access and open packages through the legacy branch in the Object Explorer in
SQL Server Management Studio.
HTH
John
"frogman7" wrote:

> I have done something wrong because the only DTSRun.exe just runs
> command prompt window and goes away.
> This is the path to the file:
> C:\Program Files\Microsoft SQL Server\80\Tools\Binn
> I am using SQL 2005 and downloaded the 2005 version from MS web site.
> Is there a better place to get the file from.
>

DTS

I just downloaded and installed DTS for SQL 2005. Now the million
dollar question
How it I run it is there a button, link, click, dropdown, or setup of
external tools I need to do'Hi
Check out the information in books online or at
http://msdn2.microsoft.com/en-us/library/ms143706.aspx, hopefully that will
point you in the right direction!
John
"frogman7" wrote:
> I just downloaded and installed DTS for SQL 2005. Now the million
> dollar question
> How it I run it is there a button, link, click, dropdown, or setup of
> external tools I need to do'
>|||Hi
Check out the information in Books online or at
http://msdn2.microsoft.com/en-us/library/ms143706.aspx hopefully that will
point you in the right direction!
John
"frogman7" wrote:
> I just downloaded and installed DTS for SQL 2005. Now the million
> dollar question
> How it I run it is there a button, link, click, dropdown, or setup of
> external tools I need to do'
>|||The mySQL DBA told me about DTS and that in SQL 2000 it was a button
click then the dialog box displayed asking for source and destination
of data to move. I added an external tool to this location:
C:\Program Files\Microsoft SQL
Server\90\Tools\Binn\VSShell\Common7\IDE\DTExecUI.exe
Is this the right exe file to use?|||Hi
DTSRun.exe is the program to use to run SQL 2000 packages DTExecUI.exe
is for SSIS packages.
John
"frogman7" wrote:
> The mySQL DBA told me about DTS and that in SQL 2000 it was a button
> click then the dialog box displayed asking for source and destination
> of data to move. I added an external tool to this location:
> C:\Program Files\Microsoft SQL
> Server\90\Tools\Binn\VSShell\Common7\IDE\DTExecUI.exe
> Is this the right exe file to use?
>|||I have done something wrong because the only DTSRun.exe just runs
command prompt window and goes away.
This is the path to the file:
C:\Program Files\Microsoft SQL Server\80\Tools\Binn
I am using SQL 2005 and downloaded the 2005 version from MS web site.
Is there a better place to get the file from.|||Hi
DTSrun is used to run the packages, try DTSRUN /? from a command prompt to
get the command line options. If you have installed SQL Server2005_DTS.msi
then you should have a package called "SQL Server 2000 DTS Designer
Components" in Control Panel's Add/Remove Programs. You will the be able to
access and open packages through the legacy branch in the Object Explorer in
SQL Server Management Studio.
HTH
John
"frogman7" wrote:
> I have done something wrong because the only DTSRun.exe just runs
> command prompt window and goes away.
> This is the path to the file:
> C:\Program Files\Microsoft SQL Server\80\Tools\Binn
> I am using SQL 2005 and downloaded the 2005 version from MS web site.
> Is there a better place to get the file from.
>

DTS

Hi,
When i save a DTS package within msdb i must provide a
valid login to save it. If i schedule the package with the
DTS schedule options (right click on the DTS object), it
will create a job with a dtsrun step with the login and
password, that i provided when it was saved, encrypted for
load the DTS. I'm i right '
Suppose i save the package with dtsadmin user, where can i
found the information about the user that saved the
package ' i need this info ' suppose i change the user's
password, my job will not run any more until i rebuild the
DTSrun encrytion with the new password.
Thanks
PhilNot exactly. The package is saved with an owner who is the
logged in user who created the package. You do not have to
provide any information to save a package.
If you schedule it through Enterprise Manager, it is
scheduled with the currently logged in users credentials.
The owner and the credentials used when scheduling the
package can be different. Additionally, if it's all done
through Windows authentication, you wouldn't have a user id
and password.
The user and password used in the encrypted string for
dtsrun is not stored anywhere.
In terms of changing passwords, that's part of the reason
that Windows authentication is preferred. The biggest reason
is that it's more secure but it also often eliminates
maintenance issues such as this.
-Sue
On Fri, 2 Apr 2004 09:37:34 -0800, "Phil"
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>When i save a DTS package within msdb i must provide a
>valid login to save it. If i schedule the package with the
>DTS schedule options (right click on the DTS object), it
>will create a job with a dtsrun step with the login and
>password, that i provided when it was saved, encrypted for
>load the DTS. I'm i right '
>Suppose i save the package with dtsadmin user, where can i
>found the information about the user that saved the
>package ' i need this info ' suppose i change the user's
>password, my job will not run any more until i rebuild the
>DTSrun encrytion with the new password.
>Thanks
>Phil

DTS

I just downloaded and installed DTS for SQL 2005. Now the million
dollar question
How it I run it is there a button, link, click, dropdown, or setup of
external tools I need to do'Hi
Check out the information in books online or at
http://msdn2.microsoft.com/en-us/library/ms143706.aspx, hopefully that will
point you in the right direction!
John
"frogman7" wrote:

> I just downloaded and installed DTS for SQL 2005. Now the million
> dollar question
> How it I run it is there a button, link, click, dropdown, or setup of
> external tools I need to do'
>|||Hi
Check out the information in Books online or at
http://msdn2.microsoft.com/en-us/library/ms143706.aspx hopefully that will
point you in the right direction!
John
"frogman7" wrote:

> I just downloaded and installed DTS for SQL 2005. Now the million
> dollar question
> How it I run it is there a button, link, click, dropdown, or setup of
> external tools I need to do'
>|||The mysql DBA told me about DTS and that in SQL 2000 it was a button
click then the dialog box displayed asking for source and destination
of data to move. I added an external tool to this location:
C:\Program Files\Microsoft SQL
Server\90\Tools\Binn\VSShell\Common7\IDE
\DTExecUI.exe
Is this the right exe file to use?|||Hi
DTSRun.exe is the program to use to run SQL 2000 packages DTExecUI.exe
is for SSIS packages.
John
"frogman7" wrote:

> The mysql DBA told me about DTS and that in SQL 2000 it was a button
> click then the dialog box displayed asking for source and destination
> of data to move. I added an external tool to this location:
> C:\Program Files\Microsoft SQL
> Server\90\Tools\Binn\VSShell\Common7\IDE
\DTExecUI.exe
> Is this the right exe file to use?
>|||I have done something wrong because the only DTSRun.exe just runs
command prompt window and goes away.
This is the path to the file:
C:\Program Files\Microsoft SQL Server\80\Tools\Binn
I am using SQL 2005 and downloaded the 2005 version from MS web site.
Is there a better place to get the file from.|||Hi
DTSrun is used to run the packages, try DTSRUN /? from a command prompt to
get the command line options. If you have installed SQL Server2005_DTS.msi
then you should have a package called "SQL Server 2000 DTS Designer
Components" in Control Panel's Add/Remove Programs. You will the be able to
access and open packages through the legacy branch in the Object Explorer in
SQL Server Management Studio.
HTH
John
"frogman7" wrote:

> I have done something wrong because the only DTSRun.exe just runs
> command prompt window and goes away.
> This is the path to the file:
> C:\Program Files\Microsoft SQL Server\80\Tools\Binn
> I am using SQL 2005 and downloaded the 2005 version from MS web site.
> Is there a better place to get the file from.
>