SSIS Package||How to stop loading Empty files into Database Tables?
Steps to handle empty files and not to load into SQL tables:
1. Create 3 variables (SourceFolder,IsNodata(Int32)and FileName
2. Write Expression in ForeachLoop Container (FELC)for Directory Property
3. Gave File Extension .txt and opt for Fully Qualified
4. Script Task (ST) inside FELC and select FileName as ReadWriteVariable and IsNodata as ReadWriteVariable and Write code for finding length of the file
5.Drag Data Flow Task (DFT)inside FELC and connect with ST and write Expression and Constraint on Precendence Constraint as @IsNodata==0
6. Configure DFT with FlatFileSource and OLEDB Destination
7. Drag FileSystemTask(FST-1) to move all data files to one folder inside FELC and connect with DFT and Configure FST-1 as FileName as Variable to Sourcepath and Create connection with Existing Folder in Destination property with path
8. Change Delay Validation Property as TRUE for FST-1
9. Drag one more FST-2 and connect with ST to move empty files to Empty Folder and Configure FST-2 as FileName as Variable to Sourcepath and Create connection with Existing Folder in Destination property with path
10. Write Expression and Constraint on Precendence Constraint as @IsNodata==1 for FST-2
10. Change Delay Validation Property as TRUE for FST-2
11. Write Expression in FileConnectionManager for Connection string FileName variable.
