February 3, 2009 at 3:54 pm
Hello,
I am far from being expert in this but few years ago I have managed to do this script in DTS. It worked well until we migrated to SQL 2005. I learned that DTS is no longer supported only afterwards and now need to re-work original script. I also want to add a couple more tasks to it so decided to start from the scratch. Please bear with me as like I said I did scratch my head 5-6 years ago and since then forgot absolutely everything. The task is quite simple and trivial though:
1. Export data from two databases into Excel file
2. Add column to Excel file containing value 'NEW STOCK' in each row
3. Email this file to given email address
4. Repeat 1-3 every week
My SQL script looked like this before:
[font="Courier New"]select STOCKA.PN, STOCKA.MFG, STOCKB.QTY
from STOCKA join STOCKB on STOCKB.PN = STOCKA.PN
where STOCKB.QTY>0
union all
...
[/font]
Now I learned that SSIS is *the* tool for the task but am having difficulties tackling it. I was able to run export from the database (in Server Mgmt Studio right click on database -> task -> export) and using the script above the file was successfully created.
I will spend some more time to save export as SSIS project to add emailing capability but for now, can someone please advise of easiest way to add column to the Excel file? The way I see it is somehow include operator which I am not aware of into the recursive script above so that the record is created with the value 'NEW STOCK' and added after other three columns but I am not sure how this could be done. I would imagine something like JOIN + value (I.e. NEW STOCK) but I guess this is erroneous.
Please help
Al
February 4, 2009 at 2:41 am
bump.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply