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...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.

Multiple database access problem

Hi ,

Im developing a web application which would make access to multiple distributive databases. The problem is...the database from all the third parties may have different standard.
For instance...a user wish to retrive a record from many databases, he input "Pentium 4" ....but some database may have different standard, they may have a record with name "P4" instead of "Pentium 4"...so my question is , how should i make it standard ?? thanksEither use translation tables or enforce standard values through the use of foreign keys to static lookup lists.|||Great, would appreciate if you could describe more in detail ...thanks a lot|||A translation table is a table in a central database that contains the datasource, foreignvalue, and commonvalue for all the elements that need to be combined across databases.

Lookup tables are tables within each database that limit the user's choices to a selection of consistent values.