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 error - Cannot import a table

I have schedule DTS to import every day few tables. It had been working
great and had no problem. All of a sudden I started getting this error.
ERROR AT DESTINATION FOR ROW NUMBER 100. ERROR ENCOUNTERED SO FAR IN THE
TASK. 1 UNIDENTIFIED ERROR.
Thanks.
any constraint violations caused by the new row? pk, unique, rule, default
etc?
"Mac" <mac@.hotmail.com> wrote in message news:Tz%oe.7720$nr3.842@.trnddc02...
>I have schedule DTS to import every day few tables. It had been working
> great and had no problem. All of a sudden I started getting this error.
> ERROR AT DESTINATION FOR ROW NUMBER 100. ERROR ENCOUNTERED SO FAR IN THE
> TASK. 1 UNIDENTIFIED ERROR.
> Thanks.
>
>
|||Also, turn on exception reporting for the Data Pump (Transformation Task).
This will output the source row and any other error messages.
Also, turn on package logging. You can usually get more detailed
information from these logs that what is genereated in the Job Agent.
Sincerely,
Anthony Thomas

"Richard Ding" <rding@.acadian-asset.com> wrote in message
news:elb$rqsaFHA.3032@.TK2MSFTNGP10.phx.gbl...
any constraint violations caused by the new row? pk, unique, rule, default
etc?
"Mac" <mac@.hotmail.com> wrote in message news:Tz%oe.7720$nr3.842@.trnddc02...
>I have schedule DTS to import every day few tables. It had been working
> great and had no problem. All of a sudden I started getting this error.
> ERROR AT DESTINATION FOR ROW NUMBER 100. ERROR ENCOUNTERED SO FAR IN THE
> TASK. 1 UNIDENTIFIED ERROR.
> Thanks.
>
>

DTS error - Cannot import a table

I have schedule DTS to import every day few tables. It had been working
great and had no problem. All of a sudden I started getting this error.
ERROR AT DESTINATION FOR ROW NUMBER 100. ERROR ENCOUNTERED SO FAR IN THE
TASK. 1 UNIDENTIFIED ERROR.
Thanks.any constraint violations caused by the new row? pk, unique, rule, default
etc?
"Mac" <mac@.hotmail.com> wrote in message news:Tz%oe.7720$nr3.842@.trnddc02...
>I have schedule DTS to import every day few tables. It had been working
> great and had no problem. All of a sudden I started getting this error.
> ERROR AT DESTINATION FOR ROW NUMBER 100. ERROR ENCOUNTERED SO FAR IN THE
> TASK. 1 UNIDENTIFIED ERROR.
> Thanks.
>
>|||Also, turn on exception reporting for the Data Pump (Transformation Task).
This will output the source row and any other error messages.
Also, turn on package logging. You can usually get more detailed
information from these logs that what is genereated in the Job Agent.
Sincerely,
Anthony Thomas
"Richard Ding" <rding@.acadian-asset.com> wrote in message
news:elb$rqsaFHA.3032@.TK2MSFTNGP10.phx.gbl...
any constraint violations caused by the new row? pk, unique, rule, default
etc?
"Mac" <mac@.hotmail.com> wrote in message news:Tz%oe.7720$nr3.842@.trnddc02...
>I have schedule DTS to import every day few tables. It had been working
> great and had no problem. All of a sudden I started getting this error.
> ERROR AT DESTINATION FOR ROW NUMBER 100. ERROR ENCOUNTERED SO FAR IN THE
> TASK. 1 UNIDENTIFIED ERROR.
> Thanks.
>
>sql

DTS error - Cannot import a table

I have schedule DTS to import every day few tables. It had been working
great and had no problem. All of a sudden I started getting this error.
ERROR AT DESTINATION FOR ROW NUMBER 100. ERROR ENCOUNTERED SO FAR IN THE
TASK. 1 UNIDENTIFIED ERROR.
Thanks.any constraint violations caused by the new row? pk, unique, rule, default
etc?
"Mac" <mac@.hotmail.com> wrote in message news:Tz%oe.7720$nr3.842@.trnddc02...
>I have schedule DTS to import every day few tables. It had been working
> great and had no problem. All of a sudden I started getting this error.
> ERROR AT DESTINATION FOR ROW NUMBER 100. ERROR ENCOUNTERED SO FAR IN THE
> TASK. 1 UNIDENTIFIED ERROR.
> Thanks.
>
>|||Also, turn on exception reporting for the Data Pump (Transformation Task).
This will output the source row and any other error messages.
Also, turn on package logging. You can usually get more detailed
information from these logs that what is genereated in the Job Agent.
Sincerely,
Anthony Thomas
"Richard Ding" <rding@.acadian-asset.com> wrote in message
news:elb$rqsaFHA.3032@.TK2MSFTNGP10.phx.gbl...
any constraint violations caused by the new row? pk, unique, rule, default
etc?
"Mac" <mac@.hotmail.com> wrote in message news:Tz%oe.7720$nr3.842@.trnddc02...
>I have schedule DTS to import every day few tables. It had been working
> great and had no problem. All of a sudden I started getting this error.
> ERROR AT DESTINATION FOR ROW NUMBER 100. ERROR ENCOUNTERED SO FAR IN THE
> TASK. 1 UNIDENTIFIED ERROR.
> Thanks.
>
>

DTS Create Report And email .

Hello,

I am trying to create a dts to schedule some reports and email it. The point is that i try to do that through Data Flow > Export to Excel....!!! Is there an other way to create reports? Cause i don't see any crystal reports or somenthing else!!!

Thanks
Stefanos
SSIS (I assume you're using SSIS, if not, you're in the wrong forum) does not create reports. You can save data to Excel, but it's just going to be rows & columns. No formatting, etc...|||

Stefanos -

I would recommend looking at SQL Reporting Services (SQLRS) for this need. SQLRS has two different report scheduling capabilities. SQL Server Standard Edition has standard subscription capability, while the Enterprise Edition adds to this a Data Driven subscription capability.

When a standard SQLRS subscription is created, this actually creates a job that is managed by the SQL Agent. If you needed SSIS to be able to execute that job/subscription, I believe it would be easy to do in SSIS.

Hope that might help you.

|||

Thanks for your reply,

I thought that through ssis i could create an excel report and edit the excel file through a script. My problem is that a want to create a report automatically and email it to a customer!! I think that Sql RS will help me doing that. As for the email forwarding, i think i will need ssis.

Thanks Stefanos

DTS copy table from source DB->destination DB

I have started to create a package in DTS. I have created 2 connections for source DB and destination DB. I want to schedule a task to copy data from source to destination

I'm not sure which task to choose from. I tried 'Data Driven Query Task'. Firstly, I entered the source, using the source DB connection. I then chose the table I want to copy.
Next I clicked on the Bindings tab and chose the destination connection and created an identical table to the source table, ready for transferring data.

I just want to transfer the data and schedule it but when I ran the package it said:

'ActiveX scripting requires Script Text and Langauge and at least one Phase function to be specified.'

I re-opened the 'Data Driven Query Task'

I clicked on the Queries tab and the Query type was set to the default 'Insert'. The next step is to build the Insert query, I thought. I clicked on the Build button and got a list of tables in the destination connection. I dragged the destination table over (the one I just created), selected all the fields and now I am lost. As I clicked on the field list, the Insert statement was generated as:

INSERT INTO tblMasterCrownOffice
(CrownOfficeUniqueID, ChangeHistory, CurrentChange, RelocationsContact)
VALUES ()

But I cannot fill in the VALUES () part as I can only choose from a lits of destination tables.

Does anyone know how to setup the package to copy data from one source connection table to a destination connection table?

Thanks.I found out that I could click on the Transformations tab and choose 'Copy Columns' transformation.

So, when I run it now I get the error

'The data driven query task requires at least one query (and associated columns) to be specified'

I don't want to set any queries. I just want the transformation to run when i execute the package.

Any ideas?|||Why not just schedule an SP to run to copy the data?

For dts.
Create the two ole db connections
Add a transform data task between them
double click on the line
Source
select the table or enter a query to filter the data
Destination
Select the destination table
Transformations
Select the mappings from source to destiation

Save the package - you can save it in msdb but I prefer to save as files.
scheduling
create a scheduled job and in a command step put dtsrun /F<package name and path>|||Originally posted by nigelrivett
Why not just schedule an SP to run to copy the data?

For dts.
Create the two ole db connections
Add a transform data task between them
double click on the line
Source
select the table or enter a query to filter the data
Destination
Select the destination table
Transformations
Select the mappings from source to destiation

Save the package - you can save it in msdb but I prefer to save as files.
scheduling
create a scheduled job and in a command step put dtsrun /F<package name and path>

Thanks,

I was using 'Data driven Query Task', when I only need 'Transform Data' task. I have to wait until next week before I can link the client's oracle table to sql-server, so I've simulated the task by scheduling a table to be copied and transformed a bit from one SQL-Server Database to another. I executed it no problem, and found the Job list under SQL Agent.

i think I'll straight copy the Oracle tables over to SQL-Server daily, then schedule an SP to run on successful completion, that will transform the data properly. my only worry is if a DTS fails then it will screw up the data. I think I'll copy the data into a temp holding table, check the no of rows are OK, and only then delete the current data with the bext day's data.

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

DTExec and Pasword Prompting

A colleague of mine is having problems when trying to schedule package execution via a .bat file that executes a .dtsx on a sql server (not file system). The scheduling system that is employed by the company requires a .bat file to execute the package. The packages themselves move data from AS400 servers to SQL Servers. Occasionally (very randomly) when the scheduling system runs the statement in the bat files, the package prompts for password information (for connectivity).

We've tried a number of solutions to this, mostly on the "ProtectionLevel" property of the package itself. We did some research and it seems as though there are a few options.What is the best solution to eliminate this from happening? We certainly don't want to have people checking to make sure the packages don't prompt for passwords.

Thanks.

SSIS Amateur wrote:

A colleague of mine is having problems when trying to schedule package execution via a .bat file that executes a .dtsx on a sql server (not file system). The scheduling system that is employed by the company requires a .bat file to execute the package. The packages themselves move data from AS400 servers to SQL Servers. Occasionally (very randomly) when the scheduling system runs the statement in the bat files, the package prompts for password information (for connectivity).

We've tried a number of solutions to this, mostly on the "ProtectionLevel" property of the package itself. We did some research and it seems as though there are a few options.What is the best solution to eliminate this from happening? We certainly don't want to have people checking to make sure the packages don't prompt for passwords.

Thanks.

I recommend setting ProtectionLevel=DontSaveSensitive and then using configurations to store your conenction strings. This is the "least hassle" choice in my experience.

-Jamie

|||

OK, we'll try that.

Couple more things, in a whole series of packages, it only prompts for a password on one or another, and it's not always the same packages, even though they always run in the same order, but they all hit AS400 data. Just wondering if you had any input on that? They are all set up the same security wise.

Also, we just came across the connection manager property "ProtectionLevel". I didn't research it yet, but if we changed the setting from the default "0", could this have an impact?

|||

SSIS Amateur wrote:

OK, we'll try that.

Couple more things, in a whole series of packages, it only prompts for a password on one or another, and it's not always the same packages, even though they always run in the same order, but they all hit AS400 data. Just wondering if you had any input on that? They are all set up the same security wise.

Seems strange but I can't explain it. Sorry.

SSIS Amateur wrote:

Also, we just came across the connection manager property "ProtectionLevel". I didn't research it yet, but if we changed the setting from the default "0", could this have an impact?

Yes, it could. You should read the BOL topic on it.

-Jamie

|||

Jamie,

We're currently trying to implement your suggestion to set the Package Level Security "ProtectionLevel" to "DontSaveSensitive" and create a configuration to hold the connection strings. I seem to not be able to even get it to run in debug mode (let alone deploying and testing), because a login is failing. I noticed that the connection string does not store the password, and it's not possible to put it in there (just an observation - maybe not relevant).

It runs fine when I changed the ProtectionLevel back to the default setting. I'm know I'm missing something really stupid here. Your help is much appreciated, you know your stuff.

|||

SSIS Amateur wrote:

Jamie,

We're currently trying to implement your suggestion to set the Package Level Security "ProtectionLevel" to "DontSaveSensitive" and create a configuration to hold the connection strings. I seem to not be able to even get it to run in debug mode (let alone deploying and testing), because a login is failing. I noticed that the connection string does not store the password, and it's not possible to put it in there (just an observation - maybe not relevant).

It runs fine when I changed the ProtectionLevel back to the default setting. I'm know I'm missing something really stupid here. Your help is much appreciated, you know your stuff.

Good observation about the password. You have to manually edit the connection string yourself and put the password in there.

As you may know there was a big push in Microsoft not long ago around tightening security holes in its products and this is one of things that came out of that - it is considered a security risk for SSIS to store a password for you. Microsoft want you to responsible for creating security risks, not them.

-Jamie

|||Jamie, when you say "manually edit the connection string", where are you talking about? Actually opening the .dtsConfig file and editing? Because I can't seem to be able to successfully store the password in the Package editor.|||

OK, I have seemed to have figured my problem out. For those of you who may be a little new to SSIS or new to configuration files, here's a run down on my problem and how I fixed it.

My company scheduling software was getting prompted for a password when a .bat file containing a DTExec command was initiated, this was due to connecting to AS400 servers while transferring data. So we had to look into a way to prevent the scheduled .bat file executions from prompting for a password. Here is the solution, in detail for those of you who are lacking in SSIS experience like myself, and have a similar situation. Some of it is very obvious to most, but was not to me, so I included everything I could think of. Thanks once again to Jamie for all the help.

1. In the package, change protection level to "DontSaveSensitive" (click anywhere in the control flow, off of a task and look at properties).
2. Create a package configuration to store connection string (there are many examples on the web, here is one).
http://msdn2.microsoft.com/en-us/library/ms140213.aspx

3. You will have to manually edit the resulting .dtsConfig to include passwords. Open in notepad and add passwords to each connection. It will be stored in the project folder.
4. Build the package and deploy it, save the configuration file where you feel best. I saved on folder a sql server itself (this is an option when deploying).
5. Change (or create) your .bat file to include the /CONF option, this will be the fully qualified path each of your .dtsConfig files.
6. Rerun you .bat to make sure it works properly.

|||

SSIS Amateur wrote:

Jamie, when you say "manually edit the connection string", where are you talking about? Actually opening the .dtsConfig file and editing?

Yep. That's exactly what I mean.

dtexec

I understand to schedule an SSIS package to run, I need to use the dtexec utility. I want to schedule the job right within SQL. I walked through how to set up the job, but I really could not decipher the proper syntax to use as a Step.

I have the following:

dtexec /File "C:\...file.dtsx"

Is the the proper way to schedule and execute an SSIS package or is there some other way I should be doing this. The pacakge will run unattended nightly. I am using SQL Server 2005.

Thanks for the information

Hi, yes the command line syntax can be a bit daunting. But you should not have to type it out yourself...if your using SQL agent and choose the Step type of "SQl Server Integration Services Package", it will show a UI that helps you build the command line for you though you do not need to build the full command line there.

If your running outside of Agent or want to run with Agent and the step type of Command exec then use dtexecui.exe as it will also help you build the command line...and its the basis of the agent UI I mention above.

Hope that helps