21 For loop Container in SSIS 

Thank you so much for your kind words.

Thank you so much Andrew for appreciating.

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. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-YWqO9KFTQCo.html

You are most welcome Sir.

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.

Its nice to know that you liked the video Krishna.

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

Thanks for your comment. Glad you liked the video.

Thank you Naveed Anwar.

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

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

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

Good to know that you liked it.

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

Thank you Pravallika.

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

Very glad to know that you understood it.

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

From OLEDB Source, use SQL Command from Variable. If you want to know how to use it, then take a look at this video ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Ra9jzy80nqQ.html

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

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

@@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.

@@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.

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

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.

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

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"

@@learnssis thanks a lot!

Use data conversion transformation to convert unicode to non-unicode or vice versa ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-aQJCky2qfCs.html

You are most welcome Sir.

Correct.

@@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?

@@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.

@@learnssis Thank you Aqil for your answer

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.

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.

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

You are most welcome.

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

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

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

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.

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

@@sriniwaaskorampalli8145 you are most welcome Srini.

Yes we can do that.

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.

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.

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

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

@@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.

@@MVinodKumar-tt1tu Correct.

Thank you Sumanth.

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.

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

@@priyanshidangi6968 Can you share the screenshot of connection string property from expression of flat file connection manager to aqil33@gmail.com ?

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.

Thank you sir Eid Mubarak you and your family members

@@darbardarbar9460 Thank you Parveen.

Hi, Can you take a look at below video ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-88XH96gSA0U.html

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

@@mahamuthashameem3250 I have updated the link of the video ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-88XH96gSA0U.html

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.

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.

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.

Okay, I will put the code inside notepad today.

I have uploaded the expression file.

@@learnssis can u provide link

@@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.

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

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: ru-vid.com/group/PL_YF--8vjjEVEXMf2hEFn0D5tEJV9kRqi

Yes.

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

Thank you so much it worked!!@@learnssis

@@Teja0709 Glad it worked for you.

@@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

@@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.

Thanks for your comment. Sure, will take care.

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

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