Solved

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

Posted on 2013-06-25
6
3,290 Views
Last Modified: 2016-02-11
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
Comment
Question by:Alaska Cowboy
  • 4
  • 2
6 Comments
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 39277093
In the File System Task you should set IsDestinationPathVariable to True, then use the Dest_File package variable in the DestinationVariable property.
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 39277635
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
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 39277963
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Assisted Solution

by:Alaska Cowboy
Alaska Cowboy earned 0 total points
ID: 39277995
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
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39278906
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
 
LVL 1

Author Closing Comment

by:Alaska Cowboy
ID: 39289375
found a bug that prevented moving the file, I had to copy it first, then delete it.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now