Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How can I create a SSIS package that reffreshes Excel document connected to SQL data. I need to then save the file to 2 locations using YYYYMM ending?

Posted on 2009-04-26
38
Medium Priority
?
6,848 Views
Last Modified: 2013-11-10
Hi,

I run monthly reporting. SPs generate the reports into tables in SQL 2000 server.
I have a linked workbook that contains links to the report table in SQL.

Everymonth a SSIS package runs the SPs to generate the reports in SQL server.

I then manually go to each report and refresh all sheets in the workbook.
Then I save the template and then save the file into 2 locations.
Is it possible to do this task using SSIS to avoid the need to manually do this.

the report structures are:

TEMPLATE: REPORT YYYYMM.Xls

Final report: G:\...\Report 200903.xls
                     F:\...\Report 200903.xls

PDF FILE SAVED AS: (Using Excel 2007's SAVE AS PDF option)
                      F:\...\Report 200903.pdf
I also save some of the reports as PDF sheets.
Can this also be done using SSIS ?

Thanks,
0
Comment
Question by:zaronline
  • 18
  • 17
36 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24247574
Yep. You do the following:
1. Install Excel on the server that SSIS is running on (can sometimes be an issue)
2. Record an Excel macro which does what you need it to do (refresh data, Save As etc.)
3. Convert that macro from VBA to .Net code
4. Paste the code into a script task in SSIS
There are some other minor complications but thats the gist of it. I can give you more info if need be.
0
 

Author Comment

by:zaronline
ID: 24256289
Hi,
1. SSIS package runs on desktop with Excel installed on it.
2. I have the Excel macro that refreshes all:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.Office.Interop.Excel

Public Class ScriptMain

    Public Sub Main()
        Dim excel As New Microsoft.Office.Interop.Excel.Application
        Dim wb As Microsoft.Office.Interop.Excel.Workbook
        wb = excel.Workbooks.Open("C:\Data\Test.xls")
        wb.RefreshAll()
        wb.Save()
        wb.Close()
        Dts.TaskResult = Dts.Results.Success
    End Sub

End Class
2A. I also need a way to save  the file into 2 locations as below (with current YYYYMM in filename) :
TEMPLATE: REPORT YYYYMM.Xls

Final report: G:\...\Report 200903.xls
                     F:\...\Report 200903.xls
2B. I then also need to save the files as PDF
PDF FILE SAVED AS: (Using Excel 2007's SAVE AS PDF option)
                      F:\...\Report 200903.pdf
3. I do not know how to  Convert that macro from VBA to .Net code. IS THERE A PROGRAM I can use to do this?
 4. I tried to copy the macro directly to SSIS Script task and it did not work too well.
I did find a piece of code that was ment to run macros from SSIS:

'===================
strExcelFile = Replace(WScript.ScriptFullName, WScript.ScriptName, "") & "test.xls"
strMacroName = "run_macro"
Set objExcel = CreateObject("Excel.Application")
Set objWB = objExcel.Workbooks.Open(strExcelFile)
objExcel.Visible = True
objWB.Application.Run strMacroName
objWB.Close False
objExcel.Quit
'===================
But I am unsure how I set this up in a SSIS package.
 
It would be greate if I could get a sample SSIS package that does these tasks.
 
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24285213
So when you pasted that code in, what error did you get? It looks like it should have worked to me. Once you get it working we just add additional lines to do the extra saving etc.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:zaronline
ID: 24291670
Hi Expert,
I am not sure how to setup the SSIS package script task paramaters to execute the Run macro in excel script.
Is there any settings I need to change ? Do I use the ActiveX Script or Script task to perform this task?
What is the VBA macro script I can use to save the files to different locations?
Thanks for you help.
If I send a copy of the SSIS package I have made so far could you correct the bugs in it for me?
0
 

Author Comment

by:zaronline
ID: 24291826
Hi Expert,
 I have attached the error I get when I try to use the SCRIPT Task in SSIS. and the SSIS package that I made to run an excel "save as" task.
I am not able to run the script below due to this error.
'===================
strExcelFile = Replace(WScript.ScriptFullName, WScript.ScriptName, "") & "test.xls"
strMacroName = "run_macro"
Set objExcel = CreateObject("Excel.Application")
Set objWB = objExcel.Workbooks.Open(strExcelFile)
objExcel.Visible = True
objWB.Application.Run strMacroName
objWB.Close False
objExcel.Quit
'===================  
Any help would be appreciated.

SSIS-Refresh-Script-Error.GIF
Excel-Refresh-n-SAVE-dtsx.txt
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24292454
The script that you posted above will only run in an ActiveX script task (not a Script Task). As ActiveX script tasks are only there for backwards compatability, I don't suggest you use it.
What happens when you run the other script (reproduced below) in a Script Task?
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.Office.Interop.Excel

Public Class ScriptMain

   Public Sub Main()
       Dim excel As New Microsoft.Office.Interop.Excel.Application
       Dim wb As Microsoft.Office.Interop.Excel.Workbook
       wb = excel.Workbooks.Open("C:\Data\Test.xls")
       wb.RefreshAll()
       wb.Save()
       wb.Close()
       Dts.TaskResult = Dts.Results.Success
   End Sub

End Class
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24300087
I tried the above code myself but my install can't access the interop assemblies (the Imports Microsoft.Office.Interop.Excel part).
I am going to put in an admin comment to add this to the .Net zone as this is getting out of my area of expertise and I may not have to time to help properly with htis.
Suffice to say, if you just want to get this working, you can paste this bit of code into an ActiveX script task and it will work. Its not really recommended though as eventualy this task will be removed from SSIS.
'===================
strExcelFile = Replace(WScript.ScriptFullName, WScript.ScriptName, "") & "test.xls"
strMacroName = "run_macro"
Set objExcel = CreateObject("Excel.Application")
Set objWB = objExcel.Workbooks.Open(strExcelFile)
objExcel.Visible = True
objWB.Application.Run strMacroName
objWB.Close False
objExcel.Quit
'===================  
0
 

Author Comment

by:zaronline
ID: 24301535
Hi Expert,
I am having no luck running the ActiveX script in SSIS. ive attached the error I recieve. Could I get a sample working version of this.
I have used the following SCRIPT TO RUN:

'===================
strExcelFile = "RCTI YYYYMM.xls"
strMacroName = "mymacro"
Set objExcel = CreateObject("Excel.Application")
Set objWB = objExcel.Workbooks.Open(strExcelFile)
objExcel.Visible = True
objWB.Application.Run strMacroName
objWB.Close False
objExcel.Quit
'===================  
 

ActiveXerror-SSIS.bmp
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24321782
Well, if we pursue the .Net solution just a little further (I am reluctant to support ActiveX in SSIS)
If you drop a Script Task on your page and go into the editor (Design Script)
Now go to Project/Add Reference
Do you see
Microsoft.Office.Interop.Excel
in the list anywhere?
Did you try my suggestion at comment ID 24292454?
0
 

Author Comment

by:zaronline
ID: 24351517
Hi Expert,
I an not able to find the Interop in the Add reference panel.
I have tried your suggestion in  comment ID 24292454 however I get the Red X symbol with an error.
Any other way or is there a way to gain this reference added?
 
Thanks
 
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24353621
I have created this question:
http://www.experts-exchange.com/Programming/Languages/.NET/Q_24387555.html
To try and work it out the Interop assembly thing but no one has been able to help so far.
How about my suggestion at id 24300087, did you try that? (in an ActiveX script task)
I think we can use the CreateObject function in .Net to get around that missing assembly. We'll basically need to convert your code and macro to .Net
0
 

Author Comment

by:zaronline
ID: 24359982
Hi Expert,
I have already tried that in Active X. Please refer to my comment on 24301535. I have attached the DTS package I created and error images.
Could you please have a look at the package and let me know if I am doing anyting incorrect?
Thanks,
 
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24363094
The attached code appears to work intermittently - paste it into a script task. (not ActiveX)
Because we are using late binding (CreateObject, in turn because we can't get the Interop to work), we need to set a tick box:
Inside the actual editor, Press Project / Properties. Under build, untick 'Option Strict On by default'
You'll need to edit the code to
1. Use the paths you want.
2. Add another SaveAs line to save another copy of the workbook
3. If an error occurs, it will show it in a messagebox. You may not want this when its finally working
I ran this a few times but it did not seem to let go of the Excel object.
So basically it is partially working, until I can get time to take a closer look.
 
Have a look and get back to me.


' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
 
Imports System
 
Public Class ScriptMain
 
    Public Sub Main()
        Dim sPeriod As String
        Dim oExcel As Object
        Dim oWB As Object
 
        Dts.TaskResult = Dts.Results.Success
 
        Try
            oExcel = CreateObject("Excel.Application")
            oWB = oExcel.Workbooks.Open("D:\KB\ExpertsExchange\Q_24357093\REPORT YYYYMM.xls")
            sPeriod = (Format(DateTime.Today, "yyyyMM"))
            Call oExcel.ActiveWorkbook.RefreshAll()
            Call oExcel.ActiveWorkbook.SaveAs("D:\KB\ExpertsExchange\Q_24357093\REPORT " & sPeriod & ".xls")
        Catch ex As Exception
            MsgBox(ex.Message)
            Dts.TaskResult = Dts.Results.Failure
        End Try
 
        Call oWB.Close(False)
        oExcel.Quit()
        oExcel = Nothing
 
    End Sub
 
End Class

Open in new window

0
 

Author Comment

by:zaronline
ID: 24370579
Hi Expert,
 
Thank you for this. I have actually compiled an Excel macro that loops through the entire folder and refreshes and saves the data as required. I have over 50 reports, so that was the quickest solution.
I need SSIS to initiate the running of that macro. How can I modify your code to make it execute a macro called "RUN_ALL_MACRO"
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24372763
Thats what  I tried doing first but there were so many Excel security issues around macros, I figured this was the simplest way.
You can try the code below (untested). You should make sure that you untick 'Option Strict On by default' as described above.
Try it and see.
 

Imports System
 
Public Class ScriptMain
 
    Public Sub Main()
        Dim oExcel As Object
        Dim oWB As Object
 
        Dts.TaskResult = Dts.Results.Success
 
        Try
            oExcel = CreateObject("Excel.Application")
            oWB = oExcel.Workbooks.Open("D:\KB\ExpertsExchange\Q_24357093\REPORT YYYYMM.xls")
            oWB.Application.Run "mymacro"
        Catch ex As Exception
            MsgBox(ex.Message)
            Dts.TaskResult = Dts.Results.Failure
        End Try
 
        Call oWB.Close(False)
        oExcel.Quit()
        oExcel = Nothing
 
    End Sub
 
End Class

Open in new window

0
 

Author Comment

by:zaronline
ID: 24469462
Thank you for the update. I have been ill last few days. I will try the solution this week and leave feedback.
0
 

Author Comment

by:zaronline
ID: 24523768
Hi Expert,
I just tested this package. Still getting the same error message as in post 24291826.
Could some one provide me with a sample working SSIS package. I could check if there is anything I am doing wrong on my one.
Thanks
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24542527
Did you untick 'Option Strict On By Default'?. This will allow late binding, which in turn will allow your script to compile (assuming there are no other problems), which in turn will remove the error in comment 24291826.
I have attached a package where this option is unticked and the code compiles OK.
Once you have got around the compilation error, I still can't guarantee that it will work as there are a lot of annoying security lockdowns that stop macros running.
 

Package.txt
0
 

Author Comment

by:zaronline
ID: 24552104
Hi Expert,
I was finally able to run the package. But again the process errored out.
I have attached the error message.
Something to do with Excel macros being disabled.
Any way to get around this issue?

ActiveXerror-SSIS.bmp
0
 

Author Comment

by:zaronline
ID: 24552120
Sorry attached the wrong error immage. Please find the error message for post 24552104.
Macro-Excel-Error.JPG
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24552702
Thats what I meant earlier on about all the macro security making things really difficult. Macros are heavily locked down and it could take you some time to work out how to unlock it.
It should be as easy as setting an 'enable' flag somewhere. Go into your Excel sheet and see if it warns you about macros being locked. If so, unlock them and save the sheet. You may need to do some kind of Excel wid or machine wide macro unlock thing, which of course makes your machine susceptible to macro attacks (which really are a thing of the past if you ask me)
What version of Excel do you have? If its 2007, do you want to post your Excel sheet to me and I can take a look.
If we can't unlock the macro's in the sheet then we need to convert the macro to VBScript (not too hard) and paste the macro into the script task.
It is not an ideal solution - you really are better off with the macro code in Excel - but it is an option.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24552746
This site
http://office.microsoft.com/en-us/ork2003/HA011403071033.aspx
indicates that for 2003, you can either set the Excel macro security to low, or digitally sign the macro. Why don't you set security to low and try your code again.
It is remotely possible that the macro doesn't exist (as per the message) but I assume you've already checked that, and I'm certain it is security related.
0
 

Expert Comment

by:jbentleyp
ID: 24574423
Use SSIS w/o Excel macros...
0
 

Author Comment

by:zaronline
ID: 24608611
Hi Expert,
Good news on one of these samples!!
I have tried ID: 24363094 and this seems to work ok for a simgle file. But there is an issue that the data is not yet refreshed and the file is attemption to save.
How can I make the query wait for the data to refresh before I save the file?
 
 
0
 

Author Comment

by:zaronline
ID: 24608643
I am getting the following error when I try to correct this issue by switching the display alerts off.
How can I set the displayAlerts property off when running this Script task?

SwitchDisplayAlertOff-Error.JPG
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24608914
Glad you made some progress.
Take the CALL out from the front of it.
Call is used when calling a method
Call is not used when setting a property (which is what you're doing)
0
 

Author Comment

by:zaronline
ID: 24618820
Thank you for the tip. Is this  .NET script that I am using in this code?
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24621488
If its inside a script task then yes its .Net script.
The ActiveX script task is for backwards compatability. It is VBScript code (comparable to VB 6)
You are using .Net script to call a COM object by late binding, which is a bit of a mish mash. COM is more used back in VB6 days. thats why we had to mess about to get it to work.
Hope that makes sense.
0
 

Author Comment

by:zaronline
ID: 24634140
Thanks for that tip that worked fine for the ODBC Connected datasets.
It does not however update pivot tables in the dataset. Is there another .NET comand that can update the pivot tables?
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24634825
Apparently the RefreshAll method is meant to refresh them. But if it isn't, you can try using the code on this page:
http://stackoverflow.com/questions/70947/how-can-i-refresh-all-the-pivot-tables-in-my-excel-workbook-with-a-macro
Which shows how to cycle through all pivot tables and refresh them.
I have converted it for you and attached below. Just plug it in before the SaveAs line.

dim w, p
for each w in oExcel.ActiveWorkbook.Worksheets
 
  for each p in w.pivottables
    p.refreshtable
    p.update
  next
next

Open in new window

0
 

Author Comment

by:zaronline
ID: 24643429
Excelent result Expert. This worked like a charm.
I guess there is more than 1 way to skin a cat.
What the MACRO did was loop through a folder and find all the EXCEL files in that folder and refresh each file then saving it to 2 locations.
It seems we have built every thing except the first part: Loop through the folder collect all the Excel sheet names and execute the code for all the Excel files one by one!!.
If this is possible in .NET then I don't need to use the macro in excel at all.
Is there a way to do this?
 
So far I have the following codes working fine for 1 file. we need to do this for all excel files in say: C:\myFolder\ExcelTemps\

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
 
Imports System
 
Public Class ScriptMain
 
    Public Sub Main()
        Dim sPeriod As String
        Dim oExcel As Object
        Dim oWB As Object
 
        Dts.TaskResult = Dts.Results.Success
 
        Try
            oExcel = CreateObject("Excel.Application")
            oWB = oExcel.Workbooks.Open("D:\KB\ExpertsExchange\Q_24357093\REPORT YYYYMM.xls")
            sPeriod = (Format(DateTime.Today, "yyyyMM"))
            Call oExcel.ActiveWorkbook.RefreshAll()
 
	dim w, p
	for each w in oExcel.ActiveWorkbook.Worksheets
 
	  for each p in w.pivottables
    	  	p.refreshtable
	  	p.update
  	    next
	next
 
 
            Call oExcel.ActiveWorkbook.SaveAs("D:\KB\ExpertsExchange\Q_24357093\REPORT " & sPeriod & ".xls")
        Catch ex As Exception
            MsgBox(ex.Message)
            Dts.TaskResult = Dts.Results.Failure
        End Try
 
        Call oWB.Close(False)
        oExcel.Quit()
        oExcel = Nothing
 
    End Sub
 
End Class
  

Open in new window

0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24643786
Yep there are many ways to skin a cat :)
For example to loop through a file you could write some VB.Net code to do it..... or you could use the For Eacg iterator in SSIS.
It depends what you would like to learn more about - SSIS or VB.Net?
I actually suggest SSIS if you are doing a lot of data integration stuff.
0
 

Author Comment

by:zaronline
ID: 24643855
Hi Expert,
I will be gready and ask to know how to do this in both SSIS and VB.NET,
As I am having a winning streak with VB.NET could you please let me know how this can be done in VB.NET first?
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 1000 total points
ID: 24644180
VB6 is my strong point, not VB.Net, but I will have a go.
Here's some coded here:
http://www.devnewsgroups.net/group/microsoft.public.dotnet.framework.adonet/topic50872.aspx
I'm a bit pressed for time so why don't you take a look and see if you can work out how you can fit it in. After some changes, the variable strFileName now contains your workbook name.
A very interesting thing is that they are using Microsoft.Interop, but they are not using Microsoft.Interop.Excel - maybe thats the issue we were having earlier when trying to use Interop.
0
 

Author Comment

by:zaronline
ID: 24679322
Hi Expert,
I am working with this code from the website you provided. Just a quick question, is this an ACTIVEX script or script task?
Import these libraries:

Imports System.IO
Imports Microsoft.Office.Interop

Then in your method body:

        Dim objDirectory As Directory
        Dim arrFileNames As String() = objDirectory.GetFiles("X:\ExcelWorkbooks")
        Dim strFileName As String
        Dim objExcelApplication As New Excel.Application
        Dim objExcelWorkbook As Excel.Workbook
        Dim objExcelWorksheet As Excel.Worksheet

        For Each strFileName In arrFileNames
            objExcelWorkbook =
objExcelApplication.Workbooks.Open(strFileName)
            For Each objExcelWorksheet In objExcelWorkbook.Worksheets
                ' Do something with objExcelWorksheet.Name
            Next
            objExcelWorkbook.Close()
        Next

        objExcelApplication = Nothing

0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 25160539
Sorry I didn't respond - that would be a script task.
Regarding comment 24351517 way up the top, I found some info on using the interop. Apparently SSIS can only see assemblies stored in limited locations. (somewhere under windows I think). It can't see them stored in other standard assmbly locations.
 
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Simple Linear Regression
Introduction to Processes

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question