DTS query result to file - can I loop it?

From: len (len_at_discussions.microsoft.com)
Date: 02/08/05 

Date: Tue, 8 Feb 2005 04:05:02 -0800

Hi there.

I have a scheduled DTS package set up on my SQL Server with the following
properties:

Source: a stored procedure which can take a parameter
Destination: a csv file on disk with a dynamic filename (each time the task
is run, a new filename is generated from an SQL query using getdate())

At the moment, no parameter is passed to the stored proc and only one file
per day is output. I would like however, to pass a parameter @MyType which
has a range of values - and to generate a new file for each value.

eg: The task is scheduled daily. For each Day, I'd like the following to
happen:

source destination
EXECUTE p_GetResults @Mytype = 1 1.csv
EXECUTE p_GetResults @Mytype = 2 2.csv
EXECUTE p_GetResults @Mytype = 3 3.csv
.
.
Note: The values for @MyType are stored in a database table so a cursor
query would be involved somewhere....

My question (finally!) is: Is it possible to create this loop by editing my
existing DTS package or will I have to create a new package to handle each
value for @MyType (which would be tricky as I need to query a table for those
values..)

I know this could be a trick one - any help would appreciated though!

Relevant Pages

  • Re: DTS Error... while reading file from mapped drive
    ... >In a DTS package I am using TextFile as data source. ... >Is it not possible to run successfully the scheduled DTS package when we ... >have a source file like this? ... global community for SQL Server professionals ...
    (microsoft.public.sqlserver.dts)
  • Re: How to capture SQL statement messages in DTS log file
    ... I have several SQL Server 2000s and a couple of SQL Server 2005 servers ... Then instead of PRINT in your DTS package, ... The execution of the following DTS Package succeeded: ... Microsoft Online Community Support ...
    (microsoft.public.sqlserver.dts)
  • Re: export access to sql server
    ... i followed article from sql server books MSDN. ... Running a DTS Package Saved as a Visual Basic File ... Actually my software will support access ... If the app needs to dynamically recreate the ...
    (microsoft.public.vb.general.discussion)
  • Executing a DTS package from a Web application.
    ... Can a SQL Server 2000 DTS Package be run from a Web application? ... The SQL Server database is not local. ... Package Execution Lineage: ...
    (microsoft.public.sqlserver.dts)
  • Re: DTS Problem General Network error
    ... Charles Kangai schrieb: ... Author of Learning Tree's 4-day course: "SQL Server 2005 Integration ... email alias: charles ... we are using SISS package (no DTS package sorry). ...
    (microsoft.public.sqlserver.dts)