Move Files SSIS Task
-
Hi
I have a task in my SSIS package that moves files from source folder to destination folder. It works if there are no duplicate files being processed. That is, suppose fileABC.txt is found in both source and destination folder then something went wrong in the processing from earlier SSIS tasks so I want to be made aware of this. I have a SSIS task that outputs a message to the window to alert me. I do not want to overwrite the file in the destination folder but instead leave the file in the source folder. My SSIS package isn't handling the duplicate file. It errors out. The 'failure' precedence control should have handled the error gracefully so the container is a success so subsequent tasks can proceed. Since the container failed then all subsequent tasks will not run. How can I fix my SSIS package?
Tasks:
Error Message:
SSIS package "C:\Users\Me\Documents\Analysis Toolkit SSIS\Package.dtsx" starting.
Error: 0xC002F304 at File System Task, File System Task: An error occurred with the following error message: "Cannot create a file when that file already exists.
".
Task failed: File System Task
Warning: 0x80019002 at Move Files To Processed Folder: 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.
Warning: 0x80019002 at Update Event Table: 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.
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 "C:\Users\Me\Documents\Analysis Toolkit SSIS\Package.dtsx" finished: Failure.
The program '[33316] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).SP for displaying message if failure:
/****** Object: StoredProcedure [dbo].[PrintConfirmationofFailedMove] Script Date: 2025-03-28 12:01:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[PrintConfirmationofFailedMove]
as
SET NOCOUNT ON;
RAISERROR('Investigation Required - Could not move files to processed folder', 10, 1) WITH NOWAIT;Thank you
-
Instead of moving processed files to a single archive folder, create a timestamped folder in the target folder and move the file there.
You will never get collisions if you do this.
Also ... 'message to the window' ... what's that? SSIS packages are designed to run unattended on a server, where there is no window.
-
Couldn't you check if the file exists In the destination folder first and if so move to unproccessed folder and then continue processing the next file?
- Phil Parkin wrote:
Instead of moving processed files to a single archive folder, create a timestamped folder in the target folder and move the file there.
You will never get collisions if you do this.
Also ... 'message to the window' ... what's that? SSIS packages are designed to run unattended on a server, where there is no window.
I am trying to keep the destination folder structure as simple as possible so that is why I picked just one output destination folder. Is there a way I can fix my current SSIS tasks?
Regarding your comment 'message to the window'...I run the package manually each time I need to so I am referring to the output window in SSIS. I haven't bothered using an agent because there are some prep work I have to do with is sadly manual so since I am already doing the manual prep work then I can manually start the SSIS package when I am done the prep.
- Tav29 wrote:
Couldn't you check if the file exists In the destination folder first and if so move to unproccessed folder and then continue processing the next file?
I am not sure how to do this in SSIS. Do you have a resource that shows how to do this?
-
What helps me, I would flowchart the entire process and then create your SSIS Package.
-
a simpler way is to run Powershell Script:CheckforSameFileNames.ps1 before processing files
Script Code below
Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Scope CurrentUser
Write-Host "Execution policy set to Unrestricted for the current user."
# Define the source folders and destination folder
$sourceFolder = "C:\Source"
$DestinationFolder = "C:\Destination"
$Unprocessed = "C:\Unprocessed" # File exists already in destination folder
# Get the list of files in both source folder and destination folder
$filesInSource1 = Get-ChildItem -Path $DestinationFolder
$filesInSource2 = Get-ChildItem -Path $sourceFolder
# Iterate through the files in the first source folder
foreach ($file1 in $filesInSource1) {
# Check if a file with the same name exists in the Destination folder
$file2 = $filesInSource2 | Where-Object { $_.Name -eq $file1.Name }
if ($file2) {
# Move the file from the source folder to the Unprocessed folder
Move-Item -Path $file2.FullName -Destination $Unprocessed
}
}
-
Add a Script Task to check if the file exists, set a boolean variable, and use precedence constraints to skip the move if duplicate, logging the event without failing the package. Forgot my deadline completely until the night before. In a panic, I found domypaper.com and placed an order. The writer was super responsive, and the result was amazing. DoMyPaper helped me avoid major embarrassment in class. Honestly, a huge relief.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply