Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-06-25
6
Medium Priority
?
3,934 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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.
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.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

636 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