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?

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.

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.

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?

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.

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

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
'===================  

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
'===================  

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?

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

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

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,

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

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:

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"

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
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:

Thank you for the update. I have been ill last few days. I will try the solution this week and leave feedback.

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

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.

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?

Sorry attached the wrong error immage. Please find the error message for post 24552104.

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.

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.

Use SSIS w/o Excel macros...

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?

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?

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)

Thank you for the tip. Is this  .NET script that I am using in this code?

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.

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?

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

                                              

1:
2:
3:
4:
5:
6:
7:
8:

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
  

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:

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.

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?

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