112 How to import the most recent file from folder using ssis

[@oguzolgun7609]

Hi Aqil, thank you very much. I did not expect such a quick response. you're really amazing and helpful. I hope your channel will get deserved interaction.

[@learnssis]

No problem. I had some free time, and this was one of the important scenario in SSIS on which I had not created a video, thus I thought to make a video on it ASAP.

[@Acito1411]

All of your videos is impactly resourceful

[@learnssis]

Thank you so much for supporting and liking the videos.

[@murthymamidi6639]

Hi Aqil, Good Morning!! Thank you very much for sharing wonderful videos. I have one scenario like, i have a file with datetime in a folder and every time we will get files with datetime and we have to process the recent file with date time and pull data from the file into sql server.so,shall i use this package for my reference .if not can u please help me. Thanks.

[@learnssis]

Yes you can use this package as a reference for your scenario. thanks

[@NanduHSN]

Hi Aquil, thanks for the nice explanation, I do have question , when I try to open script task to write c# code application throws an error as "cannot show visual studios 2015 tools for Application editor.When I searched found that I might be missing Microsoft Visual Studio Tools for Applications x64 Runtime 3.0" was the issue. This was installed corporate account , dont have permission to install any missing software ..And also the above solutions can be done without using script transformation ??

[@learnssis]

Hi Nanda, unfortunately for this requirement to get the most recent date of a file, I don't think of a solution to do this without using the script task.

[@AhmedHassan-wt6lv]

as usual amazing video thanks a lot it will help a lot

[@learnssis]

Thank you Ahmed Hassan.

[@deandrechisolm8638]

Thanks for the tutorial! Any reason why everything gets deleted in the Script Task after running the job once?

[@learnssis]

Yeah this is kind of bug in that version of Visual Studio, if you find the latest updates for Visual Studio then pls install the latest updates.

[@gracec6427]

will the package execute as soon as a new file is in the folder? or would i have to set up like a job to execute the package at given times?

[@learnssis]

The package can not start by itself. You would need to schedule it from sql agent job.

[@1217895]

Hi Aqil, Thanks again for the video. Additionally, can you please show us how to check if the record count > 0 (excluding header) for the last modified file? Ideally, i wouldn't want to import the file if the last modified fille has 0 records in it,thanks

[@sureshgpr]

Hi, i have a files with dsteand time like ac_20230314084452, like, 3 or 4 files in a folder different date and time so how to load most recent file in dstabase table

[@kiranshetty-xb8mn]

Hi, thanks for the video. Can u please let me know how to get the file from nework location or shared drive instead of local folder. Can u pls help me

[@learnssis]

The method to copy file from shared drive or network location will be same. Either you can use the File System task (copy method) or you can use File.Copy method inside script task.

[@kiranshetty-xb8mn]

@@learnssis yes it was working but the file ,it is not picking the latest file. When we give file location we need to give the file name right. Whatever file we are selecting it's getting picked. Can you please let me know how to resolve this.I want that to pick latest file

[@learnssis]

@@kiranshetty-xb8mn I think we are getting the most recent file from this video. If you have the file location and want to get the file name, then you can get it by either using below expression inside FileName variable kzbin.info/www/bejne/g6jUe2WZgNN2Y5o Or inside the script task you can get FileName from file path using below syntax string FileName = System.IO.Path.GetFileName(FilePath);

[@samderrty123]

will this work for Excel?

[@learnssis]

Yeah sure, it will work for any type of file.

[@samderrty123]

@@learnssis I receive an error: TITLE: Microsoft Visual Studio ------------------------------ The component reported the following warnings: Error at Package [Connection manager "Excel Connection Manager 1"]: 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. Error at Package: The result of the expression "@[User::FilePath]" on property "\Package.Connections[Excel Connection Manager 1].Properties[ConnectionString]" cannot be written to the property. The expression was evaluated, but cannot be set on the property. Choose OK if you want to continue with the operation. Choose Cancel if you want to stop the operation. ------------------------------ BUTTONS: OK Cancel ------------------------------

[@amarnadh9272]

Hi How to import last modified multiple files from one folder If i give, start like the file name it has to import all files what ever starting with that name

[@learnssis]

If you want to import files those starts with specific name then you can configure it in the foreach loop container in the Files option. For example if you want to import files starting with customer then in the files option in foreach loop container with file enumerator you can write customer%

[@amarnadh9272]

@learnssis if you are possible, can you make one video for this topic. It would be great for us.

[@learnssis]

@@amarnadh9272 This is shown in foreach loop container videos kzbin.info/www/bejne/j2eck6B_aZpoq6M

[@chandrasekhar-rf6bg]

Very much impressed with the presentation. I want to know the detailed job roles of SSIS developer and SSIS support. Please compare the roles between them with examples. Thank you. 🎉

[@learnssis]

Hi Chandra Shekhar, SSIS developers are responsible for designing, building, testing, and deploying ETL solutions using SSIS. This typically involves designing data integration workflows, creating data transformation logic using SSIS tasks and components, and testing and debugging the ETL process. SSIS developers may also be responsible for optimizing the performance of ETL processes, as well as maintaining and troubleshooting existing ETL solutions. SSIS support personnel are responsible for providing technical support for ETL solutions built using SSIS. This may involve troubleshooting and fixing issues with existing ETL processes, as well as providing support for new ETL development projects. SSIS support personnel may also be responsible for monitoring and maintaining the overall health of the ETL environment, as well as ensuring that ETL processes are running smoothly and efficiently.

[@chandrasekhar-rf6bg]

@@learnssis Thank you Aaqil for your time and help. 🙏🙏

[@learnssis]

​@@chandrasekhar-rf6bg No problem.

[@1217895]

Hi Aqil, don't bother - I figured ot out by viewinhg '36 Load a file only if it contains more than 0 records using SSIS' , thanks bud

[@learnssis]

Okay thank you.

[@dhirendramaurya5361]

👌👌👌💐

[@learnssis]

Thank you.

[@priyankakini8680]

Very Useful. Thanks. But I'm getting error and unable to solve. Could you please help. at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

[@learnssis]

Can you try to log the error message from script task so that we can take a look at the error message. The error message that you shared it is a generic message and shows no details about the reason of failure. Take a look at this video and try to log the error message kzbin.info/www/bejne/qGiqo2anbt-LkLM

[@priyankakini8680]

for anyone getting same error , I was able to solve this by using [user::variablename]… thanks for this video so helpful

[@learnssis]

@@priyankakini8680 Glad you figured it out.