March 11, 2010 at 10:42 am
I have a SSIS package that loads order files (txt file) from ftp server to the database, archive the file, and delete the file to the ftp server.
It runs every 15 vminutes via SQL agent job.
The main components of my package are
1. FTP task 1 - Load files from FTP server to a local folder.
2. Data flow Task - process and load file/s from local folder to SQL database.
3. File System task - Move the order file to new folder after processing.
4. FTP Task 2 - Delete file to the ftp server.
Everything is working fine.
Sometime it takes more time to process the package depending on the file size and no of files in the ftp server.
However, I have the following question.
if the customer palce new order file while the package is running, what happens?
For example:
inside the package
FTP task1 starts at 11:05:00 AM,
Data Flow Task starts at 11:05: 39 AM
File System Task starts at 11:08:02 AM and
FTP Task2 starts at 11:08: 22 AM
if the customer places new order after the ftp task1 loaded files and before ftp task2 deletes the file, let's say 11:06:00 AM, in this case, does ftp task2 delete the new order file with loading?(which is supposed to load in next run.
Do I loose that order file from the ftp server?
March 11, 2010 at 10:50 am
Not having your SSIS package in front of me, hard to say. My suggestion, test it for that scenerio.
March 11, 2010 at 11:15 am
It is not easy to send you the whole package. I will try in my next postings.
However, lat me explain you again in simple case.
SSIS PackageA has 3 tasks:
FTP task1 - get file/s from ftp,
Dataflow task - process file/s, and
FTP task2 - delete file/s to the ftp server.
Let's say the package starts at 11:15 AM and ends successifully at 11:17 AM.
if another customer places new order file at 11:16 AM,
Does FTP task2 delete that file?
What happens if the new file is placed between 11:15 AM And 11:17 AM in ftp server?
I want to know like rollback, commit, lock which is used in T_SQL.
March 11, 2010 at 11:40 am
Again, test this scenerio in your test environment. That is the best way to determine what will happen and to determine what you need to do to mitigate the problem.
Even if I had your code, I would probably do the same thing, test, test and test some more.
From your simplifed explaination, I still could not tell you what might happen. I would need to see the actual code to be able to figure out what may or may not happen.
March 11, 2010 at 2:43 pm
ganeshlohani (3/11/2010)
It is not easy to send you the whole package. I will try in my next postings.However, lat me explain you again in simple case.
SSIS PackageA has 3 tasks:
FTP task1 - get file/s from ftp,
Dataflow task - process file/s, and
FTP task2 - delete file/s to the ftp server.
Let's say the package starts at 11:15 AM and ends successifully at 11:17 AM.
if another customer places new order file at 11:16 AM,
Does FTP task2 delete that file?
What happens if the new file is placed between 11:15 AM And 11:17 AM in ftp server?
I want to know like rollback, commit, lock which is used in T_SQL.
I had a very similar situation.. This is what i will suggest you to do
Use a for each loop and place FTP Task 2 and File system task inside the FEL... Now for every file in the folder it Deletes the remote file using FTP Task and then Moves it to another folder using File System task. All the new files placed in the FTP folder after the load(FTP task 1 succeeds) will be processed on the next run
Hope this helps you
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply