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...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.|||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.|||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?|||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'|||Yes, the DTS Package has a certain format that I haven't been able to figure out how to match with bcp.|||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.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment