Re: DTS query result to file - can I loop it?

From: Allan Mitchell (allan_at_no-spam.sqldts.com)
Date: 02/08/05 

Date: Tue, 8 Feb 2005 18:55:17 +0000

You should be able to do this using a combination of a few of our
articles

How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)

Looping, Importing and Archiving
(http://www.sqldts.com/default.aspx?246)

How can I change the filename for a text file connection?
(http://www.sqldts.com/default.aspx?200)

"len" <len@discussions.microsoft.com> wrote in message
news:len@discussions.microsoft.com:
> 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

  • filenames with spaces and list in a for loop
    ... Second attempt was to double quote the "`ls -1A $1`", which generates ONE huge argument to the for loop. ... Unfortunately, the list also generates a newline every 80 characters in the $LIST, so once in a while I was left with each_item being set to something like "\nfilename". ... At the first occurrence of $each_item in the function I would get something like <filename> not found. ... what if I have a directory with more than 65000 characters worth? ...
    (comp.unix.shell)
  • Re: long running perl programs & memory untilization
    ... >> It does setup stuff, and then goes into a loop. ... # reads in temporary capture file adds timestamp, ... # Argument 1 is filename that the labled image should be stored as ... my $grab = $_; ...
    (comp.lang.perl.misc)
  • Re: python skipping lines?
    ... and your first pass through this loop, you exhaust RawData by reading to the end. ... The second pass through the UnitList loop, you pass the exhausted RawData to PullHourlyData. ... I'm thinking you'd need to RawData.seekor some such "rewind" ability. ... Part of the confusion stems from the fact that what you refer to as "filename" is actually a file object that contains state. ...
    (comp.lang.python)
  • Loop thru files and process each one
    ... write a DTS Package to loop through the files and process each one. ... I will need to execute a stored procedure against each file - passing the ... file name into the Stored Proc. ...
    (microsoft.public.sqlserver.dts)
  • Re: Best way to integrate all filenames in a directory with SQL Recordset?
    ... Actual filename value transform from old to new is more like Filename.1 -> ... mismatch lists if something goes wrong. ... I will see what I can do to add another loop mid-stream to catch all file ... > Your destination I can only presume has 4 attributes ...
    (microsoft.public.sqlserver.dts)