21 For loop Container in SSIS

[@mmusciano]

You are an amazing teacher thank you so much for all the hard work you put in into your work.

[@learnssis]

Thank you so much for your kind words.

[@kiranraju3225]

Really Worthfull Tutorial. Thanks for the Video.

[@andrewwolfenberger4888]

Amazingly helpful! Well thought out.. Thank you so much Aqil!

[@learnssis]

Thank you so much Andrew for appreciating.

[@tariqawan5279]

beautiful. Excellent work and best presentation.

[@learnssis]

You are most welcome Sir.

[@kapildev2527]

You created a variable named "FilePath" but you didn't used it anywhere.Why?

[@learnssis]

ha ha, good point. You caught it. I just watched the video again and yes you are right FilePath was declared but never used. Thanks for pointing it out.

[@kiranvepa549]

hi simple and easy explanation! Thank you! I tried loading an xml file with about 10 rows but I only see 1 row loading into the SQL db. Can you please help explain why this happens? I did use a data conversion transform and included unicode/str conversion.

[@learnssis]

It will be very hard to tell without looking at the actual code. Sorry for it.

[@krishnachaitanyareddy2781]

such a nice example you gave to for loop container. Thanks for sharing.

[@learnssis]

Its nice to know that you liked the video Krishna.

[@gauravyadavalumni9068]

Excellent demonstration

[@learnssis]

Thanks for your comment, its good to know that you liked the video.

[@elmiraxudiyeva6274]

Hello teacher, thank you very much for the lesson. I have a question. Is it possible to export to a different SQL tables instead of exporting to a different csv files?

[@learnssis]

Thank you. Yes you can export to a different sql tables. You can use the OLE DB destination to insert into a sql table.

[@elmiraxudiyeva6274]

@@learnssis I dont have table, I want a new table to be created in each loop and data to be inserted there

[@learnssis]

@@elmiraxudiyeva6274 You can use sql queries to create a new table. And you can put the queries inside the execute sql task.

[@NavSalsa]

Excellent tutorial! thank you!

[@learnssis]

Thank you Naveed Anwar.

[@ManishVaity-ns1sj]

11:08 Hi, can you please provide what "Expression" you used there? Please comment.

[@learnssis]

Here you go, you can also download the expressions from the link in the description of video. ""+ @[User::FolderPath] +"\\Customer"+(DT_WSTR,12 ) @[User::LoopCounter] +".CSV"

[@ManishVaity-ns1sj]

@@learnssis thanks a lot!

[@shahidullahallmahmod5497]

Just amazingly awesome easy tutorial.

[@learnssis]

Thanks for your comment. Glad you liked the video.

[@mohanpinagadi7870]

Hi it's very good explanation . In this video you have explained looping multiple files from customer 1 to 10 . I want to see you explain all the 10 files to be placed in 1 excel file with different sheets. Customer 1 sheet,2 sheet etc..waiting for that video

[@learnssis]

Hi Mohan, good to know that you liked this video. I have already created a Video to loop through all files in a folder and then loop through all excel sheet in a file and load the data from different sheet, each sheet will contain different columns. kzbin.info/www/bejne/j4jUgGyBe7mEedE

[@amanahmed6057]

Hi Aqil 10:30 There is no connection string in 2019 visual studio, Suggest me a solution.

[@learnssis]

You would need to make a flat file connection manager and then right click on that connection manager, go to properties, then go to expressions, and click on 3 dots next to expression.

[@amanahmed6057]

@@learnssis 🙌🙌🙌🙌🙌🙌🙌👌👌👌👌

[@marshalljackson1385]

Nice video. Very helpful. Thank you.

[@learnssis]

Good to know that you liked it.

[@harinath2483]

Hi, thanks for the video and its very good example. In this control flow how can we make the for loop to take the minimum territory id also from the database similar to max(territoryid)?

[@learnssis]

Hi thanks for your comment. To take the min territoryid from database write the query like below SELECT MAX(TERRITORYID) MAXT, MIN(TERRITORYID) MINT FROM [Sales].[Customer] ibb.co/h8Njfk It should return max and min territoryid, then put this select query into the Execute SQL Task - Get MaxTerritoryId Declare a new SSIS variable MinTerritoryId of type Int32. Navigate to Execute SQL Task, right click on it, go to result set. Click Add button to Add a new row and provide Result Name as 1 and choose the MinTerritoryId under Variable Name. This way SSIS Variable MinTerritoryId will contain the value of MinTerritoryId from Database. ibb.co/msHvmQ Then if you want to start the loop from MinTerritoryId instead of value 1 then you can do that, by replacing the Loop Starting with MinTerritoryId instead of 1. ibb.co/dUEamQ I have tested the package after making above changes and changes seems fine to me, I debugged the package and it is running fine ibb.co/mx4x0k

[@khbeyonce]

@@learnssis Hello Aqil, is there a way to define max. and min. for a name(text). For example, I have a sales data for cities Bangalore, Delhi, Hyderabad and Mumbai. How to extract data for each city to separate csv files similar to numerical territory id's? Thanks in advance.

[@learnssis]

@@khbeyonce You will need to use the conditional split for such scenario and in the Conditional split you can define a conditional for each City and then use that defined output to get the data for a particular city.

[@Liveyourlifethismoment]

Well explained.Thank you for the video

[@learnssis]

Thank you Pravallika.

[@Liveyourlifethismoment]

I should Thank you ,as you 've explained it very well and made me understand clearly

[@learnssis]

Very glad to know that you understood it.

[@mdsamiulislam2522]

If i want ot save the file as a territory name like territory1.csv, territory2.csv..and so on...then how will I manage it?

[@learnssis]

Hi, then in the Flat File Connection Manager, under connection string you would need to put the FileName whatever you would like.

[@LakshmiSivaKarthikPadala-y9h]

What is the use of FilePath variable ?

[@learnssis]

FilePath ssis variable is actually not required here. I initially created it but then forgot to use it.

[@shwetadalal1549]

The same can also be done using ADO enumerator of Foreach loop container. Right?

[@learnssis]

Correct. I should have given the better example of For loop.

[@nivethamurugavel4972]

Hi Aqil...If i didnt set dynamic names i will get TerritoryId=10 values right? not all the values will be placed inside my .csv file?

[@learnssis]

Correct.

[@nivethamurugavel4972]

@@learnssis Thanks for your answer !!!I have one more question sorry for disturbing you im new to SSIS ...my source is a flat file having Material ID 's like 0000001234KNA1 0000002345KNC 0000006547KNV In the destination i have tables like tbKNA1 tbKNC tbKNV which transformation i should choose to print my material id's correctly to the respective tables? will Script task work this case?

[@learnssis]

@@nivethamurugavel4972 no problem. For this case you can use conditional split transformation and in the conditional split transformation, you can extract right 4 characters from the material id and then it will be written to the respective destination table accordingly.

[@nivethamurugavel4972]

@@learnssis Thank you Aqil for your answer

[@TheNoOne916]

Very nice work mr.ahmed....helping a lot...thank you..... can you please do some complex tasks that are faced in reatime

[@learnssis]

Its good to know THEJESH that you liked it. Sure, in coming days I will post more real time scenarios in SSIS.

[@lazygamer8760]

Thank you so much sir

[@learnssis]

You are most welcome Sir.

[@sriniwaaskorampalli8145]

hi sir...why the text qualifier has been mentioned here? whats the diff?

[@learnssis]

Text qualifier is used to surround the column data by some specific character. For example when we export data from a sql server table to a CSV file and if data in csv file is separated by comma like below id,name,address 1,srini,c10 new delhi 2,aqil,a20, noida Thus if you look at the address of second record we have comma in the address, thus now it won't be exported correctly to csv file as SSIS will think that address is completed till first comma in the address, thus we use text qualifier and we surround the column by a text qualifier like double quotes, using the text qualifier the data will look like below "id","name","address" "1","srini","c10 new delhi" "2","aqil","a20, noida" Now SSIS will treat all data from beginning of a double quote till end of the double quote.

[@sriniwaaskorampalli8145]

@@learnssis thank you sir...blessed to have visited your channel to learn ssis.

[@learnssis]

@@sriniwaaskorampalli8145 you are most welcome Srini.

[@khbeyonce]

Good day Aqil, I suggest you to provide the sample files you use in your tutorial videos for us to follow along and practice. It becomes difficult to get such sample files for beginners.

[@learnssis]

Sure, will try to get those files and will provide them may be on google drive. Thanks.

[@learnssis]

Hi, I have created below link and will share it in the description of each video to download the files used in a video. drive.google.com/drive/folders/1PZF0bC7UitqEGEoUDx8QAW0f5WIsIZ7h?usp=sharing

[@sriniwaaskorampalli8145]

Sir what is the use of "Connection String" can you define this....

[@learnssis]

Connection string is a property in flat file connection manager which is used to connect to a particular file. You can think of connection string as full file path.

[@Shankar-zi3yk]

Can we do the same task using for each loop container

[@learnssis]

Yes we can do that.

[@pvksmaheeth8816]

My destination giving cannot convert unicode and non unicode string data types how to deal

[@learnssis]

Use data conversion transformation to convert unicode to non-unicode or vice versa kzbin.info/www/bejne/l4KtdJ6vZ9aZedU

[@vinnu236]

Hello sir, I have one doubt why loop counter is changed from int to string? Can u please clarify me in this sir.

[@learnssis]

Because we are trying to make a file name using the Counter variable and counter variable is of type Int thus even in sql server you can not concatenate a string value with a int value you need to convert int to string only then you can concatenate the 2 values together, same thing we are doing in ssis expression that we are converting int value to string only then we will be able to concatenate "Customer" string with Counter variable.

[@vinnu236]

@@learnssis yeah now I got it sir... Didn't realise that one.. Thanks for explanation sir 👍

[@darbardarbar9460]

what is differences between ssis tester and ssis developer please explain me

[@learnssis]

May be I can make a detailed video on this one, but in short, ssis developer is responsible for creating the SSIS packages, while ssis tester is responsible for making sure that the SSIS packages are doing exactly the same thing for what they were create for.

[@darbardarbar9460]

Thank you sir Eid Mubarak you and your family members

[@learnssis]

@@darbardarbar9460 Thank you Parveen.

[@kiransohal3474]

helped alot thank u

[@priyanshidangi6968]

only getting one file Customer1.csv? how can i fix it

[@learnssis]

In the flat file connection manager, you need to set the connection string through expression as shown in the video. Setting the connection string of flat file connection manager will allow the ssis package to create a new csv file for each value got from sql server table.

[@priyanshidangi6968]

@@learnssis setting everything still not getting only one file customer1.CSV

[@learnssis]

@@priyanshidangi6968 Can you share the screenshot of connection string property from expression of flat file connection manager to [e-mail] ?

[@madhubabug5945]

Hi Aqil, I have an error at 'OLEDBSource' Parameter configuration "Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable." Plz help to overcome it

[@learnssis]

From OLEDB Source, use SQL Command from Variable. If you want to know how to use it, then take a look at this video kzbin.info/www/bejne/iJKcm62vbZWhp7M

[@paradesinarasimha5271]

Thank you so much

[@learnssis]

You are most welcome.

[@mahmoudhassanine9905]

Great vedio but please I cannot find this connection string on the dropdown list

[@learnssis]

Can you tell me at which location you are unable to find the connection string ?

[@saibabac7431]

Hii sir simple and very good explanation . And i have a doubt that why we need to create variables here , Can u explain briefly.

[@learnssis]

For loop will run an iteration, so there should be an SSIS variable which can hold the current value of iteration. For example we are running for loop from 1 to 10. So there should be a way to know at which iteration the process is running at a particular time. So to hold the iteration value we need to have an SSIS variable. Thanks.

[@sumanthbalaji5157]

nice tutorial

[@learnssis]

Thank you Sumanth.

[@lakshayarora3935]

Hi Thanks for the video. Could you also provide the sql script from where I can create the table(which you created in the video in SSIS) for practice. Thanks!! :):)

[@learnssis]

Hi Lakshay, in the description of this video, I have provided the link to google drive from there you can download the create table script for this video. Thanks.

[@armaankadiwala6049]

Hi sir, when I am using Excel destination instead of CSV I am facing an issue. Can you send me the steps that would be easy for me.

[@learnssis]

Hi, Can you take a look at below video kzbin.info/www/bejne/bmm7eWxsnLh0Zrc

[@mahamuthashameem3250]

@@learnssis hi its showing video is private. not able to see. Could you please help on this.

[@learnssis]

@@mahamuthashameem3250 I have updated the link of the video kzbin.info/www/bejne/bmm7eWxsnLh0Zrc

[@krishnachaitanyareddy2781]

Text qualifier " what that means can you explain

[@learnssis]

Hi Krishna, in CSV files, most of the times, each column will be separated by a comma delimiter or sometimes it will be separated by a Pipe delimiter. Now Suppose you are storing Addresses in a CSV files and there are chances that Address can have comma in the Address itself, thus it will be hard to know if comma is in Address or it is a delimiter so separate 2 columns. Like below, Address has comma in it Id, Name, Address,Phone 1,Krishna,C-12,Chandni Chowk,Delhi,8377125452 To handle this, we enclose each column most of the time with double quote ", this " is called as text qualifier. Above data with text qualifier will look like below "Id","Name","Address","Phone" "1","Krishna","C-12,Chandni Chowk,Delhi","8377125452" Thus everything from starting a " and until ending the " is a column value.

[@MVinodKumar-tt1tu]

Hello Sir, In Flat File Destination I gave File name as "Customer_data1.txt" and in the expression ""+ @[User::FolderPath] +"\\Customer_Data"+ (DT_WSTR,12)@[User::LoopCounter] +".txt" I used like this. After Execution of Package I got 10 text files other than the file which i used in the Flat File Destination. And the file used in Flat FIle Destination doesnt have data. Other 10 Files which got generated has Data.

[@learnssis]

This is the expected behavior of SSIS package. Because you set the ConnectionManager property of flat file connection dynamically to assigned from Expression thus those 10 files got generated accordingly.

[@MVinodKumar-tt1tu]

@@learnssis Yes Sir got it. But in the video there was only 10 files. So, I got 11 totally sir

[@learnssis]

@@MVinodKumar-tt1tuDelete that extra file, it was because you created it during design time it won't be created in future.

[@MVinodKumar-tt1tu]

@@learnssisYeah Got it Sir. It is just to give a CSV/Text file and its path for reference to create the files in that location.

[@learnssis]

@@MVinodKumar-tt1tu Correct.

[@suruchigohel5555]

ye visual studio code he na ?

[@learnssis]

Yes.

[@dilipkumar-ck1nc]

Expression builder not properly zoomed and the code is not in notepad

[@learnssis]

Okay, I will put the code inside notepad today.

[@learnssis]

I have uploaded the expression file.

[@dilipkumar-ck1nc]

@@learnssis can u provide link

[@learnssis]

@@dilipkumar-ck1nc The link is in the description of each video. You will find a folder for each video and you can download the files from there.

[@dilipkumar-ck1nc]

@@learnssis I have downloaded google drive folder in zip format I also unzip it there is no code updated

[@genoses5174]

Hey great video But can I get txt file to add these data into the database , it will help me a lot Thanks

[@learnssis]

First you can create the package as shown in the video, it will generate the files from adventureworks database and then you can create another package to import those files back to the database. Right now I don't have those files.

[@Teja0709]

Thats very great explanation!!Have you done any video where it fetches data from multiple tables and output to a single text file. I mean like for example my database have 3 tables and each table have a column common called rownum and all those 3 tables are joined using this rownum. So i need to get content in output file like this Rownum1 firsttabledata Rownum1 secondtabledata Rownum1 thirdtabledata And the go to next row num Rownum2 firsttabledata Rownum2 secondtabledata Rownum2 thirdtabledata So basically it has to loop through each row in 3 tables with the rownum1 and oitput to the single file and then again loop through each row in all 3 tables with rownum2. How to achieve this??? It would be great if you could give any suggestion that would help in implementing the above wat Rownum2

[@learnssis]

You can use Union to combine the data from multiple tables into a single result set and then put this in a cte and do an order by RowNumber. You can use this query inside OLE DB Source which will select data from all 3 tables and then you can use flat file destination to export the data to a flat file. create table t1(Id int, val varchar(50)) create table t2(Id int, val varchar(50)) create table t3(Id int, val varchar(50)) insert into t1 values (1,'t1 data 1'),(2,'t1 data 2') insert into t2 values (1,'t2 data 1'),(2,'t2 data 2') insert into t3 values (1,'t3 data 1'),(2,'t3 data 2') with cte as ( select * from t1 union select * from t2 union select * from t3 ) select * from cte order by id

[@Teja0709]

Thank you so much it worked!!@@learnssis

[@learnssis]

@@Teja0709 Glad it worked for you.

[@Teja0709]

@@learnssis can i ask you one more question I have a dataflow task and many execute sql tasks. So my data flow task that contains oledbsource, row count transformation and a flat file destination. I need to generate the flat file only when i when the variable in row count transformation returns rows greater than 0. If it returns 0, I should not generate a flat file. Where should i set this condition and how to do and many thanks for your help

[@learnssis]

@@Teja0709 If the row count transformation is inside the data flow task then the flat file will always be generated either the rowcount is 0 or more. One of the method to achieve this will be, use an execute sql task before data flow task and in the execute sql task write the select query that will return the count of number of records to export and assign it an SSIS variable. Now use a precedence constraints between execute sql task and data flow task and write a condition in expression and constraints if ssisvariable>0 this way it will only execute the data flow task if the count of ssisvariable will be greater than zero.

[@parulgupta2535]

As a beginner I am not able to understand why we are doing any particular step

[@learnssis]

You should try to watch the videos in a sequence starting from video no 1 in increasing order. You should be able to understand the concepts SSIS Tutorials: kzbin.info/aero/PL_YF--8vjjEVEXMf2hEFn0D5tEJV9kRqi

[@sravankumar1767]

your explanation good but y should we use that options not explain briefly

[@learnssis]

Hey, thanks for your comment, which option you are talking about ?

[@pritampatil91]

your tutorials are helpful but not so explanatory. please try to elaborate more about things man.

[@learnssis]

Thanks for your comment. Sure, will take care.

[@flogzer0]

I wonder if your kid still screams at you

[@learnssis]

ha ha. Yeah there are a lot noises in my old videos 😀