2

I'm facing a strange problem with BIML. I'm creating SSIS packages using BIML and I have few script tasks in the package. They are very simple script tasks, one example below.

string filePath = Dts.Variables["User::SrcFile"].Value.ToString();
string folderName = Path.GetDirectoryName(filePath);
Dts.Variables["User::Error_File"].Value = folderName + "\\Errors\\" + "LoadErr.csv";

BIML generates the package fine, however the script task only works if I open the Script task in designer, click on Edit Script and then Close without making any changes. If I run the package straight away it throws the following error.

Unable to cast COM object of type ‘System.__ComObject’ to interface type ‘Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariables100’

Opening and closing the Script task in the Editor fixing something which I can't understand. I have around 50 packages and each package has 4 script tasks, so I can't open each script task in each package to make it work.

I'm using Visual Studio Prof 2015 with BIMLExpress version 5.0.61915.0 and .NET framework 4.7. This problem has eaten up most of my day without any possible solution.

And another interesting thing is I have installed SSDT 2015(VS 2015 Shell) on a temporary PC and it works fine there, Unfortunately I can't use the 2nd PC for my development. I have compared both the packages the only difference I found was the value for the BinaryItem element.

To test this further I have kept the script task to bare minimum the problem is caused by the use of Dts.Variables. I don't understand what causes this strange behaviour. Any help is greatly appreciated. Thanks in advance. S

MSBI-Geek
  • 628
  • 10
  • 25
  • 1
    Yeah, there's a bit of chatter over on the Varigence forums where people are running into similar challenges with Script tasks/components. The fact that you get different results on a second machine is truly bizarre. Perhaps your box isn't as patched up/down (OS, SSDT, Visual Studio, BimlExpress, and then potentially third party providers) as the temporary machine. – billinkc Nov 30 '17 at 22:25
  • Thanks Bill, helpful to know that I'm not alone facing this issue. I'll now look have look for workarounds. – MSBI-Geek Dec 01 '17 at 07:28
  • Looks similar to the problems I have been having that are yet to have a solution: https://stackoverflow.com/questions/45239315/ssis-script-component-cannot-load-assembly-until-manually-opened – iamdave May 16 '18 at 14:50

0 Answers0