Recently I was working with an SSIS package that was loading data to an excel file. As most of us already know this isn’t a big deal and a lot of SSIS developers do this on a regular basis. However, the other day I was thrown a curve ball that I wasn’t expecting and had a request to execute an Excel macro from SSIS. After doing a little bit of searching on the web and getting some assistance from my great team here at Pragmatic Works I have put together this little bit of VB code that’s used with a script task and got it working.
Before you can get started you will have to have downloaded these files if you don’t already have them installed. http://www.microsoft.com/download/en/details.aspx?id=3508
Once the files are installed simply drag over a script task and select VB for the Script Language. I’m also using a variable to supply the file location inside of the script task. So make sure to also pick the user variable for the read only variables.
Next you will have to add a reference within the script task. In the project explorer right click on the project and select ‘Add Reference…’ Then on the COM tab pick the option for, Microsoft Excel 14.0 Object Library. If you don’t have the above file installed you may not see this option.
The user wanted to run the command line program and then wanted to write some commands into Command Line prompt window to achieve some specific task. In an ideal scenario SSIS is not the right choice to develop this type of solution because it is best suited to ETL (Extract.
Now all you have to do is paste this code into the script task and change the variable name to use your variable. Now after the data flow has finished loading the data into the excel file the script task will execute the macro.
‘ Microsoft SQL Server Integration Services Script Task
‘ Write scripts using Microsoft Visual Basic 2008. ‘ The ScriptMain is the entry point class of the script.
Imports System
Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports Microsoft.Office.Interop
_
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 oExcel As Excel.ApplicationClass = Nothing
Dim oBook As Excel.WorkbookClass = Nothing Dim oBooks As Excel.Workbooks = Nothing
Try
‘Start Excel and open the workbook.
oExcel = CreateObject(“Excel.Application”) oExcel.Visible = False oBooks = oExcel.Workbooks oBook = oBooks.Open(Dts.Variables(“StrFilePath”).Value.ToString()) ‘ Change your variable name here.
‘Run the macros.
oExcel.Run(“Format”) ‘ Change the name of your Macro here.
‘Clean-up: Close the workbook and quit Excel.
oBook.Save() oExcel.Quit()
Dts.TaskResult = ScriptResults.Success
Finally
If oBook IsNot Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook) If oBooks IsNot Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks) If oExcel IsNot Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
oBook = Nothing
oBooks = Nothing oExcel = Nothing
End Try
End Sub
End Class
-->
This article describes how to run a SQL Server Integration Services (SSIS) package in an Azure Data Factory pipeline by using the Execute SSIS Package activity.
Prerequisites
Note
This article has been updated to use the new Azure PowerShell Azmodule. You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020.To learn more about the new Az module and AzureRM compatibility, seeIntroducing the new Azure PowerShell Az module. ForAz module installation instructions, see Install Azure PowerShell.
Create an Azure-SSIS integration runtime (IR) if you don't have one already by following the step-by-step instructions in the Tutorial: Provisioning Azure-SSIS IR.
Run a package in the Azure portal
In this section, you use the Data Factory user interface (UI) or app to create a Data Factory pipeline with an Execute SSIS Package activity that runs your SSIS package.
Create a pipeline with an Execute SSIS Package activity
In this step, you use the Data Factory UI or app to create a pipeline. You add an Execute SSIS Package activity to the pipeline and configure it to run your SSIS package.
Run the pipeline
In this step, you trigger a pipeline run.
Monitor the pipeline
Schedule the pipeline with a trigger
You can also create a scheduled trigger for your pipeline so that the pipeline runs on a schedule, such as hourly or daily. For an example, see Create a data factory - Data Factory UI.
Run a package with PowerShell
In this section, you use Azure PowerShell to create a Data Factory pipeline with an Execute SSIS Package activity that runs your SSIS package.
Install the latest Azure PowerShell modules by following the step-by-step instructions in How to install and configure Azure PowerShell.
Create a data factory with Azure-SSIS IR
You can either use an existing data factory that already has Azure-SSIS IR provisioned or create a new data factory with Azure-SSIS IR. Follow the step-by-step instructions in the Tutorial: Deploy SSIS packages to Azure via PowerShell.
Create a pipeline with an Execute SSIS Package activity
In this step, you create a pipeline with an Execute SSIS Package activity. The activity runs your SSIS package.
Run the pipeline
Use the Invoke-AzDataFactoryV2Pipeline cmdlet to run the pipeline. The cmdlet returns the pipeline run ID for future monitoring.
Monitor the pipeline
Run the following PowerShell script to continuously check the pipeline run status until it finishes copying the data. Copy or paste the following script in the PowerShell window, and select Enter.
You can also monitor the pipeline by using the Azure portal. For step-by-step instructions, see Monitor the pipeline.
Schedule the pipeline with a trigger
In the previous step, you ran the pipeline on demand. You can also create a schedule trigger to run the pipeline on a schedule, such as hourly or daily.
Next steps
See the following blog post:
Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
December 2022
Categories |