dtsrun from stored procedure

Hi all,

I am currently using a series of dts packages to extract and export data. To run the packages, I have a table (TW_DTS) which lists each dts package name, I then use a stored procedure to loop through each and 'dtsrun' it. If a package fails then the error is stored in another table from within the individual dts.

This solution works in principle, however when the stored procedure runs some of the dts packages fail for an unknown reason. Unfortunately the problem is not consistent - it is not always the same package, or even same number of packages that fail. Each package will work when run individually.

I have listed the stored procedure below, any advice may save me from throwing my laptop through the window.

Cheers.

----

CREATE PROCEDURE [spTWDTS] AS

DECLARE @.unique_id varchar(50)
DECLARE @.dts_name varchar(50)
DECLARE @.start_dttm datetime
DECLARE @.end_dttm datetime
DECLARE @.CMD varchar(1000)
DECLARE @.ERROR varchar(1000)

DECLARE
dts_cur CURSOR FOR SELECT unique_id, dts_name, start_dttm, end_dttm from TW_DTS where status = 'A'

open dts_cur

FETCH dts_cur INTO @.unique_id, @.dts_name, @.start_dttm, @.end_dttm

while @.@.fetch_status = 0

BEGIN
-- Set as No Error
SET @.ERROR = 0

--update the start date
UPDATE TW_DTS set start_dttm = getdate() where unique_id = @.unique_id

--run the package
SET @.CMD = 'dtsrun /S myserver /U myusername /P mypassword/N '+@.dts_name
EXECUTE @.ERROR = master..xp_cmdshell @.CMD

--update the end date
UPDATE TW_DTS set end_dttm = getdate() where unique_id = @.unique_id

--move to next dts package
FETCH dts_cur INTO @.unique_id, @.dts_name, @.start_dttm, @.end_dttm

END

CLOSE dts_cur
DEALLOCATE dts_cur

RETURN @.ERROR
GONot sure about this but perhaps dtsrun /L might help in finding out where/when the execution fails. You might want to add a select 'executed: ' + @.dts_name, 'Error', @.error after the xp_cmdshell. I assume there's a space between the password and the /N ? And perhaps adding a 'start /w ' to the command might help.

Like I said, I'm not sure if their any help but it might get you on the right track.|||Thanks Kaiowas,

Tried that and got the following error message in the log file:

Step 'Copy Data from Results to [export].[dbo].[extract] Step' failed

Step Error Source: Microsoft OLE DB Provider for Oracle
Step Error Description:Oracle error occurred, but error message could not be retrieved from Oracle.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:

The step just runs an oracle query and put the results into the 'extract' table.

Not really any the wiser now, do you know how I can get to the oracle error?|||It's an MDAC error message, so Oracle won't help you there. I find google to be very helpful in such cases, it came up with: http://support.microsoft.com/kb/255084/EN-US/

DTSRun Encrypted

In a Multi Server Job under SQL Server Agent (MSX), we have a series of
steps like:

DTSRun /~Z0x8E8635E6BBA...

The ~Z means it's an encrypted hexidecimal string. The person who created
this step is no longer accessible, and the string can't be trivially
decrypted, so I don't know what this runs.

Someone once mentioned that I could go to Query Analyzer, run it, and
watch the execution plan. However, if I start the job, the execution
plan simply walks through the steps required to find the job, and doesn't
go into the details of the job. I don't know how to run the DTSRun
command directly within Query Analyzer.

Is there a way I can use the execution plan to determine what package this
DTSRun line is executing? Please give me the appropriate command or
commands.

-AdamIn theory you could do this using profiler, I have never tried it though...

All DTSRun does does is call the loadpackage and then executepackage methods
on the DTS.Package object, as long as the package is a SQL Server package
and hence gets loaded out of msdb the calls to the sp's in msdb should show
up

