SQL Server Integration Services (SSIS) Part 13 - Script Tasks using C#

[@richardedwards7527]

Excellent clear and concise, covering lots of details and gotchas to look out for, thanks, I can see it was on Visual Studio & DTS from a good few years ago but is still relevant to my current version VS 2017 and SQL😃

[@WiseOwlTutorials]

Happy to hear that it's still useful Richard, thanks for watching and for taking the time to comment!

[@krismaly6300]

I watched this video second time. This has lot of information to in work in real time scenario.
This is another awesome video. I enjoyed watching this video and recommend others to watch. Narration and examples are very good. Thanks for educating the community and your volunteer-ship is appreciated. Please keep producing some more videos. Thanks a bunch

[@krismaly6300]

I liked ERROR handling shown in this video.
This is another awesome video. I enjoyed watching this video and recommend others to watch. Narration and examples are very good. Thanks for educating the community and your volunteer-ship is appreciated. Please keep producing some more videos. Thanks a bunch

[@krishnakarki2923]

Love your tutorials ! It's worth more than reading 100 pages of book.

[@pandarikkal]

I am extremely grateful to wise owl

[@dharmenderdevrath5961]

public void MakeChoice()
{
DialogResult answer=MessageBox.Show("Choose Excel?","Choice",
MessageBoxButtons.YesNo,
MessageBoxIcon.Question,
MessageBoxDefaultButton.Button2);
switch(answer)
{
case DialogResult.Yes:
Dts.TaskResult = (int)ScriptResults.Success;
break;
case DialogResult.No:
Dts.TaskResult = (int)ScriptResults.Failure;
break;
}
}

[@MethodOverRide]

Thanks for this!

[@bouchrab5434]

Very interesting and useful thank you

[@Anatoli8888]

public void Main()
{
// create an empty collection of variables
Variables WolVariables = null;
// access the first two variables as read-only
Dts.VariableDispenser.LockForRead("User::NumberMentors");
Dts.VariableDispenser.LockForRead("User::NumberFinalists");
// access the last as read-write
Dts.VariableDispenser.LockForWrite("User::NumberTotal");
// put the variables we've locked into our collection
Dts.VariableDispenser.GetVariables(ref WolVariables);
// find our the value of the two read-only variables ...
int NumberMentors = Convert.ToInt32(WolVariables["NumberMentors"].Value);
int NumberFinalists = Convert.ToInt32(WolVariables["NumberFinalists"].Value);
// ... and set the value of the read-write one
WolVariables[2].Value = NumberMentors + NumberFinalists;
// unlock all the variables we'd locked
WolVariables.Unlock();
Dts.TaskResult = (int)ScriptResults.Success;
}

[@kohavsheifa1212]

And first script:
public void MakeChoice()
{
//ask the question
DialogResult answer = MessageBox.Show("Choose excel?", "Choice",
MessageBoxButtons.YesNo,
MessageBoxIcon.Question,
MessageBoxDefaultButton.Button2);
//choose answer
switch (answer)
{
case DialogResult.Yes:
//user chose YES - use Excel
Dts.TaskResult = (int)ScriptResults.Success;
break;
case DialogResult.No:
//user chose NO - use SQL
Dts.TaskResult = (int)ScriptResults.Failure;
break;
}
}
#region ScriptResults declaration
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion

[@surena758]

Your Videos are very very useful. could you please upload videos on Slowly changing Dimensions examples.

[@maraduarz]

A video on how to use MERGE, MERGE JOIN would be very helpful as well.

[@andybrown7443]

I've written a blog on them, which you can see at www.wiseowl.co.uk/blog/s421/scd-transforms.htm . As this shows, I'm not a big fan.

[@blulinr77]

Somewhere to download your test data would be great as well. There's only so much you can learn by WATCHING, rather than DOING.

[@TheDouceHouse]

I wasn't going to respond until you started YELLING!!! If you have been watching these videos, then you will notice that they are very good and very FREE! If you go to their website, you will see that the course that covers SSIS costs roughly 795 British pounds to take or about $1200. It isn't hard to make 2 Excel spreadsheets. I believe he gives you screenshots for that very purpose, other than a good overview, at the beginning of the videos. You don't have to copy the entire long spreadsheet, just up to 7-15 to make it useful. Thanks Andy for these great tutorials.

[@andybrown7443]

TheDouceHouse
Thank you for your stalwart defence! In future videos I think I'll put links to file download sites, but you're absolutely right that a few rows would be sufficient.

[@amulyad7973]

Thanks for such nice videos :)
I was trying to generate the same package, but I am getting this run time error.
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()
Below is the code I have written within the script task.
public void Main()
{
// TODO: Add your code here
Variables wolvariables = null;
Dts.VariableDispenser.LockForRead("User::FinalistCount");
Dts.VariableDispenser.LockForRead("User::MentorCount");
Dts.VariableDispenser.LockForWrite("User::TotalCount");
Dts.VariableDispenser.GetVariables(ref wolvariables);
int FinalistCount = Convert.ToInt32(Dts.Variables["FinalistCount"].Value);
int MentorCount = Convert.ToInt32(Dts.Variables["MentorCount "].Value);
wolvariables[2].Value = FinalistCount + MentorCount;
wolvariables.Unlock();
Dts.TaskResult = (int)ScriptResults.Success;
}
Can you please help !!

[@lydialim1993]

I'm facing this error too. I wonder what could I have missed.