September 11, 2008 at 9:50 pm
Okay, I am sorry if you aren't understanding what I am asking, but if the mainframe guys are FTPing files to your server, you have to have an FTP service running on it to receive the files.
There are several ways that the FTP Service under IIS can log FTP transfers. The first is to a text file, the second to an Access database, and the third is to a SQL Server database. You can write a process that reads the text log files for successful transfers and starts the appropriate process when found, or using a SQL Server table in a database, you can setup triggers to do the same. Not really sure how you'd use an Access database in this case.
At my previous employer we logged all FTP transfers to a SQL Server database table and used triggers to start the appropriate import processes upon a successful transfer from our minicomputer systems. This worked great for us, as it eliminated the problem with missing import windows due to processes running longer than anticipated. Also, the server running the FTP service was not used for anything except for the data transfers between the OLTP system and SQL Server, so the impact on the performance of SQL Server on that system was not critical.
😎
September 12, 2008 at 7:33 am
Lynn, FInally Got IT. But for one FTP process I don't thing they would like to do it. ALso, on my side I only have one ssis packages to load all three files. So I have to kick off three times .
Jeff, no extension on file. Everey file I get from mainframe have no extension................
September 12, 2008 at 7:54 am
Does the empty file have a specific filename that differs from the datafile? Like yyyymmddLoadctl.txt? if so you can check for the existance of that file and if it doesn't exist exit or loop until it sees this file.
September 12, 2008 at 8:21 am
The empty file name at present is just "emptyfile" for all three difftypes of datafile.
However mainframe are willing to chnage it to different name like PROFemptyfile, INSTemptyfile and DENTemptyfile. ALso, they can put in diff folders for each type. At present, everything is coming to InboundFolder
September 13, 2008 at 8:21 am
gyanendra.khadka (9/12/2008)
Lynn, FInally Got IT. But for one FTP process I don't thing they would like to do it. ALso, on my side I only have one ssis packages to load all three files. So I have to kick off three times .Jeff, no extension on file. Everey file I get from mainframe have no extension................
gyanendra.khadka (9/12/2008)
Lynn, FInally Got IT. But for one FTP process I don't thing they would like to do it. ALso, on my side I only have one ssis packages to load all three files. So I have to kick off three times .Jeff, no extension on file. Every file I get from mainframe have no extension................
Then, all of this is pretty simple overall, but maybe I'm missing something here...
1. If the Empty file isn't there, you short circuit your process to exit early.
2. If the empty file IS there, your process runs all the way through. It renames the files it imports/processes as "processed" and when it's done, it deletes the empty file.
On the way, if you want to "archive" the files they send you, you can add an extension to each file processed containing yyyymmddhhmmss so you can easily tell when they were processed. Since these file names are in a different format (original files have no extensions), then this action would mark the files as "processed" and you either leave them in place or move them to an archive directory.
I suppose you could do all of this with DTS or SSIS, but I usually do all of this in T-SQL with a couple of calls through xp_CmdShell. Yeah, I know... people take grand exception to that for "security" purposes. That's only if humans have access to the box. My ETL servers are usually on dedicated boxes that require no human intervention. They can be inexpensive boxes that use the likes of MSDE so you can stack 'em all over the place.
You don't need a file watcher or any special software to do any of this. And, if you try to open a file that an FTP process is writing to, it's like any other DOS file... it'll give you an error that you should be able to trap for.
Just go for it...
--Jeff Moden
Change is inevitable... Change for the better is not.
September 13, 2008 at 9:07 am
If the empty file doesn't appear until the data file has been completely transferred and they are willing to change the name then that sounds like one of the easier solutions. another alternative is to use a script task to see if the file is locked if so you can stop the process or have a function that waits a set period then tests the file for a lock again.
September 13, 2008 at 4:46 pm
Jeff I belive u r missing something here......................
Let me tell you the situtation at present.
One Inbound folder three data files, three empty files for each data file....... the empty file is created by mainframe at the end of each succesful FTP . ( mainframe has three FTP job that kick offs at the same time and puts three data files and three empty files in teh Inbound folder) the empty files name is same so here i am assuming that when they do FTP it will overwrite it on the inbound folder..
My ssis pacakage..... i check emptyfile , if not present short circuit......package complete........
if prsent......go delete empty file......
enter in the foreach loop and load data into table, rename the file and archive it.......if there are alreday 3 data files no problem.......
or any 2 data files no problem......or 1 data file no problem.....
the only problem here is ..........one of the data file is alreday there which is colplete say fileA and another FileB is present there but its not complete. the mianframe are processing it.........so when i enter foreach loop.it sees two data files loads fileA and when colpmete start loading fileB which is still processing.........i am thinking this will slow down my process becoz i am waiting for FTP to wrtite and load ........But another situation FTP failed I have a incomolete set of data..........or may i will fail becoz of imcolete row terminator.........
Another situation which i testsed was i am in for each loop alreday ai m aprocessing fileA, during this if i receieve fileB for each loop will not process that file............
the only pronlem is i have two files in teh folder, one file is compltete and another is in the middle of processing........
I hope you got my situation...........
September 13, 2008 at 5:01 pm
You said in a previous post that the filename was just "EmptyFile"... you can't have 3 files in the same directory names "EmptyFile". And, no matter what it's called, you shouldn't delete the "EmptyFile" until you processed the full one. The mainframe isn't going to write an "EmptyFile" until the "full" file is present so you should be keying off the presence of the related "EmptyFile" and not the full file. It would be very helpful if the name of the "EmptyFile" for each of the 3 files was identical to the related full file in everyway except the extension. If the "full" file had a name of ABCDEFG with no extension, then the empty file should be named ABCDEFG.mty or something similar.
... Please post a directory listing of the file names so we can see what's really going on because either you're not explaining it right, or I'm just not understanding why you're having a problem on this.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 13, 2008 at 9:49 pm
ok Jeff, may be now I am confused too...........
here let me give the real name and real scenerio of my ssis package........
the folder where i receive all three files is D:\medclm\Inbound
the three file names are medclmPROF, medclmDENT,medclmINST ( no extension)
all empty zero KB size files are called "emptyfile".
Situtaion from mainframe.......mainframe has 3 jobs of their own, indepenedent of each other....
they produce this three files and FTP to my above folder. at the end of each successful FTP they will produce a zero KB size file called emptyfile. this indicates that their FTP is successful and a trigger for me to start the load................( the mainframe are willing to produce 3 diff empty files on the inbound folder like PROFemptyfile, INSTemptyfile and DENTemptyfile.........remeber rt now we are talking about same folder where all three files and three empty files will be)....
I am assuming ( and sure ) here that if we keep the same name for emty file like emptyfile, one will override the other ( for example when mainframe FTP is complete for medclmPROF file, i will have 2 files in the inbound folder medclmPROF and empty file and when FTP is complete for medclmINST i, i will have medclmINST file and emptfile which will override the previous one........and so for medclmDENT.........
MY SSIS..........I check emptyfile in Inbound, not found then log to table.......
If emptyfile present then i move down , nextstep i delete that emptyfile and then i enter foreachloop. inside my foreach loop i have dataflow, filesystem and sql statement......
here the situtaion where the problem might be ..............
lets say i have medclmPROF file and emptyfile associated with it. also there is medclmDENT file in teh inbound folder but not complete..,( not complete in the sense mainframe are still in the middle of processing it or copying it , i am assuming file size in several GB......)
so i start my ssis, i see empty file so i delete it and i enter foreachloop......i see 2 files medclmPROF and medclmDENT ( remeber here medclmDENT is not complete, mainframe are still coying it to my folder) I load medclmPROF and then I archive it and log record count, then I will process medclmDENT too becoz it is alreday there........NOW my questions how does this effects my load since FTP is not complete yet and mainframe are still coying the data.....Second what if FTP failed in the middle of the process? I have incomplete data that were processed.......so I know my package will fail becoz there will be some error in row delimeter or some sort.......if it get success still that was incomlete set of data.............? this is the problem i am talking for now.......
before i was talking about another situtation too.....but i tested it is solved bu itself , the nature of foreachloop......the situtaion is......i am in the middle of loading medclmPROF file, then i receive medclmDENT file which is being copied by http://FTP....here the foreach loop ignores this file medclmDENT
, what i have noticed is foreach loop verifies no of files when it enters the loop, once in teh loop it ignores any other file that are beging copied to teh folder.....
so the problem here is the one i mentiode two files present when i start ssis but only one is complete and the other in teh middl eof processing............
September 13, 2008 at 9:59 pm
But, that's the whole problem. Your code is loading the file names of the full files and that's not the right thing to do. Your code should only look for an empty file. If it finds one, then an only then should it look for the matching full file. Just because you have one empty file, it doesn't mean the other two full files are ready. The empty file is the key as to whether a full file is ready or not. Read the empty file name and keep it at a 1:1 relationship between an empty and a full file.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 13, 2008 at 11:22 pm
Finally u Got it ..............
You know what, the environment i am working changes 3 times in a day.... u go in the morning and edit the work from last time,,and during teh lunch they will tell you to completely chaneg the solution and fianlly before u leave they will ask you to bo back and add more.......there was complete diff situation before..... and at the last minute they chaneg the whole situation, and now we don't have time for more coding.....I know teh situation might might nerevr happen....this is just one of those IF then WHAT?.......
Later if this creates a problme I have a solution which miggt be not a nice and tidy one....
I will tell mainframe guys to FTP three files and emptyfile to three diff folders
then in SSIS i will check in each folder that if empty file is present copy the data file to inbound folder and delete the empty file......... and then I will load data
this will solve the problme for now..................although i am doing double work for copying
September 13, 2008 at 11:40 pm
I think 3 separate directories might be a bit of overkill, but if that works for you, good enough. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
October 6, 2008 at 9:34 am
Hello,
First ask your FTP people to give you the time when the load starts
second: make sure you run your job after this time.
third : before start doing the load verify that both files exist on your FTP folder.
fourth: when done with the import make sure to delete the empty one.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply