DTS DataPump from a Stored Procedure

Hi,

assume I have a stored proc which returns a result set. Can I use it as an Sql source query in a TransformData task within DTS ?

The goal was to pick up the result set and create Excel sheets. First I wrote VBA code in Excel, and it worked just fine with OleDb. Later, we found the corporate standard is DTS, so I attemped to set up a Task in the Package Designer Wizard ( no DTS-VBA code )

At the SQL Query box, I have entered: exec my_proc

The preview function shows the data as expected. But in the Destination Tab, I get an empty list of columns. As if DTS would be unable to recognise the column names and types if they come from a stored proc.

I used a workaround, by altering the stored proc to deposit data into a work table. But now I'm still interested to know: is this assumed to work ?Yes
Just invoque your stored procedure on your DTS. Then use the results as you want.

Paulo

Originally posted by andrewsc
Hi,

assume I have a stored proc which returns a result set. Can I use it as an Sql source query in a TransformData task within DTS ?

The goal was to pick up the result set and create Excel sheets. First I wrote VBA code in Excel, and it worked just fine with OleDb. Later, we found the corporate standard is DTS, so I attemped to set up a Task in the Package Designer Wizard ( no DTS-VBA code )

At the SQL Query box, I have entered: exec my_proc

The preview function shows the data as expected. But in the Destination Tab, I get an empty list of columns. As if DTS would be unable to recognise the column names and types if they come from a stored proc.

I used a workaround, by altering the stored proc to deposit data into a work table. But now I'm still interested to know: is this assumed to work ?

DTS and Lotus Notes

I attempted to do a Transformation DataPump between SQL
Server and Lotus Notes and DTS returns "error occurred at
Destination", no further details are provided. The
NotesSQL driver does not appear to like the Insert
statement that DTS is generating. Has DTS been proven to
work using Lotus Notes as a destination? Thanks for any advise.You do know that a lotus notes "database" isa not a database. And since your working with a database, you must have some Lotus Notes "guru" who is pushing for this, why not let them use notrix or pump to get the data.

Deliver a comma delimted file to a location everyday...what? they want real time updates?

How do you do that to a non relational architecture?|||Originally posted by sch5479
I attempted to do a Transformation DataPump between SQL
Server and Lotus Notes and DTS returns "error occurred at
Destination", no further details are provided. The
NotesSQL driver does not appear to like the Insert
statement that DTS is generating. Has DTS been proven to
work using Lotus Notes as a destination? Thanks for any advise.

Why not have the Lotus Notes people setup the DESC sub-system within Lotus Domino and have the system directly access the information wither live or timed?

Frank|||While Notes Databases arn't relational don't see why that DTS has a problem as you can get Crystal report to run against notes data. Personally I wouldn't touch Notrix my preference has always been to get the Domino application to access the data it needs using LotusScript and ODBC which just makes SQL queries to the data.

DTS a large file

I currently have a DTS that runs a Stored procedure.

The stored procedure does a hit from a few table and returns a large amount of data (100K + rows).

I tried to set up the DTS to send me the file as a .csv, but it is simply to big of a file.

Is there a way I can get this to work, do I have to use winzip or something of that nature?

I simply want the sql hit to run each month and the DTS to run the command to email me the file.

Any idea would be appreciated.What other alternatives do you have to emailing ? What will you do with the file once it has been emailed to you ?|||The file is used in a statistical program by the marketing department.

Thanks
Tom|||Instead of emailing, why don't you just ftp ?|||Originally posted by rnealejr
Instead of emailing, why don't you just ftp ?

We were hoping to have the DTS run every month and send me the file.

The idea was to make this an automated as possible.

That is why I hoping I could just have the DTS email me the .csv.

I am not sure what you mean by FTP, I know what FTP is, but are you saying that I should FTP the file after it has been created?

Thanks
Tom|||One of the tasks available with dts is a "file transfer protocol task". How are you creating the csv using dts ?

DTS - Need to Set Destination Filename dynamically

I have a stored procedure that (after selecting, incrementing and updating)
returns the next sequential filename.

I haven't been able to plug it in to the destination textfile filename in my
DTS package.

I know it has something to do with Dynamic Properties and I can navigate to
the property I want to set, but just can't seem to get the result of my
stored procedure into the filename.

I know somebody must have done this before.

Thanks
JoeOne method is to assign the file name value to a global variable in an
Execute SQL task that executes your proc. You can then use a Dynamic
Properties task to assign that global variable value to the desired
property.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Joe Hurzeler" <joe.hurzeler@.verizon.netwrote in message
news:Xns988590E745995Grok@.199.45.49.11...

Quote:

Originally Posted by

>I have a stored procedure that (after selecting, incrementing and updating)
returns the next sequential filename.
>
I haven't been able to plug it in to the destination textfile filename in
my
DTS package.
>
I know it has something to do with Dynamic Properties and I can navigate
to
the property I want to set, but just can't seem to get the result of my
stored procedure into the filename.
>
I know somebody must have done this before.
>
Thanks
Joe

dt_verstampxxx

What is this stored proc used for? It returns version iunformation and has
public permissions. We are looking to remove all public perms where possible.
When I run the following on by SQL 2000 system:
USE pubs
EXEC sp_helptext 'dt_verstamp007'
GO
The result is:
/*
** This procedure returns the version number of the stored
** procedures used by the the Microsoft Visual Database Tools.
** Version is 7.0.05.
*/
create procedure dbo.dt_verstamp007
as
select 7005
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff Ericson" <JeffEricson@.discussions.microsoft.com> wrote in message
news:66B7E3A3-6D0D-4268-99B3-9D41659E747F@.microsoft.com...
> What is this stored proc used for? It returns version iunformation and
> has
> public permissions. We are looking to remove all public perms where
> possible.

dt_verstampxxx

What is this stored proc used for? It returns version iunformation and has
public permissions. We are looking to remove all public perms where possible.When I run the following on by SQL 2000 system:
USE pubs
EXEC sp_helptext 'dt_verstamp007'
GO
The result is:
/*
** This procedure returns the version number of the stored
** procedures used by the the Microsoft Visual Database Tools.
** Version is 7.0.05.
*/
create procedure dbo.dt_verstamp007
as
select 7005
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff Ericson" <JeffEricson@.discussions.microsoft.com> wrote in message
news:66B7E3A3-6D0D-4268-99B3-9D41659E747F@.microsoft.com...
> What is this stored proc used for? It returns version iunformation and
> has
> public permissions. We are looking to remove all public perms where
> possible.

dt_verstampxxx

What is this stored proc used for? It returns version iunformation and has
public permissions. We are looking to remove all public perms where possibl
e.When I run the following on by SQL 2000 system:
USE pubs
EXEC sp_helptext 'dt_verstamp007'
GO
The result is:
/*
** This procedure returns the version number of the stored
** procedures used by the the Microsoft Visual Database Tools.
** Version is 7.0.05.
*/
create procedure dbo.dt_verstamp007
as
select 7005
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff Ericson" <JeffEricson@.discussions.microsoft.com> wrote in message
news:66B7E3A3-6D0D-4268-99B3-9D41659E747F@.microsoft.com...
> What is this stored proc used for? It returns version iunformation and
> has
> public permissions. We are looking to remove all public perms where
> possible.