• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4441
  • Last Modified:

How to move a file with variable name to a destination folder in SSIS

Simple objective: move an input file (with variable filename) to an archive directory.

I can read the file with variable file_name, based on current YYYYMMDD. then I insert to a table, that works.

But I can't seem to move the file to a destination directory.

The error I get is "The connection "H:\FTP\SBI\Archive\Magellan_20130626.txt" is not found."

I've attached the package XML and input file, maybe you could see what's going on.
Package-1-4.txt
Magellan-20130626.txt
0
Alaska Cowboy
Asked:
Alaska Cowboy
  • 4
  • 2
2 Solutions
 
ValentinoVBI ConsultantCommented:
In the File System Task you should set IsDestinationPathVariable to True, then use the Dest_File package variable in the DestinationVariable property.
0
 
Alaska CowboyAuthor Commented:
Great, thank you. Got closer but still something is still wrong. I did as you said but still got error:

Error: 0xC002F304 at File System Task, File System Task: An error occurred with the following error message: "Could not find a part of the path.".


SSIS package "Package1 4.dtsx" starting.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Warning: 0x80047076 at Data Flow Task, SSIS.Pipeline: The output column "Copy of Column 0" (156) on output "Data Conversion Output" (151) and component "Data Conversion" (149) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Warning: 0x80047076 at Data Flow Task, SSIS.Pipeline: The output column "Copy of Column 1" (159) on output "Data Conversion Output" (151) and component "Data Conversion" (149) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Warning: 0x80047076 at Data Flow Task, SSIS.Pipeline: The output column "Copy of Derived Column 2" (162) on output "Data Conversion Output" (151) and component "Data Conversion" (149) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Warning: 0x80047076 at Data Flow Task, SSIS.Pipeline: The output column "Copy of Derived Column 1" (165) on output "Data Conversion Output" (151) and component "Data Conversion" (149) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Information: 0x40043006 at Data Flow Task, SSIS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, SSIS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Data Flow Task, Flat File Source [1]: The processing of file "H:\FTP\SBI\Magellan_20130626.txt" has started.
Information: 0x4004300C at Data Flow Task, SSIS.Pipeline: Execute phase is beginning.
Information: 0x402090DE at Data Flow Task, Flat File Source [1]: The total number of data rows processed for file "H:\FTP\SBI\Magellan_20130626.txt" is 1.
Information: 0x40043008 at Data Flow Task, SSIS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Data Flow Task, Flat File Source [1]: The processing of file "H:\FTP\SBI\Magellan_20130626.txt" has ended.
Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "component "OLE DB Destination" (22)" wrote 1 rows.
Information: 0x40043009 at Data Flow Task, SSIS.Pipeline: Cleanup phase is beginning.
Error: 0xC002F304 at File System Task, File System Task: An error occurred with the following error message: "Could not find a part of the path.".
Warning: 0x80019002 at File System Task: 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.
Task failed: File System Task
Warning: 0x80019002 at Package1: 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 "Package1 4.dtsx" finished: Failure.

Open in new window

SSIS-code.txt
0
 
Alaska CowboyAuthor Commented:
ahhh, still can't figure out what's going on, it seems set up ok but the final step (to move the file) is not working . . . The script task seems to work ok, see here:
input file, output file variablesIn my file system task to move the file, that seems set up as you suggested:
file system task
When running the package, I get this error:
Error: 0xC002F304 at File System Task, File System Task: An error occurred with the following error message: "Could not find a part of the path.".
Package-1-4.txt
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Alaska CowboyAuthor Commented:
got it !!  

I googled "Could not find a part of the path", found this was a bug, that I need to copy the file, then delete it, and it worked, yea.
0
 
ValentinoVBI ConsultantCommented:
Cool, glad to hear you got that resolved!

Another option that you had was to only specify the destination folder, without filename.  That should work too.
0
 
Alaska CowboyAuthor Commented:
found a bug that prevented moving the file, I had to copy it first, then delete it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now