?
Solved

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

Posted on 2013-06-25
6
Medium Priority
?
3,784 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 37

Accepted Solution

by:
ValentinoV earned 1500 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore 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.
I have a large data set and a SSIS package. How can I load this file in multi threading?
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…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

765 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