asked on

How do Copy, move, and rename a file in SSIS

Good Morning,

I have a ssis package that I have created that basically moves data from a csv to a staging table then to a production table. I want to automate the process.

The file is downloaded to a directory

the file name is : GCPeclass_Course Report_2015_Semester_2014-10-12.csv and it is located \\ms01\courseinfo
the file name changes depending on the date. ex.

eclass_Course Report_2015_Semester_2014-10-12.csv
eclass_Course Report_2015_Semester_2014_10_7.csv
eclass_Course Report_2015_Semester_2014-9-21.csv

when I get this file I want to

copy the file

Move the original file to a folder called archive -\\ms01\courseinfo\archive

rename GCPeclass_Course Report_2015_Semester_2014-10-12 - Copy.csv
to GCPeclass_Course Report

Jim Horn

For starters, in the Control Flow add a File System Object (FSO) component, then double-click to edit the properties..

Butterfly2

ASKER

Hi Jim,

I have gotten that far already. I need assistance because the name changes(basically the last part of the file name is a date).

Christopher Gordon

@Butterfly2

I don't think you'll be able to accomplish this with the File System Object (if u figure it out I'm interested in how you pulled it off).

I usually end up using a "Script Task" and C# System.IO.File in order to pull this off. It is additional coding though.

Maybe do something like this in your "script task":

 private static bool MoveAndOverWrite(string sSource, string sDestn)
        {
            try
            {
                if (System.IO.File.Exists(sSource) == true)
                {
                    System.IO.File.Copy(sSource, sDestn, true);

                    System.IO.File.Delete(sSource);

                    return true;                   
                }
                else
                {
                    Console.WriteLine("Specifed file does not exist");

                    return true;
                }
            }
            catch (System.IO.FileNotFoundException exFile)
            {
                Console.WriteLine("File Not Found " + exFile.Message);

                return false;
            }
            catch (System.IO.DirectoryNotFoundException exDir)
            {
                Console.WriteLine("Directory Not Found " + exDir.Message);

                return false;
            }

            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);

                return false;
            }
        }

Open in new window

If you decide to go this route and need more direction, plz post a follow up question. Thx

Butterfly2

ASKER

Hi Chris,

I am totally willing to try this way, but I am not sure exactly where your code goes. Does it go under expression? If so what property do I choose?

Butterfly2

ASKER

never mind i see where to put the code

ASKER CERTIFIED SOLUTION

Christopher Gordon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial