DTS Scheduling Issue

I have a DTS package that runs without any problems when I run it manually, however, when I schedule the package at a certain time no output is produced.

I have checked the Agent and it says the job is executing, however, it seems to have hung.

The log tells me DTSStep CreateProcessTask3 started at the correct time but there is nothing after that.

How do I check this? or find out what is causing it to hang?

Thanks!Try running profiler at the time the job start starts executing to see how far the job get's. What user account is the job run under?|||Hey tr1na,

Many thanks for your response it is appreciated. SQL server is new to me so apologies for the lack of knowledge

I have never run profiler before, I have had a look and it doesnt look that simple to run and also, I couldnt see where I could call my DTS package.

Anyway, in the package log file it states DTSStep CreateProcessTask3 started at 07.32 and well thats it! I have read somewhere that this could be a security issue and I should be logged in as SYSADMIN? However, I am currently Remote Desktop to the SERVER, login through NT, the package owner is my USER ID, how can I log into EM as SYSADMIN and try scheduling a DTS package under SYSADMIN, do you think this could be the problem?

I am not sure how to check the LOGIN ID.|||To view which user the job is running as right click the job and select "view job history" then tick "show step details". You should then see

"Executed as user: SYD\blah" etc|||Thanks!

Well, unfortunately, the job does not complete because it hangs so there is no job history, however, when I cancelled to job it tells me that the job was cancelled prior to completion by my "USER ID". So I suspect this is the USER ID being used to start the job?

Perhaps I need someway to remote desktop but login as SYSADMIN?|||when you run the job manually are you logged in as your userid. if so check the job runs under your user id.|||there are differences of environment when u execute a dts by right-clicking and thru jobs. for example if u refer a server name as (local) in connection, during right-clicked execution the database will be selected from the server where EM is installed but during execution thru job it will connect to server from where the job is running. similarly any disk file path/reference to COM component also changes with the execution mode. and this is often the source of problem reported by u...|||OK. The job did not run manually when logged in by me, it does run manually when logged in to the database as "sa"

upalsen:

Thank you for your response, it is appreciated, how can I get round this problem, is it a matter of logging in as SYSADMIN, hopefully it is as something as simple as this?|||there r a few settings under properties context menu (right-click) of SQL Server Agent. by default users other than sysadmin r not allowed to run cmdexec etc command. check the Job-System tab and Connection tab for such user specific issues. also a job can be saved under user other than sa. i am not sure exactly where your job is hanging but u can try the above combinations and check the result.|||Thank you.

OK. Well the check box is checked "Only users with SysAdmin priviledges can run CmdExec etc..."

So I uncheck the box, then it asks me to "Enter the proxy account SQL agent will use to run jobs owned by non-administrators"

Does this mean I need to create a "dummy" account and does it need to be a member of the sysadmin group or have specific priviledges?

Thank you.|||u give the name & password of the local m/c administrator and enter the machine name as domain. if u r having a domain controller in your network u can specify the details of a domain account as well.

DTS question for Microsoft

Simple question, hope there's a simple answer. ok, when
you have a DTS "Execute SQL Task" step, is there any way
AT ALL, that I can see the output from that SQL? I am
NOT looking to do anything more then debug VERY large
poorly written DTS packages that have MANY steps, and
seeing the normal SQL output from the steps would help a
LOT. Also, if a step has a friendly name like "Cleanse
Customer Table" why can't THAT be shown in the output,
instead of a name like "DTSStep_DTSExecuteSQLTask_1".
Those two points make large DTS packages a nightmare to
maintain. So, I'm hoping someone knows a trick to see
REAL SQL output and not just a list like I DO see
below... When an error happens we must script out the DTS
package to VBscript and find the code that way, which is
not an easy method to debug at 3am... Thanks, Bruce
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_44
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_44
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_4
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_6
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_6
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_7
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_12
etc, etc, etc...
Bruce,
if you open up the package and right-click on the white background then open
up disconnected edit, you can browse to the step, select the step name eg
DTSStep_DTSDynamicPropertiesTask_1 and rename it to anything you want. There
are some dependencies when remaning things here - if you rename a task, the
precedence constraint refers to the old name and will itself need renaming
also.
HTH,
Paul Ibison
sqlsql

DTS question for Microsoft

Simple question, hope there's a simple answer. ok, when
you have a DTS "Execute SQL Task" step, is there any way
AT ALL, that I can see the output from that SQL? I am
NOT looking to do anything more then debug VERY large
poorly written DTS packages that have MANY steps, and
seeing the normal SQL output from the steps would help a
LOT. Also, if a step has a friendly name like "Cleanse
Customer Table" why can't THAT be shown in the output,
instead of a name like "DTSStep_DTSExecuteSQLTask_1".
Those two points make large DTS packages a nightmare to
maintain. So, I'm hoping someone knows a trick to see
REAL SQL output and not just a list like I DO see
below... When an error happens we must script out the DTS
package to VBscript and find the code that way, which is
not an easy method to debug at 3am... Thanks, Bruce
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_44
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_44
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_4
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_6
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_6
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_7
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_12
etc, etc, etc...Bruce,
if you open up the package and right-click on the white background then open
up disconnected edit, you can browse to the step, select the step name eg
DTSStep_DTSDynamicPropertiesTask_1 and rename it to anything you want. There
are some dependencies when remaning things here - if you rename a task, the
precedence constraint refers to the old name and will itself need renaming
also.
HTH,
Paul Ibison

DTS Parametirized ExecuteSQlTask

Hi,

I have a DTS whit several SQl tasks that executes a stored procedure. The result of this execution is stored in an output parameter as global variable.

The problem is if i manually launch the DTS package, it works with no problems but after i schedule the job, i received an error in the SQl tasks i said before. What can i do to fix it?

The Error is:
-----
Executed as user: SCCCOL1\sqlservices. ...t: DTSStep_DTSActiveScriptTask_4 DTSRun OnFinish:
DTSStep_DTSActiveScriptTask_4 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_26 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_26 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_4 DTSRun OnError: DTSStep_DTSExecuteSQLTask_4, Error = -2147220421 (8004043B) Error string: The task reported failure on execution. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 Error Detail Records: Error: -2147220421 (8004043B); Provider Error: 0 (0) Error string: The task reported failure on execution. Er... Process Exit Code 1. The step failed.When you schedule a dts task as a job, the permissions used for this job varies. Who is the owner of the job, what login is being used for the sql server agent service and what is the step doing when it fails ?|||Originally posted by rnealejr
When you schedule a dts task as a job, the permissions used for this job varies. Who is the owner of the job, what login is being used for the sql server agent service and what is the step doing when it fails ?

Hi,

Thanks for ur answer. The owner of my job is an windows authenticated user how has administrator permissions over the server, but user used to run sqlserver agent is default user: SqlServices. However in the properties, i've configured the connections like windows authentication ( i supuse that it's using my administrative account to run jobs).

In a previous post, i found that i have to doing bigger the login time-out for SQL Agent. I did it and i rebuild the package into one new with a connection with clear specifications to the server. i mean that before i've reference to [local] server and after i changed it to [NAMESERVER] SQL on my network. I scheduled this package and it works.

is it a bug of SQl Server? why Agent SQl works with a form and not with another?

Thanks,
Maritzita

DTS Package - Zip Output File

Does anyone know if I can write a command inside of a DTS package to
zip the text output file?
Thank you!
Angie"angela.y.austin@.gmail.com" wrote:
> Does anyone know if I can write a command inside of a DTS package to
> zip the text output file?
> Thank you!
> Angie
>
Hi Angie
You can always use a SQL task to run xp_cmdshell and invoke the command line
version of winzip! If you have larger files you may want to run winrar
instead.
John

DTS output file with "Append " mode?

I have set up an export which use DTS service to extract data from a table to a Flat ( text) format file.

Is there any way setup this simple DTS as a " APPEND" mode? so that next time's data will append to the flat file output?

because the default way I can do for this flat file is " overwrite " .
the second time's run will always overwrite the privious run result.
even save the DTS scipt to VB file still can't find where to setup output file write mode.

Appreciate any help

Regards

GuyangDid you ever get an answer on this? I have the same requirement

Originally posted by guyang2000
I have set up an export which use DTS service to extract data from a table to a Flat ( text) format file.

Is there any way setup this simple DTS as a " APPEND" mode? so that next time's data will append to the flat file output?

because the default way I can do for this flat file is " overwrite " .
the second time's run will always overwrite the privious run result.
even save the DTS scipt to VB file still can't find where to setup output file write mode.

Appreciate any help

Regards

Guyang|||If you transform the data to a excel sheet instead of a flat file its always appended. you might think of making the DTS package in such a way that first step will move data to a excel file(which is always appended) and the second step will transform data from that excel to a text file. this might solve your purpose.|||Thank-you but,

That won't work for me. The number of entries exceeds the limitations of Excel.

Currently my only viable option is to export subsets into a number of text files and concatenating them into one text file.

To me this is less than ideal.|||I was about to post the same question, but hoping there was an append mode for bcp. There is no switch listed for appending, but I really need it.

The alternative for me would be to post results to a temporary table, then output that table to a file, but I am hoping to avoid it.|||I am currently doing exactly that. But be careful if the order is important..

I am transferring a varchar value into an ntext value in another table (only way to maintain paragraph formatting) to generate LDIF files for an X500 directory. I have attempted to force a sort order through a series of SQL UPDATE statements.

Unfortunately, the sort order gets destroyed by the size of the export to the point that the text file does not match the order of the table. Indexing of this table is impossible.

Originally posted by bpdWork
I was about to post the same question, but hoping there was an append mode for bcp. There is no switch listed for appending, but I really need it.

The alternative for me would be to post results to a temporary table, then output that table to a file, but I am hoping to avoid it.|||My requirement is to build an SAP feed of billing data for a customer. Since ordering is very important (the file is actually broken up into blocks by cost center), I was thinking of a temp table cosisting of either an int or timestamp id, and a varchar(4000), which is much larger than any line can be. I simply output ordering by the id field.

I thought about building a text field and appending, but the added work of pointers, plus adding the end of row terminiators, etc. is a pain in the ass, and seems like a breeding ground for bugs. Sounds like our requirements are different though.|||Take a serious look at an ntext (blob) as opposed to a nvarchar(4000). You can maintain the format and end charachters in an ntext to text output.

Originally posted by bpdWork
My requirement is to build an SAP feed of billing data for a customer. Since ordering is very important (the file is actually broken up into blocks by cost center), I was thinking of a temp table cosisting of either an int or timestamp id, and a varchar(4000), which is much larger than any line can be. I simply output ordering by the id field.

I thought about building a text field and appending, but the added work of pointers, plus adding the end of row terminiators, etc. is a pain in the ass, and seems like a breeding ground for bugs. Sounds like our requirements are different though.

DTS Output Excel File - how to format?

Has anyone had experience formatting an excel file (i.e. run a macro) after it is (created &) outputted from a DTS package?

Also an easier question:
What is the best (easiest) way to create a unique filename in Excel with a datetimestamp in the file name (i.e. MyFile-20040608.xls)

Thanks!I will have a similar situation coming up so I am curious to see the answer. We are installing a SQL Server box in our department. It will be our own server where I will be able to create DTS jobs to export data every 15 minutes throughtout the course of the day. I plan on appending data to a table and having it do so for each and every job's output on SQL Server. The issue is I am only inhouse very early in the morning so I want to allow the client to see the reports as they run. They are tech challenged so I want to use either Excel or Access to create reports as they are run.

ddave|||I had vb developers modify a csv file while I wrotw the sql...

It was a whole store and forward app...scheduling, formatting ect...

sql just delivered the file to a location...|||>Also an easier question:
>What is the best (easiest) way to create a unique filename in Excel with a >datetimestamp in the file name (i.e. MyFile-20040608.xls)

DTS outputs it to any MyFile.xls and the next ActiveX step renames MyFile.xls file to MyFile-<date>.xls
-rohit

DTS issue

In Microsoft SQL DTS I'm trying to run a package that convert output to a excel spreadsheet. Following error appears:
Error source: Microsoft Jet Database Engine
Error Description: Failure Creating File. What has added to my frustrations is, once I go to the server and open up the DTS package and click on destination it allows me to create new table, but that is only temporary. If I run a schedule job at a later time it fails again with the above message.Well if you're doing a CREATE, have you added the DROP?|||Brett, thanks for your reponse. It's only creating a dynamic table through DTS. When I take a look at the tables list it does not exist.|||Take a look at this post -> should answer your question.

http://www.dbforums.com/showthread.php?threadid=981661

kbk

DTS headers and trailers

Does anyone know how to incorporate header and trailer info in the text output from a DTS task. The SQL I'm using in the task works well when outputting to a text file on the server. However, when trying to incorporate header and trailer info the process fails. I tried doing it using SQL but the DTS transformation process is looking for fields to transform when I'm trying to output a 3 line header and 2 line trailer with the output rows in between. Has anyone done this before? Your help would be greatly asppreciated.

Here is an example of the SQL that fails:

*** Trying to get header info here ***
select 'Header'
select 'filename.txt'
select convert(char(11),getdate(),101)

*** Main SQL runs here ***
select
a.field1,
a.field2,
...

from tblA a JOIN tblB b ON b.someid = a.someid
JOIN tblC c ON c.someid = a.someid

where a.someval >= '2003'

*** Trying to get trailer info here ***
select 'Trailer'
select ''
select count(id) FROM tblAI'd bcp out three files (or echo out the header and trailer)

Then append the files to each other using dos commands...