DTS-to Text file
-
hi does anybody no how i can create a dts package that runs a store proc and sends the results to a file.
-
Use the ExecuteSQL task to execute the stored procedure. Use a rowset output parameter to store the result set in a global variable. Upon success of the ExecuteSQL task, run an ActiveX script task to stream the rowset variable contents to a text file.
-
I created a very simple package with two connections: one sql server, one text destination. I then created a transformation from the sql to the text. I set the source to a query that uses the stored procedure.
Russel Loski, MCSE Business Intelligence, Data Platform
-
Yep, that works too. Russel's solution will be simpler to write. If all you need to do is send the SP results to a file and that file name can be the same each time, use Russel's solution. If you need to have the ability to dynamically change the file name or to use the SP results anywhere else in the package, I would go the ActiveX route.
-
John summarized it very well.
Russel Loski, MCSE Business Intelligence, Data Platform
-
Wicked! cheers guys! didnt think it was that simple
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply