SQL Server Integration Services (SSIS) Part 12 - Other Foreach Loops

[@yasarkocyigit1314]

create table mentornumbers ( firstname varchar(30), lastname varchar(30), proteges int ) insert into mentornumbers values ('Cheryl','Cole',10), ('Dani','Minogue',13), ('Gary','Barlow',6), ('Kelly','Rowland',4), ('Louis','Walsh',30), ('Nicole','Scherzinger',3), ('Sharon','Osbourne',13), ('Simon','Cowell',24), ('Tulisa','Contostavlos',5)

[@uncle_eddies_adventures]

This man has single-handedly convinced me that I belong in the UK. Thank you sir.

[@asingb]

This is published 6 years ago 🤔. Just came across this tutorial. It's really awesome, explained very well. Thanks.

[@blrm4681]

Thank you , your videos are excellent!!!! In my last job they did not use ssis (or have it installed - have no idea why) and now that I am looking for a new job,many of the jobs do mention it . Your tutorial with me trying to do the same myself should give me some basic starting knowledge.. Will make a donation (if I remember) once I land a job. Thank you.

[@mohsenhs]

Awesome video, nice and clear explanation. Thanks a lot

[@MrIrrepressible]

This is great, thanks for this I'm sure it will come in handy for me at some point in time, I'm think of something like outputting the rows that have been affected by an update.

[@franciso4650]

Can you please provide the sample data files for these tutorials, it will be greatly appreciated. Excellent job so far.

[@parrisgjerde9212]

And of course, instead of looping through the records in the table we could have just run a select statement to get the name and max protégés from the database... but where’s the fun in that😀 Nice video.

[@WiseOwlTutorials]

Indeed! Thanks for the comment and for watching!

[@judedcosta5178]

Thanks Andy for these tutorials. Really good quality. You should get your content over to plural sight if possible!

[@barnaberodrigues3341]

Barnabe

[@MiaMia-tb9cl]

Thank you for All God Bless You

[@-Harambe]

Andy, thank you so much for these great tutorial videos. Andy, towards 11:46 minutes of this video, you are converting data type to string before you populate the local variable "Highest Name". Why is this necessary? Data type you assigned to "Highest Name" is already data type from the time of declaration. Also, can you run *.dtsx file from SQL Server job(task) for automation purpose?

[@-Harambe]

***** Thank you so much for your help Andy! I really appreciate it.

[@hemadriboddu6388]

super ,thank you so much

[@ryanblythe7735]

Is there any advantage of having used a Script task to determine the largest value? Couldn't a ternary operator (?:) have been used in an expression task instead?

[@akncaliskan8162]

There is no mistake like error message what could be problem?

[@akncaliskan8162]

"Error: The type of the value (Int32) being assigned to variable "User::ServerName" differs from the current variable type (String). Variables may not change type during execution. Variable types are strict, except for variables of type Object."

[@yasarkocyigit1314]

part 2 script task public void Main() { // read in value ow two variables passed in string highestname = Convert.ToString(Dts.Variables["highestname"].Value); int highestnumber = Convert.ToInt32(Dts.Variables["highestnumber"].Value); //display name of busiest mentor MessageBox.Show("Busiest mentor is " + highestname + "with" + highestnumber.ToString() + "proteges"); Dts.TaskResult = (int)ScriptResults.Success; }

[@yasarkocyigit1314]

public void Main() { // TODO: Add your code here User::firstname,User::lastname,User::proteges User::highestname,User::highestnumber string firstname = Convert.ToString(Dts.Variables["firstname"].Value); string lastname = Convert.ToString(Dts.Variables["lastname"].Value); int proteges = Convert.ToInt32(Dts.Variables["proteges"].Value); int highestnumber = Convert.ToInt32(Dts.Variables["highestnumber"].Value); if (proteges > highestnumber) { //set new highest number Dts.Variables["highestnumber"].Value = proteges; Dts.Variables["highestname"].Value = firstname + " " + lastname; }

[@Anatoli8888]

string FirstName = Convert.ToString(Dts.Variables["FirstName"].Value); string LastName = Convert.ToString(Dts.Variables["LastName"].Value); int Proteges = Convert.ToInt32(Dts.Variables["Proteges"].Value); int HighestNumber = Convert.ToInt32(Dts.Variables["HighestNumber"].Value); string HighestName = FirstName + " " + LastName; if (Proteges > HighestNumber) { //set new highest number Dts.Variables["HighestNumber"].Value = Proteges; Dts.Variables["HighestName"].Value = FirstName + " " + LastName; HighestNumber = Convert.ToInt32(Dts.Variables["HighestNumber"].Value); HighestName = FirstName + " " + LastName;

[@Anatoli8888]

string HighestName = Convert.ToString(Dts.Variables["HighestName"].Value); int HighestNumber = Convert.ToInt32(Dts.Variables["HighestNumber"].Value); MessageBox.Show("Busiest mentor is " + HighestName + " with " + HighestNumber.ToString() + " proteges");

[@kohavsheifa1212]

Большое человеческое спасибо!

[@kohavsheifa1212]

string tName = Convert.ToString(Dts.Variables["User::TableName"].Value); System.IO.StreamWriter sw = new System.IO.StreamWriter( "C:\\ajb files\\tables.txt", true); sw.WriteLine(tName); sw.Close(); ...для полноты картины

[@dharmenderdevrath5961]

public void Main() { string tName = Convert.ToString(Dts.Variables["TableName"].Value); System.IO.StreamWriter sw = new System.IO.StreamWriter("C:\\Users\\dharmender.devrath\\Downloads\\Training\\SSIS Training\\Tutorial SSIS owl" + "\\tables.txt", true); sw.WriteLine(tName); sw.Close(); Dts.TaskResult = (int)ScriptResults.Success; }