May 24, 2013 at 8:10 am
I'm having a problem with files not being moved properly to their destination directory.
I have an SSIS package that generates comma delineated text files. Once the file is created, there is a File System Task that Copies the file to a directory on another server for archival purposes and then another File System Task that Moves the files to its production destination on the same server that the file was archived to.
For example.
Created Locally on
C:\Out\FileGenerator\
Copied to
\\AnotherServer\g\AnalyticsProgram\ArchiveSQL\
Moved to
\\AnotherServer\g\AnalyticsProgram\
AnalyticsProgram is a folder that is accessed by an Analytics program that we use. It has a service that runs on that server (named AnotherServer here) where it imports and deletes the files I create, archiving them in a subdirectory of its own.
The Source Connection and Destination Connection in both the File System Tasks are variables. The file names are generated by a combination of File Type Designation (e.g 001) numeric StoreID (e.g. 10025) and underscore and the date of the data (e.g. 20130524) followed by the iteration of the file (e.g. 10012) and finally .txt
The Archive files then append (before the .txt) another underscore and the creation date, formatted as above.
A sample file name would be
00110025_2013052410012.txt
With an archive file of
00110025_2013052410012_20130524.txt
The job has two schedules
The first runs at 45 minutes after the hour every hour between 7 AM and midnight.
The second is a cleanup run at 12:30 AM.
The job runs every day, seven days a week.
The job is also kicked off from another job at 7:30PM, 10:30PM, 1:30AM and 3:30AM every day, seven days a week. This ensures all data is generated before it moved to the data warehouse.
The 12:30 run is left over from when we only had one job to move data to the data warehouse at 1 AM instead of the 4 listed above.
Our business EOW is Thursday with Friday being the start of a new week.
Every Friday morning at 12:30 AM, the files are created properly and are copied to the archive folder, but are not moved to production.
In a normal move, the files disappear from the creation directory, so if the move simply wasn't happening, the files should be left in C:\Out\FileGenerator\ but the folder is empty, as it should be if everything is working properly.
The files all end up where they're supposed to every other run for the week.
This anomaly has happened three weeks in a row now. Always at the same time on the same data and never any other time or date.
In an attempt to see what was happening, I turned off the service that imports and deletes the files from \\AnotherServer\g\AnalyticsProgram at 12:15AM and watched the folder from 12:28AM to 12:32AM. Nothing happened. When I checked the archive subfolder, the files were there, they simply never appeared in the main folder.
When I coped the files from Archive to Production (10 minutes later since I had to rename the copies I made of the Archive files, removing the generated-date suffix), they appeared properly. When I turned the service back on, they were consumed properly.
I've done a search of the destination server and the origin server and the files haven't been moved to a different location, they're just gone.
I'm baffled. What is happening here? Anyone have any ideas?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 24, 2013 at 8:39 am
And you've verified there are no other jobs moving these files? That the config file / table for this package doesn't accidently use another location? That the SSIS package only uses one configuration source (not multiple files or tables) that could be interfering?
And that it's not referencing any config information on your local desktop that might be overwriting what you're expecting?
May 24, 2013 at 9:00 am
Brandie Tarvin (5/24/2013)
And you've verified there are no other jobs moving these files? That the config file / table for this package doesn't accidently use another location? That the SSIS package only uses one configuration source (not multiple files or tables) that could be interfering?And that it's not referencing any config information on your local desktop that might be overwriting what you're expecting?
I have checked that no other job is running at that time.
I've done full searches of both the destination server and the origin server and the files don't end up anywhere else. There is one other folder where they could end up, but they don't go there either.
The SSIS package gets the destination information from a single table. I've checked all the possible destinations that could be given by that table, they aren't there.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 24, 2013 at 9:48 am
Are the file destinations NAS or SAN? Directly connected to your server or not?
(Just trying to think of everything that could be interfering with the issue)
Is the Inbound folder on the same drive as the final destination?
May 24, 2013 at 9:52 am
Brandie Tarvin (5/24/2013)
Are the file destinations NAS or SAN? Directly connected to your server or not?(Just trying to think of everything that could be interfering with the issue)
Is the Inbound folder on the same drive as the final destination?
The destinations are a drive directly on the server.
The creation folder is on one drive on one server, the destination and archive directories are on another drive on another server.
If that's not what you're asking, let me know where I went wrong.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 24, 2013 at 10:32 am
Stefan Krzywicki (5/24/2013)
Brandie Tarvin (5/24/2013)
Are the file destinations NAS or SAN? Directly connected to your server or not?(Just trying to think of everything that could be interfering with the issue)
Is the Inbound folder on the same drive as the final destination?
The destinations are a drive directly on the server.
The creation folder is on one drive on one server, the destination and archive directories are on another drive on another server.
If that's not what you're asking, let me know where I went wrong.
No, that's what I'm asking.
I've noticed in my own environment that files "disappear" a lot faster when they are the server that is running the move process. Sometimes so fast that my screen refresh doesn't capture it. But the files would have to be fairly small for that to happen.
We also have issues with Move not working correctly (copying but not deleting, or not copying and deleting) on drives that are not directly connected to the server. I believe the problem is related to network issues. A command is taking so long to move that it never completes or the packets get interupted or something.
As I learn more about this, I'll post additional info. At this point, I think we need to engage a sniffer to find out why this isn't working.
May 24, 2013 at 10:39 am
Brandie Tarvin (5/24/2013)
Stefan Krzywicki (5/24/2013)
Brandie Tarvin (5/24/2013)
Are the file destinations NAS or SAN? Directly connected to your server or not?(Just trying to think of everything that could be interfering with the issue)
Is the Inbound folder on the same drive as the final destination?
The destinations are a drive directly on the server.
The creation folder is on one drive on one server, the destination and archive directories are on another drive on another server.
If that's not what you're asking, let me know where I went wrong.
No, that's what I'm asking.
I've noticed in my own environment that files "disappear" a lot faster when they are the server that is running the move process. Sometimes so fast that my screen refresh doesn't capture it. But the files would have to be fairly small for that to happen.
We also have issues with Move not working correctly (copying but not deleting, or not copying and deleting) on drives that are not directly connected to the server. I believe the problem is related to network issues. A command is taking so long to move that it never completes or the packets get interupted or something.
As I learn more about this, I'll post additional info. At this point, I think we need to engage a sniffer to find out why this isn't working.
I have discovered a clue. I have another step in the process that also places a file in the same directory. All the file processing steps are the same, but one. Instead of doing a Move File, it does a Rename File. I am going to try changing the Move to a Rename and see if it fixes the problem.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply