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 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 Packages

I'm moving to SQL Server 2005 on a new box. In SQL 2000 I have a three step
SQL job that:
1. truncates data
2. runs a DTS package
3. runs a stored procedure
In 2000 I ran DTSRUNUI to generate an encripted command that generated a
GUID line. This was the line in I inserted into step two listed above.
In SQL 2005 I installed the dts compatability stuff but it does not give me
the DTSRUNUI application. Any suggestions other than use SSIS?
--
Thanks
TitanHi
Why don't you run the package using it's name?
John
"Titan" <Titan@.discussions.microsoft.com> wrote in message
news:380EC2B4-133A-4F4A-BA11-2677D1F066DD@.microsoft.com...
> I'm moving to SQL Server 2005 on a new box. In SQL 2000 I have a three
> step
> SQL job that:
> 1. truncates data
> 2. runs a DTS package
> 3. runs a stored procedure
> In 2000 I ran DTSRUNUI to generate an encripted command that generated a
> GUID line. This was the line in I inserted into step two listed above.
> In SQL 2005 I installed the dts compatability stuff but it does not give
> me
> the DTSRUNUI application. Any suggestions other than use SSIS?
> --
> Thanks
> Titansqlsql

DTS package step to disable

Hi DBA's,

I have a DTS package that has steps 1 to 13. Say for a given situation I do not want the package to execute Step 11, 12 and 13 but steps 1 to 10 should be executed and the package should be treated as a sucess. Please confirm if the following approach is correct.

1. I go to Local Packages on the EM and then open up my package in designer view.
2. I go to step 11, right-click to go to WorkFlow properties. I go to Options tab and under execution I select disable this step.
3. I repeat the process for step 12 and step 13. Now 11 to 13 are disabled. I now execute the package.

Is the above correct?That will work in your description - but that is very hardwired - when would you not execute those steps ?|||Originally posted by rnealejr
That will work in your description - but that is very hardwired - when would you not execute those steps ?

Well step 11 is a stored proc which is failing now in the DTS package. So I want to disable step 11 and all the further steps. I intend to run step 11 in the QA. I tried to automate it by includusing step 11 in a DTS package. I am concerned with only step 1 to step 10. the rest of the steps 11-13 can be done manually.

DTS Package - "Provider generated code execution exception: "EXCEPTION_ACCESS_VIOLATION"

