Home SSIS Archiving a file
Reply: 1

SSIS Archiving a file

sql2015
1#
sql2015 Published in 2017-12-05 09:51:11Z

I have an SSIS package that outputs a csv file what I want to do is look in the destination folder before I generate a csv and if one already exists (I produce them on a daily basis ) then move it to an archive folder.

my file name structure is "NAME_YYMMDD_HHMM.csv" I'm not sure how I go about looking for a file as the date and time will always be different. i've created a variable for the filename "NAME_*_.csv but not sure how id add this to a file system task? if a file doesn't exist id like it to just move on to next step in package if it does exist then move file to archive folder

plaidDK
2#
plaidDK Reply to 2017-12-05 13:55:10Z

You should use a script task to do this:

Use a variable in string file instead which holds your values from YYMMDD since HHMM is unable to get when you created it run time.

Date formats can be found here

Use namespace using System.IO;

        string filepath = @"D:\new";

        string file = "test_171205_1010.txt";

        string fullpath = filepath + @"\" + file;

        string newpath = @"D:\arch\";
        string newfullpath = newpath + file;

        if(
            File.Exists(fullpath))
        {
            File.Move(fullpath, newfullpath);
        }

Update

And if you wanna use something about todays date, you can do like below where i look for all files of today and move them. Its up to your what the logic should be.

        string txtFile = null;
        string txtMoveFile = null;

        string date = DateTime.Now.ToString("yyMMdd");
        string filepath = @"D:\new"; 

        /*USE THIS LINE IF YOU WANNA USE VARIABLE FROM FOREACH LOOP CONTAINER*/
        string file = Dts.Variables["User::name"].Value.ToString() +"_*_*.txt";

        //string file = "test_"+date+"_*.txt";

        string[] allfilesfromcurrentdate = Directory.GetFiles(filepath, file);
        string fullpath = filepath + @"\" + file;

        string newpath = @"D:\arch\";
        string newfullpath = newpath + file;

        foreach(string fileName in allfilesfromcurrentdate)
        {
            if(fileName.Contains("test_"+date))
            {
                txtFile = fileName;
                txtMoveFile = txtFile.Replace("new", "arch");
               if(File.Exists(txtFile))
                {
                    File.Move(txtFile, txtMoveFile);
                }

            }
        }
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.317197 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO