Thanks man. Assuming that the target is a database table, how to change this logic to read the files in parallel and load into the target in parallel instead of sequentially? The actual requirement is a SQL task prior to the Foreach Loop is feeding a list of databases against which same SQL has to be run in parallel (as the SQL runs for a long time) on all the databases that the Foreach Loop receives and the data extracted from each database should be loaded to the same target table in the target database.
You can create a kind of Configuration table and add a column like Thread. Now if you want to run 100 tasks, and you want to create 4 thread for 100 tasks, then you can use ntile function in sql to split the 100 tasks into 4 thread, thus for thread column the value will be 1 for 25 task, same way value will be 2 for next 25 task and so on. Now you can use 4 foreach loop container and in the select query before each foreach loop container we can write a condition (foreach loop container 1), select * from configurationtable where thread=1 (foreach loop container 2), select * from configurationtable where thread=2 and so on.
@@learnssis Thank you for such a quick reply. I am not sure if I fully understand this but I will try to change the logic as you suggested and see. If you could do a video on this, I will be super thankful to you.
@@prashanthtalla Read about the ntile function in sql server. Using Ntile function you can divide the rows into equal smaller parts. And now multiple foreach loop container can work parallelly. One foreach loop on one part.
Hi your videos are very useful to me, I have a doubt that if I have a folder containing many files I need to move only a certain name files, is this possible with this, could you please explain to me.
If you want to move specific types of files from one folder to another then you can use foreach loop container along with file system task to do the same ua-cam.com/video/IbieDAatf5M/v-deo.html
You can not put the checkpoint in for loop or foreach loop container, however you can try using the checkpoint in sequence container. For sequence container the checkpoint should work.
Hi I have an issue with for each loop container that is not getting resolved. After I set the variables and try to load mutliple excel files from one folder to sql table. There is an error popping up saying.....Error: the connection string format is not valid. It must consist of one or more components of the form X=Y separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager. The result of the expression "@[User:FP] on property "\Package.Connections[Excel Connection Manager].Properties[ConnectionString]" cannot be writen to the property. The expression was evaluated but cannot be set on the property. Also my table is getting loaded with the same first file mutliple times(5 times) . I have 3 excel files in the folder. I am quite unsure why the loop is not iterating and why the first file gets loaded 5 times. Please help me here. Thanks!
If you are trying to load the multiple excel files, then in the excel connection manager instead of setting the connection manager property dynamically you should set the "ExcelFilePath" property dynamically. ua-cam.com/video/TH9ptAkCHNA/v-deo.html
Hi, Do you have any videos that cover loading data from sql server or any other source - in dest they should create Dim Tables and fact Table, Lets say Dim Table would be Product/Customer/Other(?) and Fact table Sales? It would be great if you could cover a way to create surrogate keys (lets say that in source file we dont have an ID but some text value as primary key) then fact table should store data based on surrogate keys and not text
Thanks brother, this video help me in implement my SSIS package. Its very useful.
You are most welcome brother.
Good one bhaiyya thanku
Thank you Bhai.
Thanks man. Assuming that the target is a database table, how to change this logic to read the files in parallel and load into the target in parallel instead of sequentially?
The actual requirement is a SQL task prior to the Foreach Loop is feeding a list of databases against which same SQL has to be run in parallel (as the SQL runs for a long time) on all the databases that the Foreach Loop receives and the data extracted from each database should be loaded to the same target table in the target database.
You can create a kind of Configuration table and add a column like Thread. Now if you want to run 100 tasks, and you want to create 4 thread for 100 tasks, then you can use ntile function in sql to split the 100 tasks into 4 thread, thus for thread column the value will be 1 for 25 task, same way value will be 2 for next 25 task and so on. Now you can use 4 foreach loop container and in the select query before each foreach loop container we can write a condition (foreach loop container 1),
select * from configurationtable where thread=1
(foreach loop container 2),
select * from configurationtable where thread=2
and so on.
@@learnssis Thank you for such a quick reply. I am not sure if I fully understand this but I will try to change the logic as you suggested and see. If you could do a video on this, I will be super thankful to you.
@@prashanthtalla Read about the ntile function in sql server. Using Ntile function you can divide the rows into equal smaller parts. And now multiple foreach loop container can work parallelly. One foreach loop on one part.
Great video, how can I use the filename to insert into a table, to store that the file has been used?
You can take a look at this video.
ua-cam.com/video/75hI04cbkx0/v-deo.html
Hi your videos are very useful to me,
I have a doubt that if I have a folder containing many files I need to move only a certain name files, is this possible with this, could you please explain to me.
If you want to move specific types of files from one folder to another then you can use foreach loop container along with file system task to do the same
ua-cam.com/video/IbieDAatf5M/v-deo.html
Hi Sir - I have one question in SSIS we can use check points in For each loop container,For loop container & Sequence container
You can not put the checkpoint in for loop or foreach loop container, however you can try using the checkpoint in sequence container. For sequence container the checkpoint should work.
@@learnssis thank you
Hi I have an issue with for each loop container that is not getting resolved. After I set the variables and try to load mutliple excel files from one folder to sql table. There is an error popping up saying.....Error: the connection string format is not valid. It must consist of one or more components of the form X=Y separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager. The result of the expression "@[User:FP] on property "\Package.Connections[Excel Connection Manager].Properties[ConnectionString]" cannot be writen to the property. The expression was evaluated but cannot be set on the property. Also my table is getting loaded with the same first file mutliple times(5 times) . I have 3 excel files in the folder. I am quite unsure why the loop is not iterating and why the first file gets loaded 5 times. Please help me here. Thanks!
If you are trying to load the multiple excel files, then in the excel connection manager instead of setting the connection manager property dynamically you should set the "ExcelFilePath" property dynamically.
ua-cam.com/video/TH9ptAkCHNA/v-deo.html
Hi, Do you have any videos that cover loading data from sql server or any other source - in dest they should create Dim Tables and fact Table, Lets say Dim Table would be Product/Customer/Other(?) and Fact table Sales?
It would be great if you could cover a way to create surrogate keys (lets say that in source file we dont have an ID but some text value as primary key) then fact table should store data based on surrogate keys and not text
below are the videos on loading data to Dim and Fact tables.
ua-cam.com/video/c2lSKLQPb_I/v-deo.html
ua-cam.com/video/duaR_PhLdmc/v-deo.html
@@learnssis Thanks