open and save excel file using script task in ssis

Question

open and save excel file using script task in ssis

sql.pravin

Mr or Mrs. 500

Points: 578
More actions
August 25, 2010 at 3:20 pm

#95066

Hi Everyone,
'excelsheet1.xls' has a table generated ( output in one of the data flow task)
I have a 'excelsheet2.xls' which is a template (standard format at the work place--local drive)
I have reference fields pointing from 'excelsheet1.xls' into 'excelsheet2.xls'.
So whenever excelsheet1.xls is generated(new values), my excelsheet2.xls (template) is updated
I'm using send-mail-task to send this updated excelsheet2.xls to our email group
if i manually open the excelsheet2.xls, i'm able to see the updated values (it asks to save when we close)
problem: i'm not able to see the updated values in the email (it shows the last manually saved date)
should i write any script code (VB) to save the file before sending thru emails ? if so what's the code because i don't know VB
Please give your suggestions 🙂
Thanks
sachin

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

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

WayneS SSC Guru Points: 95405 More actions · Answer 1

If I'm understanding this correctly, 'spreadsheet2' had an external data link to grab data from 'spreadsheet1'. Is this correct?

If so, why do you need to worry about sending it out every time 'spreadsheet1' gets updated? When the users open up 'spreadsheet2', it will then update itself from 'spreadsheet1'. Just ensure that 'spreadsheet2's external data link is via UNC file name to a file share, and the users have read access, and you should be good to go.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

sql.pravin Mr or Mrs. 500 Points: 578 More actions · Answer 2

ya, if the user opens it manually then there is no issue

but the requirement is:

excelsheet2 has to be sent thru email (email group) 🙁

WayneS SSC Guru Points: 95405 More actions · Answer 3

Well, in that case, read this article[/url] about automating Excel. You should be able to take out the T-SQL stuff and use just the script calls to have Excel open then save ExcelSheet2. Use a script task, and just open the file, then save it. Should be pretty simple stuff.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

smacumber 77733 Ten Centuries Points: 1288 More actions · Answer 4

I see you are still an active member so hopefully you get an email for this.

I went to the above article, great by the way, and was to get the code to

function but I have a question.

I am actually trying to open an XML doc in Excel and do Save As an XLS document, which it does.

But when I right click on the XML file and do "Open With" Excel it asks a question how you want the XML doc opened and I choose "As an XML table". And the data comes in the way I need it.

In Excel if I create a macro it would look like the following:

Sub openXML()

ChDir "C:\Test"

Workbooks.OpenXML fileName:="C:\Test\xml_data2.xml", LoadOption:=xlXmlLoadImportToList

End Sub

My question is there a way to incorporate the LoadOption into

declare @xlWorkbook integer

execute @rs = master.dbo.sp_OAMethod @xlWorkbook, 'Open', @xlWorkBook OUTPUT, 'C:\Test\xml_data2.xml'