Running DTS packages from stored procs

[Kurt Zoner]

I read a very promising article ("Customize DTS" by John Woods at
http://msdn.microsoft.com/library/periodic/period99/sql99k8.htm) that described
how to modify and execute a DTS import package using a stored procedure.

After fiddling with it for hours, everything runs great except...nothing
happens. The proc runs cleanly but no data gets imported. I've tried:

- adding error trapping all over the place but I don't get any error messages.
- reversing the package to export data....same result.
- commenting out everything except the parts needed to execute the package..same
result. (the package works fine when executed directly)

I've attached the stored procedure. It's kind of big because of all the
diagnostic messages and error trapping. The article describes how to set up the
package. I used a simple table with three fields and a simple CSV text file with
three fields.

Would greatly appreciate any help!

Kurt Zoner
FaxOne Systems Consulting

[Lars Temme]

Very very nice.

Now as for me it worked perfectly.

See below:
****************************************************************************
*******
DECLARE @Pkg int
DECLARE @dataSource varchar(50)
DECLARE @conn int
DECLARE @hr int
DECLARE @propName varchar(50)
Set nocount on
Exec @hr = sp_OACreate 'DTS.Package', @Pkg out
PRINT @hr
Exec @hr = sp_OAMethod @pkg, 'LoadFromSQLServer',NULL, @@SERVERNAME,
@PackageName = 'test', @Flags = 256
PRINT @hr
Exec @hr = sp_OAGetProperty @Pkg, 'connections(SourceFile)', @conn out
PRINT @hr
Exec @hr = sp_OASetProperty @conn, 'datasource', 'D:\test1.txt'
PRINT @hr
Exec @hr = sp_Oamethod @pkg, 'Execute'
PRINT @hr

-- Cleanup
Exec @hr = sp_OADestroy @conn
Exec @hr = sp_OADestroy @pkg
****************************************************************************
*********

I opted just to keep trying within the Query Analyzer until the package
acctually inserted records into the test table I had created.

My package name is test
The text file conection name is SourceFile

Please tell me what you are planning to do with this I have found some nice
places where I can eliminate a few jobs that are almost identical to each
other.

-Lars

"Kurt Zoner" <kurt...@faxone.com> wrote in message
news:06c001c04a47$6b200d50$33862ecf@cpmsftngxa04...

[Claes Welander]

or You can use xp_cmdshell:
for example

execute master..xp_cmdshell 'dtsrun /S server_name /U user_name /P pwd /N
pkg_name'

Runs the package pkg_name.

/Claes Welander

"Lars Temme" <lte...@loadmedia.com> wrote in message
news:eRIMAnnSAHA.283@cppssbbsa03...

[Kurt Zoner]

Lars:

Why I am doing this:
I have a web-based reporting app with a large database. The users want a way to
extract large amounts of data (100+K rows) for off-line analysis. Pushing that
amount of data through a browser causes problems. I am trying to write a stored
proc that modifies and executes a DTS package to dump the requested data to a
file (txt, xls or mdb). The user can pick up the extract file using Windows
Explorer.

Guess What?:
It works! I think I just had a couple of names twisted. Also it took me a while
to figure out where 'D:\test1.txt' was. I was working on a client PC not the
server.

Next Step:
Since the users don't want the same extract every time, I'd like to have DTS
extract the results of a query where the user provides the parameters via my web
app.

Any idea where to look for more information about the different uses and settings
for sp_OAGetProperty? As well as changing the destination file, it would be nice
to specify type of output (txt,mdb,xls) and more.

Thanks for your help,

Kurt

-Lars

I read a very promising article ("Customize DTS" by John Woods at

.

[Claes Welander]

Suggestions:

A.
If your web app provides parameters about output format and which extract
they want.
You could build the sql dynamically in a stored proc, and depending on the
output format
decide which utility to run ( dts package, sp, bcp... ).
If the output files get created on the server you could set up linked
servers for .xls and .mdb using odbc.
for .txt you could use bcp.

B.
Using dts packages with a transact sql statement which extracts the data you
could take the parameters supplied and
use them in the extract sp, either by storing them in a table which the sp
reads or by altering the sp with the current paramters. ( Another sp that
alters the extract sp )
For the different output formats the dts package would read the current
output format parameter from the table where you
åut them and choose appropriate Transformation.

/Claes Welander

"Kurt Zoner" <kurt...@faxone.com> wrote in message

news:08ae01c04b6a$a029bb80$4b862ecf@cpmsftngxa10...
Lars:

Thanks for your help,

Kurt

-Lars

I read a very promising article ("Customize DTS" by John Woods at

.

[Don Goodman]

Works here too when the sp_OAGetProperty and sp_OASetProperty execs are
disabled; here is sample
I would appreciate any POVs

Don
/****************************************RUN IN QA*************
Requires a package named 'test' with a data source file entry of 'x' in the
DTS package
If the same value as assigned to @Source is defined in the DTS package, it
will work but that seems to nullify the purpose of getting/setting the
properties*/

DECLARE @Pkg int
DECLARE @dataSource varchar(50)
DECLARE @conn int
DECLARE @hr int
DECLARE @propName varchar(50)

DECLARE @Source varchar(255) --= 'D:\MSSQL7\DataFiles\SendtoList.txt'

Set nocount on

set @Source= 'D:\MSSQL7\DataFiles\SendtoList.txt'

Exec @hr = sp_OACreate 'DTS.Package', @Pkg out

Exec @hr = sp_OAMethod @pkg, 'LoadFromSQLServer',NULL,

@@SERVERNAME,@PackageName = 'test', @Flags = 256
Exec @hr = sp_OAGetProperty @Pkg,'connections(source)', @conn out

--PRINT @hr
BEGIN
IF @hr <> 0
GOTO errhandler
END

Exec @hr = sp_OASetProperty @conn, 'datasource', @Source
BEGIN
IF @hr <> 0
GOTO errhandler
END
--PRINT @hr

Exec @hr = sp_Oamethod @pkg, 'Execute'

-- Cleanup

Exec @hr = sp_OADestroy @conn
Exec @hr = sp_OADestroy @pkg

set nocount off

errhandler:
DECLARE @output varchar(255)
--DECLARE @hr int
--DECLARE @source varchar(255)
DECLARE @description varchar(255)

PRINT '****OLE Automation Error Information*****'
EXEC @hr = sp_OAGetErrorInfo @output, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END

"Lars Temme" <lte...@loadmedia.com> wrote in message
news:eRIMAnnSAHA.283@cppssbbsa03...

[Darren Green]

In article <uihdPgp...@cppssbbsa02.microsoft.com>, Don Goodman
<don...@varysoft.com> writes

>Works here too when the sp_OAGetProperty and sp_OASetProperty execs are
>disabled; here is sample
>I would appreciate any POVs
>
>Don

Just for comparison there are a couple of samples on my site, a simple
execute the package, and a more advanced - set some global variables,
and then execute.

http://www.swynk.com/friends/green/dtsole.asp

They work for me on a regular basis.

--
Darren Green
SQL/DTS - http://www.swynk.com/friends/green/

[Don Goodman]

Hi Darren,

Thanks for the suggestion. I suspect the problem with the package begins
with the second parameter required by sp_OAGetProperty. As I change it, I
can get different error reports. If I change the other params. it makes
little difference. Do you have any source info/helps, etc... on that second
parameter?

Thanks

Don

"Darren Green" <darren...@uk.com> wrote in message
news:kgvfJGBW...@freeserve.co.uk...

[Don Goodman]

Hi Darren,

Here is a sample of what I am refering to..
*****************************************
--property code to execute:

Exec @hr = sp_OAGetProperty @Pkg, 'connections(Source)', @conn out

--above returns the error below:

****OLE Automation Error Information*****
Source: Microsoft Data Transformation Services (DTS) Package
Description: Connection 'Source' was not found.

***********************************************************

[Darren Green]

In article <OG5BPhyUAHA.261@cppssbbsa04>, Don Goodman
<don...@varysoft.com> writes

>Hi Darren,
>
>Here is a sample of what I am refering to..
>*****************************************
>--property code to execute:
>
>Exec @hr = sp_OAGetProperty @Pkg, 'connections(Source)', @conn out
>
>--above returns the error below:
>
>****OLE Automation Error Information*****
> Source: Microsoft Data Transformation Services (DTS) Package
> Description: Connection 'Source' was not found.
>
>***********************************************************

Try this-

DECLARE @Conn int, @Catalog varchar(255)

EXEC @hr = sp_OAGetProperty @oPKG, 'Connections("CHARLIE")', @Conn out
EXEC @hr = sp_OAGetProperty @Conn, 'Catalog', @Catalog out

SELECT @Catalog

------- OR

EXEC @hr = sp_OAGetProperty @oPKG, 'Connections("CHARLIE").Catalog',
@Catalog out

SELECT @Catalog

[Don Goodman]

Hi Darren,

Thanks for the suggestion. It returns NULL
Here is the source as tested.
--***************************
Set @propName = 'connections(' + @Source + ')'

EXEC @hr = sp_OAGetProperty @pKG, @propName, @Conn out

EXEC @hr = sp_OAGetProperty @Conn, 'Catalog', @Catalog out

SELECT @Catalog

the second test also returned NULL

thanks

Don

--*********************************

"Darren Green" <darren...@uk.com> wrote in message

news:JB$EBaBbl...@freeserve.co.uk...

[Don Goodman]

Here are some more details.

--***********************
set @Source = '\\servername\MSSQL7\DataFiles\source.txt'

Set @propName = 'connections(' + @Source + ')'

Exec @hr = sp_OAGetProperty @Pkg, @propName, @conn out

returns:

-2147220501

****OLE Automation Error Information*****
Source: Microsoft Data Transformation Services (DTS) Package

Description: Connection '\\servername\MSSQL7\DataFiles\source.txt' was not
found.

--****************************
Package properties
name: test
Task: Bulk Insert Task
Existing Connection: Microsoft OLE DB Provider for SQL Server
Destination Table: valid local table; two columns
Source data file: x
specify format: Row Delimiter {CR}{LF}
Column Delimiter Tab
Options Tab set to defaults

1 Connection in Package:
Existing Connection "Microsoft OLE DB Provder for SQL Server"
Data Source "Microsoft OLE DB Provider for SQL Server"
Server: (local)
Username: valid local admin
Password: valid local pw
Database: valid local database

Thanks

[Darren Green]

In article <eAOk8aLWAHA.243@cppssbbsa03>, Don Goodman
<don...@varysoft.com> writes
>Hi Darren,
>

>Thanks for the suggestion. It returns NULL
>Here is the source as tested.
>--***************************

>Set @propName = 'connections(' + @Source + ')'
>

>EXEC @hr = sp_OAGetProperty @pKG, @propName, @Conn out

>EXEC @hr = sp_OAGetProperty @Conn, 'Catalog', @Catalog out
>
>SELECT @Catalog
>

>the second test also returned NULL
>
>thanks
>
>Don
>

Don, take a closer look and you'll notice I used some double quotes, so
try -

Set @propName = 'connections("' + @Source + '")'

EXEC @hr = sp_OAGetProperty @pKG, @propName, @Conn out

EXEC @hr = sp_OAGetProperty @Conn, 'Catalog', @Catalog out

SELECT @Catalog