Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Location
    Memphis
    Posts
    62

    Unanswered: Multiple Files -- DTS

    I have a rather large sale transaction DB. Basic header, and detail tables. I am providing a third party company with daily sales information, and I need to give them back data from about 8 or 9 months ago. I currently have a DTS package that gets sales for the current day, but since I have to go back, I have to manually edit the query in the DTS package, and change the date range...UNLESS ...

    Blah, blah, blah. The problem is that they can only take the data in Daily files. So, there would be ONE file for each day. I really don't need to be manually running these jobs, so I'm wondering if someone could point me to a way of writing a package (maybe ActiveX, not sure) that would run through a loop, basically, of dates, and create a seperate file for each day. Versus having to edit a generic DTS package, and changing the date range 350 times...
    Last edited by AnSQLQuery; 05-18-04 at 23:25.

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    would it be an option to select the current daterange from a table. Then, as a last step, update daterange to the next (or have a daterange column and another column that acts as a 'done this one' and update that column)? If the package simply exports the table contents you could also consider creating a dynamic sp but I am not sure if that's an option for you.

  3. #3
    Join Date
    Sep 2003
    Location
    Memphis
    Posts
    62
    That does make sense, but I am not sure how I would go about "looping" through the Daterange, and then creating a file for each day based on the query that is in the one DTS package.

  4. #4
    Join Date
    Sep 2003
    Location
    Memphis
    Posts
    62

    ok

    This is what I've done, and it's led me to another question:


    @Date1=(SELECT BeginDate FROM DateRangeTable)
    @Date2=(SELECT EndDate FROM DateRangeTable)


    ...SELECT BLAH, BLAH...WHERE Date BETWEEN @Date1 AND @Date2


    UPDATE DateRange SET Loaded='YES' WHERE BeginDate=@Date1

    EXEC master..xp_cmdshell 'REN I:\DailySales ' + @Date1


    -----

    By itself, the EXEC xp_cmdshell runs just fine. However, when I include The Main query, It doesn't work at all with NO errors...anyone know what gives?

  5. #5
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Does it have to be formatted a certain way? You could certainly do it in ActiveX, or simply use xp_cmdshell to execute BCP:


    EXEC master..xp_cmdshell 'bcp ' + 'SELECT BLAH, BLAH...WHERE Date BETWEEN @Date1 AND @Date2'
    That which does not kill me postpones the inevitable.

  6. #6
    Join Date
    Sep 2003
    Location
    Memphis
    Posts
    62

    format

    Yes, the DTS Package has a certain format that I haven't been able to figure out how to match with bcp.

  7. #7
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    I haven't worked with BCP for sometime, but it does have a parameter for a "format file". If you can't get BCP to format it the way you want, you can do it in ActiveX.
    That which does not kill me postpones the inevitable.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •