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