When running a step within my DTS package I'm receiving the following
error - "Provider generated code execution exception:
"EXCEPTION_ACCESS_VIOLATION".
I think it may be something to do with my global variable, but I'm not
sure as I'm pretty certain I've set it all up correctly.
Below are screenprints showing my settings.
http://img153.imageshack.us/my.php?image=19tv1.jpg
http://img153.imageshack.us/my.php?image=25wz1.jpg
http://img153.imageshack.us/my.php?image=39pf.jpg
http://img164.imageshack.us/my.php?image=43nx.jpg
http://img164.imageshack.us/my.php?image=51ao.jpg
http://img164.imageshack.us/my.php?image=64lo.jpg
http://img164.imageshack.us/my.php?image=71yn.jpg
Any advice of fixing this would be greatly appreciated.Can anyone help? The below screenshots show my specific settings (note
I'm still getting the same error with these options)
http://www.files2net.com/files/46053431/1.JPG
http://www.files2net.com/files/266513949/2.JPG
http://www.files2net.com/files/266955779/3.JPG

DTS Package - "Provider generated code execution exception: "EXCEPTION_ACCES

When running a step within my DTS package I'm receiving the following error
- "Provider generated code execution exception: "EXCEPTION_ACCESS_VIOLATION"
.
I think it may be something to do with my global variable, but I'm not sure
as I'm pretty certain I've set it all up correctly.
Below are screenprints showing my settings.
http://img153.imageshack.us/my.php?image=19tv1.jpg
http://img153.imageshack.us/my.php?image=25wz1.jpg
http://img153.imageshack.us/my.php?image=39pf.jpg
http://img164.imageshack.us/my.php?image=43nx.jpg
http://img164.imageshack.us/my.php?image=51ao.jpg
http://img164.imageshack.us/my.php?image=64lo.jpg
http://img164.imageshack.us/my.php?image=71yn.jpg
Any advice of fixing this would be greatly appreciated.Can anyone help? The below screenshots show my specific settings (note I'm s
till getting the same error with these options)
http://www.files2net.com/files/46053431/1.JPG
http://www.files2net.com/files/266513949/2.JPG
http://www.files2net.com/files/266955779/3.JPG|||Managed to solve it by changing the settings to the following:
http://www.files2net.com/files/154842170/settings.jpg

DTS logs and "sysdtssteplog" in msdb

HI,
Does anybody know (maybe from MS) is it safe to remove (truncate table) rows
from the "sysdtssteplog" table with contains DTL step logs?
RedardsYou can delete the logs from Enterprise Manager. Go to Data Transformation
Services, right click in the right pane, and choose 'Package logs...' from
the pop up menu. This will give you a dialog from which you can delete
package logs for all package past and present, and you can delete all the
logs for a package at once as well.
--
Jacco Schalkwijk
SQL Server MVP
"stats12" <vhs180@.polbox.com> wrote in message
news:%231txanRpDHA.2808@.TK2MSFTNGP10.phx.gbl...
> HI,
> Does anybody know (maybe from MS) is it safe to remove (truncate table)
rows
> from the "sysdtssteplog" table with contains DTL step logs?
> Redards
>|||Yes i know, but i must do it manulay for each package.
Now i have 70-ty packages and each is running even 100 times a day.
That's why i'm thinging about some process with wich i would be able to
automatycly clear the DTS logs.
U¿ytkownik "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> napisa³ w
wiadomo¶ci news:e6y1vsRpDHA.2512@.TK2MSFTNGP09.phx.gbl...
> You can delete the logs from Enterprise Manager. Go to Data Transformation
> Services, right click in the right pane, and choose 'Package logs...' from
> the pop up menu. This will give you a dialog from which you can delete
> package logs for all package past and present, and you can delete all the
> logs for a package at once as well.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "stats12" <vhs180@.polbox.com> wrote in message
> news:%231txanRpDHA.2808@.TK2MSFTNGP10.phx.gbl...
> > HI,
> >
> > Does anybody know (maybe from MS) is it safe to remove (truncate table)
> rows
> > from the "sysdtssteplog" table with contains DTL step logs?
> >
> > Redards
> >
> >
>|||You can create a DTS package with a VBScript task and use the DTS object
model to remove the logs with the PackageSQLServer.RemovePackageLogRecords
method See the article "Retrieving DTS System, Package, and Log Data" in
Books Online for more details.
Undocumented and thus unsupported is the use of the stored procedures that
are called by the above, sp_dump_dtspackagelog and sp_dump_dtslog_all. You
can use these, or have a look at the code of them (they are in the msdb
database), but if something goes wrong with updating system tables directly
like this, you won't have anything to fall back on
--
Jacco Schalkwijk
SQL Server MVP
"stats12" <vhs180@.polbox.com> wrote in message
news:eyJuolSpDHA.3024@.tk2msftngp13.phx.gbl...
> Yes i know, but i must do it manulay for each package.
> Now i have 70-ty packages and each is running even 100 times a day.
> That's why i'm thinging about some process with wich i would be able to
> automatycly clear the DTS logs.
>
> U¿ytkownik "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> napisa³ w
> wiadomo¶ci news:e6y1vsRpDHA.2512@.TK2MSFTNGP09.phx.gbl...
> > You can delete the logs from Enterprise Manager. Go to Data
Transformation
> > Services, right click in the right pane, and choose 'Package logs...'
from
> > the pop up menu. This will give you a dialog from which you can delete
> > package logs for all package past and present, and you can delete all
the
> > logs for a package at once as well.
> >
> > --
> > Jacco Schalkwijk
> > SQL Server MVP
> >
> >
> > "stats12" <vhs180@.polbox.com> wrote in message
> > news:%231txanRpDHA.2808@.TK2MSFTNGP10.phx.gbl...
> > > HI,
> > >
> > > Does anybody know (maybe from MS) is it safe to remove (truncate
table)
> > rows
> > > from the "sysdtssteplog" table with contains DTL step logs?
> > >
> > > Redards
> > >
> > >
> >
> >
>|||Thank's for the tip with VB and the "PackageSQLServer" object - that's all i
need...
Regards...
U¿ytkownik "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> napisa³ w
wiadomo¶ci news:eqoiYDTpDHA.392@.TK2MSFTNGP11.phx.gbl...
> You can create a DTS package with a VBScript task and use the DTS object
> model to remove the logs with the PackageSQLServer.RemovePackageLogRecords
> method See the article "Retrieving DTS System, Package, and Log Data" in
> Books Online for more details.
> Undocumented and thus unsupported is the use of the stored procedures that
> are called by the above, sp_dump_dtspackagelog and sp_dump_dtslog_all. You
> can use these, or have a look at the code of them (they are in the msdb
> database), but if something goes wrong with updating system tables
directly
> like this, you won't have anything to fall back on
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "stats12" <vhs180@.polbox.com> wrote in message
> news:eyJuolSpDHA.3024@.tk2msftngp13.phx.gbl...
> > Yes i know, but i must do it manulay for each package.
> > Now i have 70-ty packages and each is running even 100 times a day.
> > That's why i'm thinging about some process with wich i would be able to
> > automatycly clear the DTS logs.
> >
> >
> > U¿ytkownik "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> napisa³ w
> > wiadomo¶ci news:e6y1vsRpDHA.2512@.TK2MSFTNGP09.phx.gbl...
> > > You can delete the logs from Enterprise Manager. Go to Data
> Transformation
> > > Services, right click in the right pane, and choose 'Package logs...'
> from
> > > the pop up menu. This will give you a dialog from which you can delete
> > > package logs for all package past and present, and you can delete all
> the
> > > logs for a package at once as well.
> > >
> > > --
> > > Jacco Schalkwijk
> > > SQL Server MVP
> > >
> > >
> > > "stats12" <vhs180@.polbox.com> wrote in message
> > > news:%231txanRpDHA.2808@.TK2MSFTNGP10.phx.gbl...
> > > > HI,
> > > >
> > > > Does anybody know (maybe from MS) is it safe to remove (truncate
> table)
> > > rows
> > > > from the "sysdtssteplog" table with contains DTL step logs?
> > > >
> > > > Redards
> > > >
> > > >
> > >
> > >
> >
> >
>

DTS job not executing all tasks

SQL Server 2000
I created DTS package with several step.
When I run manually, all steps work OK.
When I schedule as job, only the first step runs. The next two steps
do not run.
The log simply says step ...xyz.. did not run
The owner of the job is the same as my regular XP login anme which is
also the same name I run it as manually.
Any ideas?
Thanks
jeffWhat is the login used to start the services of SQL agent.
from
Doller|||>What is the login used to start the services of SQL agent.
system account
I have more specifics about the problem.
I scheduled and ran only the package that makes up the first step of
the previously mentioned package.
This first step (package) runs a command file (DOS .bat) that ftp's
to a remote machine and does an "mget filename".
When this single package is executed manually it all works. I look in
the local machine folder and there is the file.
When run as a scheduled job the log shows the ftp mget getting the
file. However, the file does not exist on the local machine when the
job finishes.
As a matter of fact, if I manually execute the scheduled job, it also
shows that is gets file, but file does not exist on local machine.
jeff
On 19 Apr 2006 19:58:53 -0700, "doller" <sufianarif@.gmail.com> wrote:
>What is the login used to start the services of SQL agent.
>from
>Doller

DTS job not executing all tasks

SQL Server 2000
I created DTS package with several step.
When I run manually, all steps work OK.
When I schedule as job, only the first step runs. The next two steps
do not run.
The log simply says step ...xyz.. did not run
The owner of the job is the same as my regular XP login anme which is
also the same name I run it as manually.
Any ideas?
Thanks
jeffWhat is the login used to start the services of SQL agent.
from
Doller|||>What is the login used to start the services of SQL agent.
system account
I have more specifics about the problem.
I scheduled and ran only the package that makes up the first step of
the previously mentioned package.
This first step (package) runs a command file (DOS .bat) that ftp's
to a remote machine and does an "mget filename".
When this single package is executed manually it all works. I look in
the local machine folder and there is the file.
When run as a scheduled job the log shows the ftp mget getting the
file. However, the file does not exist on the local machine when the
job finishes.
As a matter of fact, if I manually execute the scheduled job, it also
shows that is gets file, but file does not exist on local machine.
jeff
On 19 Apr 2006 19:58:53 -0700, "doller" <sufianarif@.gmail.com> wrote:

>What is the login used to start the services of SQL agent.
>from
>Doller

DTS import from zipped flat file

hi there
is it possible to import data from a zipped flat file into a SQL Server 2000
database using DTS in a single step, ie. running the external decompression
application or algorithm, perhaps as a task, from within DTS?
best regards
Loane
In the future you should probably ask DTS questions in the DTS newsgroup to
ensure you get proper attention. But there are plenty of 3rd party tools
that you can use to unzip a file. I see no reason why you can't call one of
them in a task.
Andrew J. Kelly SQL MVP
"Loane Sharp" <look_sharp_not@.hotmail.com> wrote in message
news:eVeNcmiiFHA.3692@.TK2MSFTNGP09.phx.gbl...
> hi there
> is it possible to import data from a zipped flat file into a SQL Server
> 2000 database using DTS in a single step, ie. running the external
> decompression application or algorithm, perhaps as a task, from within
> DTS?
> best regards
> Loane
>

DTS import from zipped flat file

hi there
is it possible to import data from a zipped flat file into a SQL Server 2000
database using DTS in a single step, ie. running the external decompression
application or algorithm, perhaps as a task, from within DTS?
best regards
LoaneIn the future you should probably ask DTS questions in the DTS newsgroup to
ensure you get proper attention. But there are plenty of 3rd party tools
that you can use to unzip a file. I see no reason why you can't call one of
them in a task.
Andrew J. Kelly SQL MVP
"Loane Sharp" <look_sharp_not@.hotmail.com> wrote in message
news:eVeNcmiiFHA.3692@.TK2MSFTNGP09.phx.gbl...
> hi there
> is it possible to import data from a zipped flat file into a SQL Server
> 2000 database using DTS in a single step, ie. running the external
> decompression application or algorithm, perhaps as a task, from within
> DTS?
> best regards
> Loane
>

DTS import from zipped flat file

hi there
is it possible to import data from a zipped flat file into a SQL Server 2000
database using DTS in a single step, ie. running the external decompression
application or algorithm, perhaps as a task, from within DTS?
best regards
LoaneIn the future you should probably ask DTS questions in the DTS newsgroup to
ensure you get proper attention. But there are plenty of 3rd party tools
that you can use to unzip a file. I see no reason why you can't call one of
them in a task.
--
Andrew J. Kelly SQL MVP
"Loane Sharp" <look_sharp_not@.hotmail.com> wrote in message
news:eVeNcmiiFHA.3692@.TK2MSFTNGP09.phx.gbl...
> hi there
> is it possible to import data from a zipped flat file into a SQL Server
> 2000 database using DTS in a single step, ie. running the external
> decompression application or algorithm, perhaps as a task, from within
> DTS?
> best regards
> Loane
>