"Adam Augusta" <roxton@.wpi.edu> wrote in message
news:Pine.LNX.4.58.0309191701430.27905@.ccc8.WPI.ED U...
> In a Multi Server Job under SQL Server Agent (MSX), we have a series of
> steps like:
> DTSRun /~Z0x8E8635E6BBA...
> The ~Z means it's an encrypted hexidecimal string. The person who created
> this step is no longer accessible, and the string can't be trivially
> decrypted, so I don't know what this runs.
> Someone once mentioned that I could go to Query Analyzer, run it, and
> watch the execution plan. However, if I start the job, the execution
> plan simply walks through the steps required to find the job, and doesn't
> go into the details of the job. I don't know how to run the DTSRun
> command directly within Query Analyzer.
> Is there a way I can use the execution plan to determine what package this
> DTSRun line is executing? Please give me the appropriate command or
> commands.
> -Adam

DTS: how to dynamically import Excel spreadsheet into SQL Server?

(SQL Server 2000, SP3a)
Hello all!
I have a series of Excel spreadsheets, of which I don't know the columns beforehand, that
I'd like to import into SQL Server. These spreadsheets are pretty simple in that they'll
always have a title row and all the data will be on the primary (first) worksheet. I'd
like to dynamically create a *new* table in my database that maps one-to-one with the
columns in the spreadsheet.
Obviously, from the DTS Designer, this is pretty straightforward using the Transform Data
Task on a specific spreadsheet basis. However, I'd like to make the package dynamic,
where I can specify a spreadsheet, have DTS introspect the spreadsheet columns (is this
even possible?), create a destination table, and squirt the data into the table.
I'd be very grateful for any help anyone can provide! :-)
John Peterson
John,
You can do this with an ad hoc query:
select * into #abc
from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel
8.0;Database=c:\excel\yourFile.xls;HDR=YES;IMEX=1' ,Sheet1$)
See the threads at
http://groups.google.com/groups?q=29...8-39393E666E76
for some more information about importing from Excel.
In order to make this dynamic, you'll need to create the entire query
dynamically, something like this:
declare @.sql nvarchar(2000)
set @.sql = 'select * into ##table##
from OpenRowSet( ''Microsoft.Jet.OLEDB.4.0'', ''Excel
8.0;Database=c:\excel\##file##.xls;HDR=YES;IMEX=1' ' ,Sheet1$) '
set @.sql = replace(@.sql,'##file##','thisfileistheone')
set @.sql = replace(@.sql,'##table##','thisisthetablenametogive it)
exec(@.sql)
Do not let users type in filenames, or if you do, replace using
QUOTENAME(@.filename,char(39)) and not without the quotename. Failure to
do this opens the door for SQL Injection attacks. See
http://www.sommarskog.se/dynamic_sql.html
Steve Kass
Drew University
John Peterson wrote:

>(SQL Server 2000, SP3a)
>Hello all!
>I have a series of Excel spreadsheets, of which I don't know the columns beforehand, that
>I'd like to import into SQL Server. These spreadsheets are pretty simple in that they'll
>always have a title row and all the data will be on the primary (first) worksheet. I'd
>like to dynamically create a *new* table in my database that maps one-to-one with the
>columns in the spreadsheet.
>Obviously, from the DTS Designer, this is pretty straightforward using the Transform Data
>Task on a specific spreadsheet basis. However, I'd like to make the package dynamic,
>where I can specify a spreadsheet, have DTS introspect the spreadsheet columns (is this
>even possible?), create a destination table, and squirt the data into the table.
>I'd be very grateful for any help anyone can provide! :-)
>John Peterson
>
>
|||Steve Kass <skass@.drew.edu> wrote in message ...
[vbcol=seagreen]

> You can do this with an ad hoc query:
> select * into #abc
> from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel
> 8.0;Database=c:\excel\yourFile.xls;HDR=YES;IMEX=1' ,Sheet1$)
Now all the OP needs to know is how to find the name of the first
worksheet. The first sheet isn't necessarily named Sheet1 and vice
versa. Hint: how would you find the name of the first table in a SQL
Server DB?
Jamie.
|||Oh -- that's clever! I think I can work with that -- thanks guys!
One question: does the OPENQUERY operate on the path from the SQL Server? I'm betting
that it does, which adds a considerable wrinkle...
Jamie, in my case, I think that there will only be one Worksheet that I need to worry
about -- so I'm hoping that determining which is the "first" sheet won't be a problem.
Though, if I understand your concern correctly -- it seems like I need to know the *name*
of that worksheet beforehand in the OPENQUERY?
"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
news:2ed66b75.0407150616.17615762@.posting.google.c om...
> Steve Kass <skass@.drew.edu> wrote in message ...
>
> Now all the OP needs to know is how to find the name of the first
> worksheet. The first sheet isn't necessarily named Sheet1 and vice
> versa. Hint: how would you find the name of the first table in a SQL
> Server DB?
> Jamie.
> --
|||"John Peterson" wrote ...

> Jamie, in my case, I think that there will
> only be one Worksheet that I need to worry
> about -- so I'm hoping that determining which
> is the "first" sheet won't be a problem.
> Though, if I understand your concern correctly
> -- it seems like I need to know the *name*
> of that worksheet beforehand in the OPENQUERY?
Correct, unless you are using a defined Name ('named range') in the
Excel workbook in which case you'd need to know the Name's name.
Jamie.
|||I'm still at kind of an impasse with this. I had thought that Steve's suggestion might
work for me, but there are issues with OPENROWSET that are kind of stymieing me.
Now I'm kind of thinking that if I can introspect the Excel spreadsheet to identify all
the columns that are "in use" and create Transformations for them, maybe I can do
something with that. Problem is, that seems like a *lot* of work.
If anyone has any other suggestions, I'd be obliged!
"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
news:2ed66b75.0407160030.7a3ae242@.posting.google.c om...
> "John Peterson" wrote ...
>
> Correct, unless you are using a defined Name ('named range') in the
> Excel workbook in which case you'd need to know the Name's name.
> Jamie.
> --
|||John,
You can retrieve the sheet and named region names from an excel file
that has been added as a linked server, if that's some help:
exec sp_tables_ex exlsrv
Can you be more specific about what your Excel file contains and what
you need to retrieve?
Steve
John Peterson wrote:

>I'm still at kind of an impasse with this. I had thought that Steve's suggestion might
>work for me, but there are issues with OPENROWSET that are kind of stymieing me.
>Now I'm kind of thinking that if I can introspect the Excel spreadsheet to identify all
>the columns that are "in use" and create Transformations for them, maybe I can do
>something with that. Problem is, that seems like a *lot* of work.
>If anyone has any other suggestions, I'd be obliged!
>
>"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
>news:2ed66b75.0407160030.7a3ae242@.posting.google. com...
>
>
>

DTS: how to dynamically import Excel spreadsheet into SQL Server?

(SQL Server 2000, SP3a)
Hello all!
I have a series of Excel spreadsheets, of which I don't know the columns beforehand, that
I'd like to import into SQL Server. These spreadsheets are pretty simple in that they'll
always have a title row and all the data will be on the primary (first) worksheet. I'd
like to dynamically create a *new* table in my database that maps one-to-one with the
columns in the spreadsheet.
Obviously, from the DTS Designer, this is pretty straightforward using the Transform Data
Task on a specific spreadsheet basis. However, I'd like to make the package dynamic,
where I can specify a spreadsheet, have DTS introspect the spreadsheet columns (is this
even possible?), create a destination table, and squirt the data into the table.
I'd be very grateful for any help anyone can provide! :-)
John PetersonJohn,
You can do this with an ad hoc query:
select * into #abc
from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel
8.0;Database=c:\excel\yourFile.xls;HDR=YES;IMEX=1' ,Sheet1$)
See the threads at
http://groups.google.com/groups?q=29C76785-22D9-46A2-A398-39393E666E76
for some more information about importing from Excel.
In order to make this dynamic, you'll need to create the entire query
dynamically, something like this:
declare @.sql nvarchar(2000)
set @.sql = 'select * into ##table##
from OpenRowSet( ''Microsoft.Jet.OLEDB.4.0'', ''Excel
8.0;Database=c:\excel\##file##.xls;HDR=YES;IMEX=1'' ,Sheet1$) '
set @.sql = replace(@.sql,'##file##','thisfileistheone')
set @.sql = replace(@.sql,'##table##','thisisthetablenametogiveit)
exec(@.sql)
Do not let users type in filenames, or if you do, replace using
QUOTENAME(@.filename,char(39)) and not without the quotename. Failure to
do this opens the door for SQL Injection attacks. See
http://www.sommarskog.se/dynamic_sql.html
Steve Kass
Drew University
John Peterson wrote:
>(SQL Server 2000, SP3a)
>Hello all!
>I have a series of Excel spreadsheets, of which I don't know the columns beforehand, that
>I'd like to import into SQL Server. These spreadsheets are pretty simple in that they'll
>always have a title row and all the data will be on the primary (first) worksheet. I'd
>like to dynamically create a *new* table in my database that maps one-to-one with the
>columns in the spreadsheet.
>Obviously, from the DTS Designer, this is pretty straightforward using the Transform Data
>Task on a specific spreadsheet basis. However, I'd like to make the package dynamic,
>where I can specify a spreadsheet, have DTS introspect the spreadsheet columns (is this
>even possible?), create a destination table, and squirt the data into the table.
>I'd be very grateful for any help anyone can provide! :-)
>John Peterson
>
>|||Steve Kass <skass@.drew.edu> wrote in message ...
> > I have a series of Excel spreadsheets, of which
> > I don't know the columns beforehand, that
> > I'd like to import into SQL Server. These spreadsheets
> > are pretty simple in that they'll
> > always have a title row and all the data will be
> > on the primary (first) worksheet.
> You can do this with an ad hoc query:
> select * into #abc
> from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel
> 8.0;Database=c:\excel\yourFile.xls;HDR=YES;IMEX=1' ,Sheet1$)
Now all the OP needs to know is how to find the name of the first
worksheet. The first sheet isn't necessarily named Sheet1 and vice
versa. Hint: how would you find the name of the first table in a SQL
Server DB?
Jamie.
--|||Oh -- that's clever! I think I can work with that -- thanks guys!
One question: does the OPENQUERY operate on the path from the SQL Server? I'm betting
that it does, which adds a considerable wrinkle...
Jamie, in my case, I think that there will only be one Worksheet that I need to worry
about -- so I'm hoping that determining which is the "first" sheet won't be a problem.
Though, if I understand your concern correctly -- it seems like I need to know the *name*
of that worksheet beforehand in the OPENQUERY?
"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
news:2ed66b75.0407150616.17615762@.posting.google.com...
> Steve Kass <skass@.drew.edu> wrote in message ...
> > > I have a series of Excel spreadsheets, of which
> > > I don't know the columns beforehand, that
> > > I'd like to import into SQL Server. These spreadsheets
> > > are pretty simple in that they'll
> > > always have a title row and all the data will be
> > > on the primary (first) worksheet.
> > You can do this with an ad hoc query:
> >
> > select * into #abc
> > from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel
> > 8.0;Database=c:\excel\yourFile.xls;HDR=YES;IMEX=1' ,Sheet1$)
> Now all the OP needs to know is how to find the name of the first
> worksheet. The first sheet isn't necessarily named Sheet1 and vice
> versa. Hint: how would you find the name of the first table in a SQL
> Server DB?
> Jamie.
> --|||"John Peterson" wrote ...
> Jamie, in my case, I think that there will
> only be one Worksheet that I need to worry
> about -- so I'm hoping that determining which
> is the "first" sheet won't be a problem.
> Though, if I understand your concern correctly
> -- it seems like I need to know the *name*
> of that worksheet beforehand in the OPENQUERY?
Correct, unless you are using a defined Name ('named range') in the
Excel workbook in which case you'd need to know the Name's name.
Jamie.
--|||I'm still at kind of an impasse with this. I had thought that Steve's suggestion might
work for me, but there are issues with OPENROWSET that are kind of stymieing me.
Now I'm kind of thinking that if I can introspect the Excel spreadsheet to identify all
the columns that are "in use" and create Transformations for them, maybe I can do
something with that. Problem is, that seems like a *lot* of work.
If anyone has any other suggestions, I'd be obliged!
"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
news:2ed66b75.0407160030.7a3ae242@.posting.google.com...
> "John Peterson" wrote ...
> > Jamie, in my case, I think that there will
> > only be one Worksheet that I need to worry
> > about -- so I'm hoping that determining which
> > is the "first" sheet won't be a problem.
> > Though, if I understand your concern correctly
> > -- it seems like I need to know the *name*
> > of that worksheet beforehand in the OPENQUERY?
> Correct, unless you are using a defined Name ('named range') in the
> Excel workbook in which case you'd need to know the Name's name.
> Jamie.
> --|||John,
You can retrieve the sheet and named region names from an excel file
that has been added as a linked server, if that's some help:
exec sp_tables_ex exlsrv
Can you be more specific about what your Excel file contains and what
you need to retrieve?
Steve
John Peterson wrote:
>I'm still at kind of an impasse with this. I had thought that Steve's suggestion might
>work for me, but there are issues with OPENROWSET that are kind of stymieing me.
>Now I'm kind of thinking that if I can introspect the Excel spreadsheet to identify all
>the columns that are "in use" and create Transformations for them, maybe I can do
>something with that. Problem is, that seems like a *lot* of work.
>If anyone has any other suggestions, I'd be obliged!
>
>"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
>news:2ed66b75.0407160030.7a3ae242@.posting.google.com...
>
>>"John Peterson" wrote ...
>>
>>Jamie, in my case, I think that there will
>>only be one Worksheet that I need to worry
>>about -- so I'm hoping that determining which
>>is the "first" sheet won't be a problem.
>>Though, if I understand your concern correctly
>>-- it seems like I need to know the *name*
>>of that worksheet beforehand in the OPENQUERY?
>>
>>Correct, unless you are using a defined Name ('named range') in the
>>Excel workbook in which case you'd need to know the Name's name.
>>Jamie.
>>--
>>
>
>

DTS: how to dynamically import Excel spreadsheet into SQL Server?

(SQL Server 2000, SP3a)
Hello all!
I have a series of Excel spreadsheets, of which I don't know the columns bef
orehand, that
I'd like to import into SQL Server. These spreadsheets are pretty simple in
that they'll
always have a title row and all the data will be on the primary (first) work
sheet. I'd
like to dynamically create a *new* table in my database that maps one-to-one
with the
columns in the spreadsheet.
Obviously, from the DTS Designer, this is pretty straightforward using the T
ransform Data
Task on a specific spreadsheet basis. However, I'd like to make the package
dynamic,
where I can specify a spreadsheet, have DTS introspect the spreadsheet colum
ns (is this
even possible?), create a destination table, and squirt the data into the ta
ble.
I'd be very grateful for any help anyone can provide! :-)
John PetersonJohn,
You can do this with an ad hoc query:
select * into #abc
from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel
8.0;Database=c:\excel\yourFile.xls;HDR=YES;IMEX=1' ,Sheet1$)
See the threads at
http://groups.google.com/groups?q=2...98-39393E666E76
for some more information about importing from Excel.
In order to make this dynamic, you'll need to create the entire query
dynamically, something like this:
declare @.sql nvarchar(2000)
set @.sql = 'select * into ##table##
from OpenRowSet( ''Microsoft.Jet.OLEDB.4.0'', ''Excel
8.0;Database=c:\excel\##file##.xls;HDR=YES;IMEX=1'' ,Sheet1$) '
set @.sql = replace(@.sql,'##file##','thisfileistheon
e')
set @.sql = replace(@.sql,'##table##','thisisthetable
nametogiveit)
exec(@.sql)
Do not let users type in filenames, or if you do, replace using
QUOTENAME(@.filename,char(39)) and not without the quotename. Failure to
do this opens the door for SQL Injection attacks. See
http://www.sommarskog.se/dynamic_sql.html
Steve Kass
Drew University
John Peterson wrote:

>(SQL Server 2000, SP3a)
>Hello all!
>I have a series of Excel spreadsheets, of which I don't know the columns be
forehand, that
>I'd like to import into SQL Server. These spreadsheets are pretty simple i
n that they'll
>always have a title row and all the data will be on the primary (first) wor
ksheet. I'd
>like to dynamically create a *new* table in my database that maps one-to-on
e with the
>columns in the spreadsheet.
>Obviously, from the DTS Designer, this is pretty straightforward using the
Transform Data
>Task on a specific spreadsheet basis. However, I'd like to make the packag
e dynamic,
>where I can specify a spreadsheet, have DTS introspect the spreadsheet colu
mns (is this
>even possible?), create a destination table, and squirt the data into the t
able.
>I'd be very grateful for any help anyone can provide! :-)
>John Peterson
>
>|||Steve Kass <skass@.drew.edu> wrote in message ...

[vbcol=seagreen]
> You can do this with an ad hoc query:
> select * into #abc
> from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel
> 8.0;Database=c:\excel\yourFile.xls;HDR=YES;IMEX=1' ,Sheet1$)
Now all the OP needs to know is how to find the name of the first
worksheet. The first sheet isn't necessarily named Sheet1 and vice
versa. Hint: how would you find the name of the first table in a SQL
Server DB?
Jamie.|||Oh -- that's clever! I think I can work with that -- thanks guys!
One question: does the OPENQUERY operate on the path from the SQL Server?
I'm betting
that it does, which adds a considerable wrinkle...
Jamie, in my case, I think that there will only be one Worksheet that I need
to worry
about -- so I'm hoping that determining which is the "first" sheet won't be
a problem.
Though, if I understand your concern correctly -- it seems like I need to kn
ow the *name*
of that worksheet beforehand in the OPENQUERY?
"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
news:2ed66b75.0407150616.17615762@.posting.google.com...
> Steve Kass <skass@.drew.edu> wrote in message ...
>
>
> Now all the OP needs to know is how to find the name of the first
> worksheet. The first sheet isn't necessarily named Sheet1 and vice
> versa. Hint: how would you find the name of the first table in a SQL
> Server DB?
> Jamie.
> --|||"John Peterson" wrote ...

> Jamie, in my case, I think that there will
> only be one Worksheet that I need to worry
> about -- so I'm hoping that determining which
> is the "first" sheet won't be a problem.
> Though, if I understand your concern correctly
> -- it seems like I need to know the *name*
> of that worksheet beforehand in the OPENQUERY?
Correct, unless you are using a defined Name ('named range') in the
Excel workbook in which case you'd need to know the Name's name.
Jamie.|||I'm still at kind of an impasse with this. I had thought that Steve's sugge
stion might
work for me, but there are issues with OPENROWSET that are kind of stymieing
me.
Now I'm kind of thinking that if I can introspect the Excel spreadsheet to i
dentify all
the columns that are "in use" and create Transformations for them, maybe I c
an do
something with that. Problem is, that seems like a *lot* of work.
If anyone has any other suggestions, I'd be obliged!
"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
news:2ed66b75.0407160030.7a3ae242@.posting.google.com...
> "John Peterson" wrote ...
>
> Correct, unless you are using a defined Name ('named range') in the
> Excel workbook in which case you'd need to know the Name's name.
> Jamie.
> --|||John,
You can retrieve the sheet and named region names from an excel file
that has been added as a linked server, if that's some help:
exec sp_tables_ex exlsrv
Can you be more specific about what your Excel file contains and what
you need to retrieve?
Steve
John Peterson wrote:

>I'm still at kind of an impasse with this. I had thought that Steve's sugg
estion might
>work for me, but there are issues with OPENROWSET that are kind of stymiein
g me.
>Now I'm kind of thinking that if I can introspect the Excel spreadsheet to
identify all
>the columns that are "in use" and create Transformations for them, maybe I
can do
>something with that. Problem is, that seems like a *lot* of work.
>If anyone has any other suggestions, I'd be obliged!
>
>"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
>news:2ed66b75.0407160030.7a3ae242@.posting.google.com...
>
>
>