61

I am creating a SSIS project via Biml (Using the current version of BimlExpress per the Varigence BimlExpress page) that uses a script component within a Data Flow Task. The project is created without issue however errors when it gets to the Script Component:

Could not load file or assembly 'Microsoft.SqlServer.DTSPipelineWrap, Version=14.100.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.

at ScriptMain.PreExecute() at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PreExecute()

I can see this dll within the GAC and when I try to add the reference manually, SSIS states that I can't add it as it is already included in the project.

However, if I open the Script Component and manually Build it - but change nothing else - the Data Flow Task will run through without issue.

To clarify, I know I have changed nothing else as I am recreating this project from the Biml each time I want to test a new approach, which provides a consistent baseline.

Does anyone know why a Script Component that includes a reference to a GAC dll, will only work when manually Built but not beforehand?

I have used this exact Biml before without issue, though on a different environment. Worked using Visual Studio 2012 against a SQL Server 2016 instance yet doesn't here within Visual Studio 2015 (targeting 2012) against a SQL Server 2016 instance.

For further context I have also tested this outputting the SSIS Project targeted at SQL Server 2012, 2014 and 2016 with the exact same error message.

Update

This appears to definitely be a dll version problem in the project as output from BimlExpress, in that if I manually create the v4.0_14.100.0.0__89845dcd8080cc91 folder with the version 13 dll inside at C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.DTSPipelineWrap the script component now errors out with the following:

System.InvalidCastException: Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData100'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{ID removed just in case}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PreExecute()

at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper100 wrapper)

iamdave
  • 12,023
  • 3
  • 24
  • 53
  • 1
    Are you using the recently released BimlExpress 2017 version or 2016 Biml components? – Tom Willwerth Jul 21 '17 at 14:10
  • Seems like it is trying to load a library of version 14, which according to this link, is related to SQL Server 2017. https://social.technet.microsoft.com/Forums/en-US/6e029604-d754-4916-be7f-ae1a3594a00f/microsoftsqlserverbatchparserclient-version1410000?forum=sqlgetstarted – DVT Jul 21 '17 at 14:48
  • @TomWillwerth The VSIX version of BimlExpress that is available on the [Varigence BimlExpress page](https://www.varigence.com/BimlExpress) – iamdave Jul 21 '17 at 14:52
  • @DVT That appears to be the problem, though I am not sure how to fix. Is this something Varigence need to fix within BimlExpress or something I need to do? – iamdave Jul 21 '17 at 15:08
  • @iamdave I'm a learner here. I am waiting to see the answer too. – DVT Jul 21 '17 at 15:21
  • It's odd that it doesn't work for you when targeting 2016, that's usually the fix. The problem has to do with Visual Studio Tools for Applications (VSTA) gets updated with every new version of SSIS. I have VS 2015, SSDT 14.0.61705.170, with BimlStudio 2017. My script task packages run as expected when I target SQL 2016. – Shannon Lowder Aug 08 '17 at 13:02
  • I have no answers but I've wrestled with similar problems. They can be VERY hard to diagnose and repair. Get to know the Fusion Log and, remember it can be an assemblies (DLL) dependencies that are at issue, e.g. missing C++ runtimes have often caused me grief. Fuslogvw.exe (Assembly Binding Log Viewer): https://www.hanselman.com/blog/back-to-basics-using-fusion-log-viewer-to-debug-obscure-loader-errors https://learn.microsoft.com/en-us/dotnet/framework/tools/fuslogvw-exe-assembly-binding-log-viewer – ScottWelker Apr 14 '21 at 17:14

2 Answers2

1

I guess your problem may be related to the Express version.

See at the related page the differences between Express and BimlStudio versions (Feature Comparison Chart under "Compare with BimlStudio" button):

https://www.varigence.com/BimlExpress

David BS
  • 1,822
  • 1
  • 19
  • 35
  • Which part of that chart are you referring to? – iamdave Jul 28 '17 at 08:12
  • @iamdave, see topic "Automation": "MS Build Support" and "API Access from external applications". Also, in topic "Extensibility": "Redistribute BimlEngine Binaries" . I suppose your problem may be related to these. – David BS Jul 28 '17 at 13:40
  • 1
    Those relate to the automating and scripting of the Biml Engine itself, not the exporting of SSIS components. As in, setting up a script that automatically builds a Biml solution, exports the SSIS project, then does something with it. – iamdave Jul 28 '17 at 15:30
  • 1
    Are you sure that the exported SSIS scripts from BIML won't access/utilize really the pointed DLL with problem? It could explain the "No Interface" problem found in your problem, I mean, an unregistered service/DLL to SQL. – David BS Jul 28 '17 at 15:57
  • 1
    Yes, because when exported using SSDT 2013 the correct dll is referenced and the solution works. The script component exported by VS 2015 is trying to reference a version of the dll higher than is installed on my machine. – iamdave Jul 29 '17 at 17:46
  • 1
    Hmmm.... VS2013 utilizes .NET 4.5 while VS2015 references .NET 4.6 - which you can change in the project settings but (sometimes) I saw conflicts. I have a project here that I had to change to 4.6 without any reasonable cause. Try to install .NET 4.6 (and subversions) and .NET 4.7. – David BS Jul 31 '17 at 14:15
  • 1
    That's the problem. Installing the newer .net versions is not a viable option on the target machines. It looks like this is a problem Varigence need to sort out themselves. – iamdave Aug 01 '17 at 10:44
  • 1
    @iamdave, it's ok to me your comments. But I remind you that installing .NET onto a server won't affect any previous versions or systems. Anyway, I understand your point. Good luck. – David BS Aug 01 '17 at 12:49
  • 4
    @davidbs "API Access from external applications" refers to the fact that with express you cannot reference BimlC and emit packages via programs you build or command line. It does not refer to referencing assemblies via script tasks. – Shannon Lowder Aug 08 '17 at 12:39
0

I had the same issue with the Data Profiling component. I used the following post.

ssis-data-profiling-task-editor-return-error-codes

Mostafa NZ
  • 382
  • 1
  • 6