Current Position:Home > SSIS Script task not executing macro through SQL Agent (but it does through bids)

SSIS Script task not executing macro through SQL Agent (but it does through bids)

Update:10-11Source: network consolidation
Advertisement
<p>Hello everyone,</p><p>I am having an issue with SQL Agent when executing a macro contained in a script task component. The script task actually opens an excel file, runs the macro, save and closes the file. </p><p>When
I execute the package via BIDS/Visual studio, it works like a charm. However, when i execute the package with SQL agent, the package runs successfully but it seems that the macro is not executed as the excel file has not been modified as it should have. Also,
the history log does not show any error messages. </p><p>Could </p>

The Best Answer

Advertisement
Thanks!I did create a credential and a proxy too but still the macro is not executed.I have searched online for solutions but no one has experimented this kind of issue before it seems. Please have a look at the script task code:
Imports
Excel = Microsoft.Office.Interop.Excel
Imports
System
Imports
System.Data
Imports
System.Math
Imports
Microsoft.SqlServer.Dts.Runtime
<System.AddIn.AddIn(
"ScriptMain", Version:="1.0",
Publisher:="", Description:="")>
<System.CLSCompliantAttribute(
False)> _
Partial
Public
Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End
Enum
Public
Sub Main()
Dim Macro_name
As
String
Dim ExcelObject
As
New Microsoft.Office.Interop.Excel.Application
Dim oBook
As Microsoft.Office.Interop.Excel.Workbook
Dim oBooks
As Microsoft.Office.Interop.Excel.Workbooks
Try
Macro_name =
"Macro001"
ExcelObject =
CType(CreateObject("Excel.Application"),
Excel.Application)
ExcelObject.Visible =
True
ExcelObject.UserControl =
False
ExcelObject.DisplayAlerts =
False
oBooks = ExcelObject.Workbooks
oBook =
CType(oBooks.Open("C\Book1.xls"),
Excel.WorkbookClass)
ExcelObject.Run(Macro_name)
Catch ex
As Exception
ExcelObject.Application.Quit()
ExcelObject.DisplayAlerts =
True
ExcelObject =
Nothing
End
Try
Dts.TaskResult = ScriptResults.Success
End
Sub
End
Class
  • SSIS Script task not executing macro through SQL Agent (but it does through bids) Update:10-11

    <p>Hello everyone,</p><p>I am having an issue with SQL Agent when executing a macro contained in a script task component. The script task actually opens an excel file, runs the macro, save and closes the file. </p><p>When I e

  • Running a Select query against multiple sql servers using SSIS script task. Update:11-30

    Hi Guys, I need to fetch data from multiple sql servers using  SSIS scirpt task inside a foreach container. is there anyway i can build dynamic sql connections using ssis variables inside SSIS script task in each loop Please guide me or refer any blo

  • NameSpace for XML But in SSIS Script Task Update:10-11

    Hello,  I am creating an xml Document that pulls information from a staging table in sql. However the issue I am having is that i need  a namespace or a start element to handle a ':' but i keep getting its not a hexadecimal blah blah.  I feel like I

  • SSIS : Read Rows from an Object variable in SSIS Script Task which is looped many times. Update:10-11

    Hello All, Here is what I am trying to do... 1. I am having two rows, one column in an Object Variable. (vLoopCountObj). 2. I am having 30 Rows, 2 Columns in my second Object  Variable (vTableRowsObj) 3. I have a FOR EACH LOOP which will run for numb

  • SSIS/Script Task/ VB 2008 Help Urgent... Update:10-11

    Hi Guys,  Simple Script Task, just rename Excel tab, here is my code... Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports Microsoft.Office.Interop.Excel Imports System.IO Imports System.Text <Syste

  • Getting COM Component error while opening SSIS Script task. Update:10-11

    Hi All, While click on EDIT SCRIPT Button in script task it's not opening the script editing window. Getting below error message: "Error HRESULT E_FAIL has been returned from a call to a COM component. (EnvDTE)" We have added ADODB reference in