August 18, 2009 at 5:07 pm
I have the following requirement:
We have about 2000 customers. Each customers places order every week and is stored in the OrderHeader table(SQL server 2005 environment). I have to retrive the data(eg. ordernumber, producttype, quantity) for each customer and send them back as a conformation recept in the text file format.
In this case, I have to create 2000 textfile dynamically each week from the table and dump them in the corporate ftp site.
Is it possible in ssis package? any other solution? How can I acheive this task within SQL Server 2005.
I would appreciate your suggestions.
Thanks
August 18, 2009 at 5:19 pm
You could use SSIS to accomplish that. Without spedning too much time, you could use a combination of Script Task and FTP Task in SSIS to do what you need.
Assummption is, 2000 text files will be one for each customer in the database.
Hope that direction helps.
August 18, 2009 at 8:42 pm
Thank you so much for your suggestion.
Could you suggest me the steps to be set up those task within a package.
Thanks
October 14, 2009 at 7:40 am
This can be easily achieved using SQL . Do you need the code snippet ?
October 14, 2009 at 7:47 am
yes, please.
October 14, 2009 at 7:56 am
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 PRINT 'Error in Scripting Object'
--Open a file
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @file_name, 8, 1
IF @OLEResult <> 0 PRINT 'Error in OpenTextFile'
--Write Text1
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1
IF @OLEResult <> 0 PRINT 'Error in WriteLine'
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS
Use the above code to save the file in the specified path.
October 14, 2009 at 7:57 am
What type of text file Is that comma seperated or tab seperated or text with headers ?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply