Hi Aqil, quick question - what if my connection to SQL server is via SQL Authentication and not Windows authentication? What would the connection string string connString = @"Server =DESKTOP-EKJ1P64\SQL2019; Database = Work; Trusted_Connection = True;"; be modified to?
Hi I need your suggestion in one of my scenario what I am facing now.In folder we have n number of CSV files in that I should give priority for particular input file(filename vl be in dynamic) in foreachloop container how should I achieve that?
What I would do is that I will use a foreach loop container with file enumerator and then inside foreach loop container I will use an execute sql task and in that I will insert the filename into a staging sql table along with a column like Priority or something. Now after the foreach loop container I will use an execute sql task and in that I will assign a number to the priority column based on an update statement, we can use row_number function to get a number and assign the number. Now I will use another execute sql task to select the files from the table according to priority and then will use another foreach loop container with ado enumerator to get the file name. Now inside the foreach loop container as you have the filename thus you can do anything with file like loading the file to sql server or something.
Hi Aqil, thank you for the help with this video. I have a question, why my script task always throws error: "DTS Script Task has encountered an exception in user code... exception has been thrown by the target of an invocation" ? I've tried to change the "TargetServerVersion" but it didn't work. Thanks
Try to implement error handling in script task and it can log the actual error message may be to a flat file. You can take a look at this video where I have shown how to implement this. th-cam.com/video/r7Gr3q9zXZQ/w-d-xo.html
Thank you very useful !! What if file contains current Date with file name i.e. UserInfo_17052022.CSV .. Date value gets changed everyday. If there are multiple files with same name then i want to load file which contains Maximum records.
Hi Deepak, if the file contains current date then we can just everything before the under score _ and check for that value in the List if that value exists in list then load the file. For your second scenario that if multiple files found with same name then we need to get the count of each file by using below query System.IO.File.ReadAllLines("path").Length And append this value to a list and then you can sort the list in descending order and just load the top file.
Aqil, excelent video. Thanks for taking the time to answer my question. It simple and very concise. Looking forward to watching the rest of your videos. I hope you’re making a lot of money with your videos :).
No problem. I found your question interesting thus thought to make a video on this one. When I started creating the video then I found it more complex than I thought initially. As of now I am not earning a lot the views on the videos are not that good as you can see, but may be if you guys will support then in future I can earn more :)
@@learnssis In the script you don't need to load all the Filenames into a list and then search for a match. Initialize SSISFileName before then inside the while loop you can check for a match as you go and immediately return if success. In fact, loooing through entire FileNames table every file load is redundant and hinders performance. Surely there"s a more elegant way.
Thanks so much for your support. But honestly speaking you don't need to do this. I make the videos because I like doing this, I made this video because I found the question interesting. If you can just watch my videos that will be a good support, you don't need to pay anything. Anyway thanks for supporting.
Hi Aqil, thank you for this video. It almost answers my questions. I have another one, in a project I have multiple files in my source folder like below; orders_20230105_0700.csv orders_20230105_1000.csv orders_20230105_1300.csv I need to process the latest one then move it to the Process folder, and archive others directly to the Archive folder. I can manage the file moving part but I'm confused about this part; how can I get the latest file to process and how can I move others without any process? I saw your youtube channel yet, so if you have some videos about this I must have missed it. If you have time to answer me I would be glad. Thanks you
Thank you Oguz for your comment. I will try to make a video on the scenario you mentioned to import the data from most recent file and move the file to processed folder and then move rest of the files to the archive folder.
@@oguzolgun7609 No problem its fine. The video should be uploaded in another 30 min but it will be unlisted however you can watch it from the link I will share.
Hi Aqil, quick question - what if my connection to SQL server is via SQL Authentication and not Windows authentication? What would the connection string string connString = @"Server =DESKTOP-EKJ1P64\SQL2019; Database = Work; Trusted_Connection = True;"; be modified to?
![🔴 ฟุตบอลแชมป์กีฬา 7HD แชมเปียน คัพ 2024 [รอบรองชนะเลิศ]](http://i.ytimg.com/vi/zPm8ClLVhoE/mqdefault.jpg)
Hi Aqil, quick question - what if my connection to SQL server is via SQL Authentication and not Windows authentication?
What would the connection string
string connString = @"Server =DESKTOP-EKJ1P64\SQL2019; Database = Work; Trusted_Connection = True;";
be modified to?
Sir please do one video on performance tuning in ssis, sql server
Sure, will make a video on this topic as well.
Hi I need your suggestion in one of my scenario what I am facing now.In folder we have n number of CSV files in that I should give priority for particular input file(filename vl be in dynamic) in foreachloop container how should I achieve that?
What I would do is that I will use a foreach loop container with file enumerator and then inside foreach loop container I will use an execute sql task and in that I will insert the filename into a staging sql table along with a column like Priority or something. Now after the foreach loop container I will use an execute sql task and in that I will assign a number to the priority column based on an update statement, we can use row_number function to get a number and assign the number.
Now I will use another execute sql task to select the files from the table according to priority and then will use another foreach loop container with ado enumerator to get the file name. Now inside the foreach loop container as you have the filename thus you can do anything with file like loading the file to sql server or something.
Thank you for the solution.
Hi Aqil, thank you for the help with this video. I have a question, why my script task always throws error: "DTS Script Task has encountered an exception in user code... exception has been thrown by the target of an invocation" ? I've tried to change the "TargetServerVersion" but it didn't work. Thanks
Try to implement error handling in script task and it can log the actual error message may be to a flat file. You can take a look at this video where I have shown how to implement this.
th-cam.com/video/r7Gr3q9zXZQ/w-d-xo.html
Thank you very useful !!
What if file contains current Date with file name i.e. UserInfo_17052022.CSV .. Date value gets changed everyday.
If there are multiple files with same name then i want to load file which contains Maximum records.
Hi Deepak,
if the file contains current date then we can just everything before the under score _ and check for that value in the List if that value exists in list then load the file.
For your second scenario that if multiple files found with same name then we need to get the count of each file by using below query
System.IO.File.ReadAllLines("path").Length
And append this value to a list and then you can sort the list in descending order and just load the top file.
Hi can you use a where clause when you select the file names from the table . I have tried but it still loads all the files that are in the table
Take a look at this example
th-cam.com/video/S0YlV3J2x3U/w-d-xo.html
Aqil, excelent video. Thanks for taking the time to answer my question. It simple and very concise. Looking forward to watching the rest of your videos. I hope you’re making a lot of money with your videos :).
No problem. I found your question interesting thus thought to make a video on this one. When I started creating the video then I found it more complex than I thought initially. As of now I am not earning a lot the views on the videos are not that good as you can see, but may be if you guys will support then in future I can earn more :)
@@learnssis In the script you don't need to load all the Filenames into a list and then search for a match. Initialize SSISFileName before then inside the while loop you can check for a match as you go and immediately return if success. In fact, loooing through entire FileNames table every file load is redundant and hinders performance. Surely there"s a more elegant way.
@@glennbabic5954 Yes you are correct. We are doing some unnecessary work here. Thanks for your suggestion.
@@learnssis I've always been a nasty nitpicker sorry. Thanks for your videos they teach me a lot and help me prepare for interviews!
@@glennbabic5954 You are awesome. We all can learn from each other. Keep watching.
Thanks!
Thanks so much for your support. But honestly speaking you don't need to do this. I make the videos because I like doing this, I made this video because I found the question interesting. If you can just watch my videos that will be a good support, you don't need to pay anything. Anyway thanks for supporting.
👌
How to load several files from database to folders through ssis
Take a look at this video Aditi
th-cam.com/video/I7em3qfJ0N4/w-d-xo.html
Hi Aqil, thank you for this video. It almost answers my questions. I have another one, in a project I have multiple files in my source folder like below;
orders_20230105_0700.csv
orders_20230105_1000.csv
orders_20230105_1300.csv
I need to process the latest one then move it to the Process folder, and archive others directly to the Archive folder. I can manage the file moving part but I'm confused about this part; how can I get the latest file to process and how can I move others without any process?
I saw your youtube channel yet, so if you have some videos about this I must have missed it. If you have time to answer me I would be glad.
Thanks you
Thank you Oguz for your comment. I will try to make a video on the scenario you mentioned to import the data from most recent file and move the file to processed folder and then move rest of the files to the archive folder.
@@learnssis thank you so much, If I you think I can help you somewhere, please let me know. :)
@@oguzolgun7609 No problem its fine. The video should be uploaded in another 30 min but it will be unlisted however you can watch it from the link I will share.
@@learnssis you're amazing. thank you so much!
@@oguzolgun7609 Below is the link to the video
th-cam.com/video/O1xVlKq8cqY/w-d-xo.html
How to load recent files in a particular folder by using file time stamp.
Please take a look at this video, I am doing almost same thing here
th-cam.com/video/rtYir_2SlPM/w-d-xo.html
Tq sir
Hi Aqil, quick question - what if my connection to SQL server is via SQL Authentication and not Windows authentication?
What would the connection string
string connString = @"Server =DESKTOP-EKJ1P64\SQL2019; Database = Work; Trusted_Connection = True;";
be modified to?
Take a look at this video
th-cam.com/video/MZfpiEYHu3I/w-d-xo.html