December 3, 2019 at 7:15 pm
I am testing an SSIS package to see if we have all required software installed on the server to run SSIS jobs.
Server config:
Windows Server 2016, SQL Server 2016, Visual Studio 2017 (on server)
Package has 1 Script Task, using VB.Net 2017, added Reference to Microsoft Excel 15.0 Object Library for Interop assembly.
Script Code:
#Region "Imports"
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
#End Region
<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Public Sub Main()
Dim lobjExcel As Object 'Instance Of The Excel Application Object
Dim lobjWorkBook As Object 'Instance For WorkBook Collection
Dim lstrExcelFile As String = CType(Dts.Variables("User::LabExcelFileFullPath").Value, String)
Dim lstrTextFile As String = CType(Dts.Variables("User::LabTextFileFullPath").Value, String)
lobjExcel = CreateObject("Excel.Application")
lobjWorkBook = lobjExcel.Workbooks.Open(lstrExcelFile)
lobjExcel.DisplayAlerts = False
'Saving In The Tab Delimited Format
lobjWorkBook.SaveAs(lstrTextFile, -4158)
lobjWorkBook.Close()
Call ReleaseCom(lobjWorkBook)
'Destroying The Excel Object
lobjExcel.Quit()
Call ReleaseCom(lobjExcel)
GC.Collect()
Dts.TaskResult = ScriptResults.Success
End Sub
Sub ReleaseCom(ByVal o As Object)
Try
If Not o Is Nothing Then
System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
End If
Catch ex As Exception
o = Nothing
Finally
If Not o Is Nothing Then o = Nothing
End Try
End Sub
Exception:
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
It's been a while since I have coded in VB.Net so I am probably missing something simple. I inherited this from another developer. Any advice would be great!
Scott
December 3, 2019 at 7:40 pm
are you aware that interop requires Excel to be installed on the server?
Not only that is not supported by Microsoft but it also has licensing implications and costs that your company may not be expecting.
and its not just software that needs to be installed - sometimes particular folders have to be created for "UI based software" to work as a service
December 3, 2019 at 7:56 pm
Frederico, thanks for the quick response. Unfortunately, I work for a company that is highly regulated by the state as we provide Medicare & Medicaid. Therefore installing Office products on a server is not allowed. It took the sign-off of the CIO just to get Visual Studio installed. It is necessary (dictated by vendors) to be able to manipulate Excel files like renaming Sheets and in the example above, saving the Workbook in a text format.
Any ideas?
Scott
December 3, 2019 at 8:25 pm
so looking at the code, it is using Excel Interop to save to a tab delimited file;
can you eliminate the middleman and just read the excel document with SSIS, and use the dataflow to export out to a flat file?
why the extra work? does the sheet change with each iteration or something, so the columns change?
OpenXML 2.5 is a .NET library you can use to do similar work. I use it to make highly customized excel documents.
Lowell
December 3, 2019 at 8:39 pm
It is necessary (dictated by vendors) to be able to manipulate Excel files like renaming Sheets and in the example above, saving the Workbook in a text format.
Any ideas?
Scott
You can't do this stuff in SSIS unless you install Excel in the same place as where SSIS is running.
Edit: just saw Lowell's reply. I have not used OpenXML, but it sounds promising.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 3, 2019 at 8:48 pm
if that regulated then installing Visual Studio on the server is also not allowed - again licensing issues.
using OpenXML (2.9 better than 2.5 but harder to install) is one way.
http://www.microsoft.com/en-ie/download/details.aspx?id=30425
https://www.nuget.org/packages/DocumentFormat.OpenXml/
For other things just using Microsoft Access Runtime (which can be installed on servers and is supported) is another option to read/write Excel files but it won't allow you to rename tabs - for that OpenXML is the way to go.
In either case you will not be able to run macros - just in case whoever is doing the development also though of being smart and using macros.
I am aware of one company(Willis Towers Watson) which has software where they wish to use Excel on server. They have been advised before that they needed to move to OpenXML precisely because of this issue - Other companies with similar software also need to do the same.
December 3, 2019 at 11:33 pm
I have used the MS Access database engine to process Excel files in SSIS. It's a library, so no user interface, if that makes a difference, regulation-wise.
My wife has a saying, "Like's to short to ____________". Pretty much anything you don't want to do can fill in the blank. That said, life really is too short to work with Excel files in SSIS. I only use the library to convert them to .csv in a script task before the data flow. I have not used OpenXML, but if/when I have to do a new package with Excel input, I will certainly look into it.
The Access database engine is available here.
https://www.microsoft.com/en-us/download/details.aspx?id=54920
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply