110 How to check if File exists in SSIS

[@highsky9682]

how to check if data already exists in table. can multiple lookups be used?

[@learnssis]

Lookup can be used to check if data already exists in table and yes multiple lookup transformations can be used as well. In below video I have used multiple looks kzbin.info/www/bejne/mqbEg5KGnbGXo8U

[@tejaswinerella5223]

Hi Aqil, While executing the package I'm getting a runtime error in the script task. Can you please help me with this... 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() Error: 0x1 at Script Task: The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there. Task failed: Script Task Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "D:\practise\110 How to check if File exists in SSIS\Package.dtsx" finished: Failure.

[@learnssis]

The element cannot be found in a collection This means you are trying to use a variable that you have not selected in the readonly variable or readwrite variables.

[@tejaswinerella5223]

@@learnssis Yeah, I understand this I checked the variables it's there..

[@awwalhameed6711]

Thanks!

[@learnssis]

Thank you Awwal Hameed for your support.

[@oguzolgun7609]

Hi Aqil, thank you so much for the video. Is there any way to check if the file is exist in the source folder without script task? And can I use this solution for variable file names like ABC_customer_20230213.csv, DEF_customer_20230214.csv instead of static like customer.csv?

[@learnssis]

Sorry I am not aware about any other method without using script task to check if a file exists or not.

[@oguzolgun7609]

@@learnssis thank you so much!

[@oguzolgun7609]

​@@learnssis Another question here :) I tried this solution for variable file names like customer_1.csv customer_2.csv.. But When I run the package, it doesn't find the files. Maybe you remember that you helped me about one of my question to get most recent files from folder. I'm trying to add a check if there is a file exists with required name pattern, or not. If no, I won't run the package, if yes, I will.

[@learnssis]

@@oguzolgun7609 In this video, I showed the logic by using an SSIS variable File1 in the script task, so this will surely for variable file name, file name is not hard coded here, you just need to pass the FilePath from foreach loop container and use that variable instead of File1 ssis variable.

[@oguzolgun7609]

I did it, thanks. When the folder is empty, foreach loop container works, but script task in the foreach loop container doesn't work. Do you have any idea?

[@w0w1t0]

Good tutorials dude :) This can be done on a folder instead of a single file? Thx :D

[@learnssis]

I think this can be done using a foreach loop container so that we won't need to write code multiple times. We can pass the FilePath using Foreach loop container and that should work.

[@w0w1t0]

@@learnssis thanks I Will try It :)

[@learnssis]

@@w0w1t0 Let me know how it goes.

[@learnssis]

But I think there is a challenge here, how we will know which file exists and which file does not exists, we would need to store all files names somewhere may be in a sql table and then using a Loop may be Foreach loop with ADO enumerator we will create the full file path based on a file name from sql table and then using the script task we will check if file exists or not. 1. Get the file name from a sql table using execute sql task, assign it to an Object variable 2. Use For each loop container with ADO enumerator and assign the value of file name to ShortFileName SSIS variable 3. Now in the script task we can create a FilePath by concatenating Folder Path and FileName. This FilePath we can use to check if File Exists or not. And then remaining code of Script task can be used as it is and rest of the package code will be same. This way we won't use multiple tasks for multiple files.

[@sunithagg]

Hi, Did you get it working on more than 1 file. I have only 2 files to check in a folder. I edited same script task with different variables and used Logical End in expression and condition to check. But it's not working as expected.

[@jagadeeshpinninti]

Hi, Can you please make a video comparing the values of 2 db like OLTP & DW --- sales Quantity, Cost price like multiple fact values -- to sync all the data between 2 db are same

[@learnssis]

I have not worked on loading the data to data warehouse very extensively. I have worked on loading the data for Dim tables from an OLTP to DW and then loaded few Fact tables as well but I don't not a lot of experience on it.

[@LucianoVazBH]

CONGRATULATIONS !!! Your videos are very educational. could make a video showing how to export the result of a query as a .csv file to a SharePoint Online folder, with validation if the file has already been exported or not.

[@learnssis]

Thanks Luciano at the moment I am out of station and will be back on Sunday. At the moment I don't have access to any SharePoint Online folder, I need to see if I can find any public SharePoint online folder or something to test it.

[@aliviagibson4014]

v6mlam VOR.RED