SSIS - specified path, file name, or both are too long
-
Hi All,
I have built a package which:
1. searches for flat files through a Folder structure on the test server
2. moves the files to an active folder
3. Then to a processing folder - at this stage all transfomations and load to the DB table takes place
4. Then once this is complete the files are moved to an archive folder
Issue: I need to save the folder name of the source data in the database table but get the error below .. it fails at the first 'File SYstem Task'
Error Message:
[File System Task] Error: An error occurred with the following error message: "The specified path, file name, or both are too long. The fully qualified file name must be less than 260 characters, and the directory name must be less than 248 characters.".
In the For Each Loop container if I use the 'Name and Extension' option only the package runs fine. But to get the folder name I need to use 'Fully QUalified' but this is when the error message is generated.
I realise this is because the full file path is quite long.
Is there a way around this in SSIS.
Any help or suggestions would be appreciated - .
-
Not really enough information to provide a good answer, so how about a shot in the dark about what the problem could be.
From the looks of it, you may be searching a directory structure that is muliple levels deep and the directory/filenames may be fairly long, especially when combined. You may need to look at flattening your directory structure or targetting deeper into the structure to find files.
Without more information it is difficult to provide you with a better answer.
-
aarionsql (6/11/2012)
Is there a way around this in SSIS.You could code it in .NET. It usually is a bit more flexible and robust than a For Each Loop with a FileSystem Task.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Apologies .. I tried to upload a document which show the structure of the package but for some reason I cannot load an attachment from work .. there are a few levels
I am not good at coding .NET ... I would not know where to begin.
I have been using variable to map the folder paths .. I am testing out the package using shorter path file lengths .. I'll see if the problem still exists or if I am going wrong somewhere with a variable.
Thank you for your sugestions though ..
-
aarionsql (6/11/2012)
Hi All,I have built a package which:
1. searches for flat files through a Folder structure on the test server
2. moves the files to an active folder
3. Then to a processing folder - at this stage all transfomations and load to the DB table takes place
4. Then once this is complete the files are moved to an archive folder
Issue: I need to save the folder name of the source data in the database table but get the error below .. it fails at the first 'File SYstem Task'
Error Message:
[File System Task] Error: An error occurred with the following error message: "The specified path, file name, or both are too long. The fully qualified file name must be less than 260 characters, and the directory name must be less than 248 characters.".
I don't know if you finally sorted out this error; based on your package details listed above, it could have more than one File System Task, you did not identify the task returning the error, did it happens at 2, 3 or 4 (I am guessing here, because each one of these tasks seem to be moving files between folders); anyway, I will suggest.
1. A package level variable containing the name of the archive folder; something like @[User::archiveFolder], the folder name ending with \ (back slash)
2. A package level variable containing the name of the processing folder; something like @[User::processFolder], the folder name ending with \ (back slash)
3. Configure Your Foreach Loop Task to return 'Name and Extension', this way your Foreach loop variable contains the file name with its extention; let's assume the Foreach variable is @[User::fileName] (this is a package level variable as well).
4. Add another package level variable, @[User::archiveFile], set its expression to: @[User::archiveFolder] + @[User::fileName]
5. Add another package level variable, @[User::processFile], set its expression to: @[User::processFolder] + @[User::fileName]
6. Your file system task copying to the archive folder will use the variable @[User::archiveFile] as the destination variable
7. Your file system task copying to the process folder will use the variable @[User::processFile] as the destination variable.
NOTE
It seems 3 folders are involved in your solution, you identified the archive and process folders without mentioning the files initial location, it is hard to give better advise when the information supplied is not enough.
Cheers,
Hope this helps,
Rock from VbCity -
Thank you all for your replies.
I need the file parts from the source data folders.I have attached a document with some diagrams of the folder structure.
If you have any suggestion on how i can do this I would be very grateful.
Thank you
-
Well, I don't like the folder structure you are implementing, but I am sure it was agreed to be done that way 🙂
This is a proposed solution, I tested it as much as I could in my environment; the attached pictures support the following explanation.
Overview
1. Script Task takes the source file name.
2. The source file name is in the variable @[User::FileName]
3. The source file name contains something like:
Data\SystemA\FileType1\FilaA.csv
Data\SystemB\FileType2\FileB.csv
Data\SystemC\FileType3\FileA.csv
4. The Script Task split the source file name in its parts, e.g. FileType and FileSystem, as well as the file name without its extention.
5. The following package level variables were introduced
User::TheFile
User::TheFileType
User::TheFileSystem
User::ProcessedFolder
6. The script task returns the following variables
User::TheFile
User::TheFileType
User::TheFileSystem
7. The script task creates the folders (when required):
Processed\FileA
Processed\FileA\Active
Processed\FileA\Processed
Processed\FileA\Archive
8. This script task should go inside the ForEach Loop Task, probably the first task inside the loop.
These are the supporting images, plus, I am attaching the C# script used by the Script Task (as a txt file), just copy the script (whole) and paste it in your script task (replacing everything in it)
The source files
The Processed folder structure
The Script Task parameters
WARNING
The variable [User::FileName] is the one in use by the ForEach Loop task, you could be using a different variable, if you choose to use your s ensure you pass it to the ScriptTask as read only, and replace the corresponding reference inside the script code.
The same warning applies to the variable [User::ProcessedFolder], I do not know your variable name, if you replace it, do so, in the task script parameters and inside its code as shown in the image below.
Cheers,
Hope this helps,
Rock from VbCity -
the missing image.
Hope this helps,
Rock from VbCity -
Thank you so much ... I will try this out this weekend.
I usually try to avoid the script task and component due to lack of confidence but your very detailed example will help out immensely .. Thank you very much for taking the time out to do this.
I have in the mean time nearly completed the package with variables ... Once that is complete tomorrow or the day after I will try your script as I feel your method will be more efficient
-
This was removed by the editor as SPAM
-
Thank you for that I will check it out.
I have come up with a solution using expressions to rename and move the file using a File System task.
I will provide a detailed example. I don't have access to a windows machine at home and it is slowing documenting this process.
-
This was removed by the editor as SPAM
-
This was removed by the editor as SPAM
-
This was removed by the editor as SPAM
-
This was removed by the editor as SPAM
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply