September 24, 2018 at 8:54 am
if all specific files are exist then process to archive folder in ssis.
source folder : c:\sourcepath\
in the source path have files like : a.txt,b.txt,c.csv ,d.csv,e.txt
here I need to move files only a.txt,b.txt,c.csv ,d.csv only , no need to process e.txt file.
if this files(all required files) are exist then move this files into archive folder.
if any one file is not exist then no need to move this files into archive folder.
examples: if source path have only a.txt and b.txt files and not have c.csv and d.csv then no need to process.
similary all cases(a.txt exist not exist b.txt or other files then stop)
I design package like below:
declare variables:
sourcepath : c:\sourcepath\
filename : a.txt
sourcefullpath: c:\sourcepath\a.txt( expression sourcepath + filename)
archivepath : c:\archivepath\
archivefullpath: c:\archivepath\a.txt ( archivepath + filename)
after that drag and drop foreachloop container:type is : foreach fileenumerator ...>directory..>sourcepath variable
file type :*.*
inside foreachloop drag and drop filesystem task ..> then operation : move files ....>source connection >is sourcepathvariable >true..>source connection>sourcefullpath variable > is destinationpath variable>true >destination connectin >archivefullpath variable.
here I am unable to implement script for all files checking condition that all are exist or not.
please tell me how implement to write/check all required files are exist or not before move to archive folder using ssis package.
September 24, 2018 at 10:55 am
You can go nuts trying to figure out how to wire boxes together to do something like this, or you can open a Script task and do some actual coding. This would be a very easy problem to solve in C# with .Net IO methods.
September 24, 2018 at 11:53 am
Scott Coleman - Monday, September 24, 2018 10:55 AMYou can go nuts trying to figure out how to wire boxes together to do something like this, or you can open a Script task and do some actual coding. This would be a very easy problem to solve in C# with .Net IO methods.
Agree - however I would like to understand the requirement of having all files available before archiving any files. The process should be written so that any file dropped into a source location can be loaded, processed and archived - and then, when all required files have been loaded and processed, a final step can be executed if needed.
A simple execute SQL task would be set that checks a table for all loaded/processed files. If all expected files have been loaded - continue processing else quit.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 24, 2018 at 2:07 pm
I would imagine the wait-until-all-four-files-are-present rule may have to do with the files not being created (or completely written) simultaneously. One integration I work with may take a while to write an entire file, but when finished it writes a zero-byte file of the same name to another folder. So looking for the control file instead of the original data files avoids a lot of file lock errors.
And I would prefer to deal with the files in .Net code rather than SQL, to handle the IO tasks like adding timestamps to the filename, moving files to the archive folder, and so on.
September 24, 2018 at 2:39 pm
This should be very straight forward in powershell.
$testy = @("C:\Test\test.txt", "C:\Test\st.txt")
If( (Test-Path $testy) -notcontains $false)
{
Move-Item $testy "C:\Test\Test\"
Write-Output "All found"
}
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply