COPY XL SPREADSHEET FROM ONE XL WORKBOOK TO ANOTHER IN LOOP ?

Question

COPY XL SPREADSHEET FROM ONE XL WORKBOOK TO ANOTHER IN LOOP ?

VoldemarG

Hall of Fame

Points: 3883
More actions
December 4, 2018 at 3:28 pm

#373184

Here is my situation (looking for a way to accomplish this task via SSIS)
400 Excel files ftp-arrive daily to 2 directories : DIR1 and DIR2
There are 400 files in each directory, 1 file per Agency.
Each file in Dir1 and Dir2 is named THE SAME WAY: < ‘ AgencyInfo’ + ‘_’+ @AgencyNumber + ‘.xlsx’ >
In Dir1 they are all called
AgencyInfo_123.XLSX
AgencyInfo_124.XLSX
AgencyInfo_125.XLSX
//………………..//
Etc.
       (three digits at end of file name is Agency Number)
In Dir2 they are all called
AgencyDetails_123.XLSX
AgencyDetails_124.XLSX
AgencyDetails_125.XLSX
//………………..//
Etc.
AgencyInfo% files in DIR1 have 3 spreadsheets each, and AgencyDetails% files in DIR2 have 5 spreadsheets.
TASK that I need to accomplish
(using ANY method that I can call from ANY SSIS TASK) :
-->  Copy Spreadsheet #2 from AgencyDetails% files into AgencyInfo% files (insert it as spreadsheet #4, to the end of other 3 spreadsheets).
So
--> Spreadsheet #2 from AgencyDetails_123.XLSX will be added into AgencyInfo_123.XLSX file               (not touching existing spreadsheets in it)
Spreadsheet #2 from AgencyDetails_124.XLSX will be added into AgencyInfo_124.XLSX file
Spreadsheet #2  from AgencyDetails_125.XLSX  will be added into AgencyInfo_125.XLSX  file
ETC., for all 400 files.
How can I possibly do it in an SSIS Package?
Likes to play Chess

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246959 More actions · Answer 1

If you are prepared to install Excel on the server where SSIS is running (I wouldn't), it should be possible using the Sheet.Copy method – in a script task.

Not pretty and it's going to be slow.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

david_h_edmonds Default port Points: 1430 More actions · Answer 2

I agree with Phil, not pretty and slow.
Another not pretty and slow idea you might try is to use a for each loop to import the files into 2 tables with a filename field and a datetime stamp. Then build package in ssis that creates the files in the output format you desire and loop around the distinct list of filenames.
High level thoughts I know but there might be some mileage in it.

Cheers

Dave

VoldemarG Hall of Fame Points: 3883 More actions · Answer 3

Interesting idea. I like it ! thanks.

Likes to play Chess