
In these times of data warehouse automation and fast track SSIS, BIML has become a huge contributor to my daily work.
On one of my most recent projects we had a custom script task in SSIS that we needed to implement in the same package several times.
The code of the custom script is in its own biml-file in the project and referenced from the main biml-file.
We kept getting a mysterious error when executing the package in debug mode from Visual Studio. (No warning upon building the packages).
After alot of debugging and hair pulling hours, we finally got our arms around the bug.
The error was that the ProjectCoreName of the new 3 script tasks in the same package had the same name.
My learning from this, and yes, I’m not a .NET developer, is that upon executing the custom script task, the SSIS engine must compile the code and store it for usage upon handling the data. This storage is shared with the whole package, and therefore cannot contain the same .NET project name. [Written from my personal understading – correct me if I’m wrong].
So for any future BIML users who wants to add the same custom script task to the BIML project – remember to add a variable to the ProjectCoreName of your biml-script.
This can be done fairly easy, as your biml-scripts can relate to the variables calling the biml-file. Just add the standard
<#=variablename#>
to he ProjectCoreName-tag of your biml-file containing the custom script.
Happy BIML-ing…
I have run into the same issue. Can you elaborate on how to add the variable?
I added the variable to the ScriptProject biml Project Name and declared it in the first tier environment file.
However it wont compile, keeps saying variable does not exist in current context
If you like I can review the code for you? You can send it here in a comment (just the first couple of lines) and I’ll gladly help you
Great, thanks.
It sounds like what you figured out, is it possible to see a sample of your biml code?
I have a file called ScriptProjects.biml that contains all my ScriptComponents and ScriptTasks.
I then have a package that needs to call the same scriptcomponent many times in a dataflow.
When there is only one call to the scriptcomponent its fine, if I add another call in the dataflow, it cannot generate the dtsx file.
You mentioned adding a variable to the ScriptComponent name, where is this variable declared and how is it incremented?
Would it need to be done using CallBimlScript?
Regards
Jon
Brian? Any feedback?
Sorry – for my late reply here.I think the error is the ID for the scripttask cannot be the same if you have more than one of the same scripttask. The scripttask must have a unique id. I always use the packagename and some sort of given name in the script task id field.
I use this as an example – notice the ProjectCoreName is unique as it takes the variable columnName
I hope it makes sense
No worries, I’m stuck and just grateful somebody might have an idea.
You mention adding a variable to the ScriptComponent name, how do I do this?
I have a ScriptComponent.biml that defines the component and then its referenced in the packages.
Where do I declare the variable and how to I increment it?
Thanks
I can only see one line
Am I missing something?
HEHE – no – I also saw that. I’ve updated the code with an image instead 🙂
Thanks for that, thought I was losing my mind.
Would it be possible to see your declaration of the ScriptComponent in SCD2RangeLookup.
I’m guessing it also has the column variable.
I had all my scriptcomponents in a single file that was added as a separate tier at compilation.
I will try this approach.
Again thank you so much for the tip.
I can mail it to you. It’s a big file 🙂 – can I send it to you on your reported Gmail?
Yes please!
Done 🙂
That worked. I thought the ScriptComponent Name had to match up the srcipt definition name.
Thank you again for your help.
No worries – always a pleasure to help other people 🙂
Brian? Does that make sense? Any help would be appreciated