SQLTeam.com | Weblogs | Forums

Copying and moving files ssis

hi. i have a package where i need to copy and move files to archive. ithe package reads in 10 files 9 of them have to move to archive and 1 has to stay in the folder and and copy a file to archive just encase. when i run the move logic is fails saying [File System Task] Error: Variable "FileName" is used as a source or destination and is empty. its failing every time i hit the 1 file in the folder i dont want to move. is there a way around this. my move logic is


 public void Main()
        {
          
            // Uncomment to view status in messaage box while debugging
            if (Equals("CurrentlyProcessing.arc", Dts.Variables["User::CurrentFile"].Value) == false)
            {
                //System.Windows.Forms.MessageBox.Show(Convert.ToString(Dts.Variables["User::CurrentFile"].Value));
                Dts.Variables["User::FileName"].Value = "";
                string filename = (string)Dts.Variables["User::CurrentFile"].Value;
                FileInfo fileinfo;
                fileinfo = new FileInfo(filename);


                //file's behavior
                string FileBehaviorBloom = (string)Dts.Variables["User::FileBehaviorBloom"].Value;
                string FileBehaviorCounterParty = (string)Dts.Variables["User::FileBehaviorCounterParty"].Value;
                string FileBehaviorGSL113 = (string)Dts.Variables["User::FileBehaviorGSL113"].Value;
                string FileBehaviorGSL200 = (string)Dts.Variables["User::FileBehaviorGSL200"].Value;
                string FileBehaviorHoldingsTrigger = (string)Dts.Variables["User::FileBehaviorHoldingsTrigger"].Value;
                string FileBehaviorInbound = (string)Dts.Variables["User::FileBehaviorInbound"].Value;
                string FileBehaviorLookup = (string)Dts.Variables["User::FileBehaviorLookup"].Value;
                string FileBehaviorNaming = (string)Dts.Variables["User::FileBehaviorNaming"].Value;
                string FileBehaviorSecurity = (string)Dts.Variables["User::FileBehaviorSecurity"].Value;
                string FileBehaviorSleeve = (string)Dts.Variables["User::FileBehaviorSleeve"].Value;
                string FileBehaviorSplitter = (string)Dts.Variables["User::FileBehaviorSplitter"].Value;
                string FileBehaviorTopTran = (string)Dts.Variables["User::FileBehaviorTopTran"].Value;
                string FileBehaviorXRateHol = (string)Dts.Variables["User::FileBehaviorXRateHol"].Value;
                string FileBehaviorSOI = (string)Dts.Variables["User::FileBehaviorSOI"].Value;

                //file's name keywords

                string KeywordBloom = (string)Dts.Variables["User::KeywordBloom"].Value;
                string KeywordCounterParty = (string)Dts.Variables["User::KeywordCounterParty"].Value;
                string KeywordGSL113 = (string)Dts.Variables["User::KeywordGSL113"].Value;
                string KeywordGSL200 = (string)Dts.Variables["User::KeywordGSL200"].Value;
                string KeywordHoldingsTrigger = (string)Dts.Variables["User::KeywordHoldingsTrigger"].Value;
                string KeywordInbound = (string)Dts.Variables["User::KeywordInbound"].Value;
                string KeywordLookup = (string)Dts.Variables["User::KeywordLookup"].Value;
                string KeywordNaming = (string)Dts.Variables["User::KeywordNaming"].Value;
                string KeywordSecurity = (string)Dts.Variables["User::KeywordSecurity"].Value;
                string KeywordSleeve = (string)Dts.Variables["User::KeywordSleeve"].Value;
                string KeywordSplitter = (string)Dts.Variables["User::KeywordSplitter"].Value;
                string KeywordTopTran = (string)Dts.Variables["User::KeywordTopTran"].Value;
                string KeywordXRateHol = (string)Dts.Variables["User::KeywordXRateHol"].Value;
                string KeywordSOI = (string)Dts.Variables["User::KeywordSOI"].Value;


                if (filename.ToUpper().Contains(KeywordBloom.ToUpper()) && Equals("MOVE", FileBehaviorBloom.ToUpper()) == true)
                {
                    Dts.Variables["User::FileName"].Value = Dts.Variables["User::CurrentFile"].Value;
                }

                else if (filename.ToUpper().Contains(KeywordCounterParty.ToUpper()) && Equals("MOVE", FileBehaviorCounterParty.ToUpper()) == true)
                {
                    Dts.Variables["User::FileName"].Value = Dts.Variables["User::CurrentFile"].Value;
                }
                else if (filename.ToUpper().Contains(KeywordGSL113.ToUpper()) && Equals("MOVE", FileBehaviorGSL113.ToUpper()) == true)
                {
                    Dts.Variables["User::FileName"].Value = Dts.Variables["User::CurrentFile"].Value;
                }
                else if (filename.ToUpper().Contains(KeywordGSL200.ToUpper()) && Equals("MOVE", FileBehaviorGSL200.ToUpper()) == true)
                {
                    Dts.Variables["User::FileName"].Value = Dts.Variables["User::CurrentFile"].Value;
                }
                else if (filename.ToUpper().Contains(KeywordHoldingsTrigger.ToUpper()) && Equals("MOVE", FileBehaviorHoldingsTrigger.ToUpper()) == true)
                {
                    Dts.Variables["User::FileName"].Value = Dts.Variables["User::CurrentFile"].Value;
                }
                else if (filename.ToUpper().Contains(KeywordInbound.ToUpper()) && Equals("MOVE", FileBehaviorInbound.ToUpper()) == true)
                {
                    Dts.Variables["User::FileName"].Value = Dts.Variables["User::CurrentFile"].Value;
                }
                else if (filename.ToUpper().Contains(KeywordLookup.ToUpper()) && Equals("MOVE", FileBehaviorLookup.ToUpper()) == true)
                {
                    Dts.Variables["User::FileName"].Value = Dts.Variables["User::CurrentFile"].Value;
                }
                else if (filename.ToUpper().Contains(KeywordNaming.ToUpper()) && Equals("MOVE", FileBehaviorNaming.ToUpper()) == true)
                {
                    Dts.Variables["User::FileName"].Value = Dts.Variables["User::CurrentFile"].Value;
                }
                else if (filename.ToUpper().Contains(KeywordSecurity.ToUpper()) && Equals("MOVE", FileBehaviorSecurity.ToUpper()) == true)
                {
                    Dts.Variables["User::FileName"].Value = Dts.Variables["User::CurrentFile"].Value;
                }
                else if (filename.ToUpper().Contains(KeywordSleeve.ToUpper()) && Equals("MOVE", FileBehaviorSleeve.ToUpper()) == true)
                {
                    Dts.Variables["User::FileName"].Value = Dts.Variables["User::CurrentFile"].Value;
                }

                else if (filename.ToUpper().Contains(KeywordSplitter.ToUpper()) && Equals("MOVE", FileBehaviorSplitter.ToUpper()) == true)
                {
                    Dts.Variables["User::FileName"].Value = Dts.Variables["User::CurrentFile"].Value;
                }
                else if (filename.ToUpper().Contains(KeywordTopTran.ToUpper()) && Equals("MOVE", FileBehaviorTopTran.ToUpper()) == true)
                {
                    Dts.Variables["User::FileName"].Value = Dts.Variables["User::CurrentFile"].Value;
                }
                else if (filename.ToUpper().Contains(KeywordXRateHol.ToUpper()) && Equals("MOVE", FileBehaviorXRateHol.ToUpper()) == true)
                {
                    Dts.Variables["User::FileName"].Value = Dts.Variables["User::CurrentFile"].Value;
                }
                else if (filename.ToUpper().Contains(KeywordSOI.ToUpper()) && Equals("MOVE", FileBehaviorSOI.ToUpper()) == true)
                {
                    Dts.Variables["User::FileName"].Value = Dts.Variables["User::CurrentFile"].Value;
                }


                else if (filename.ToUpper().Contains(KeywordSOI.ToUpper()) && Equals("COPY", FileBehaviorSOI.ToUpper()) == true)
                {
                    Dts.Variables["User::FileName"].Value = Dts.Variables["User::CurrentFile"].Value;
                }

                Dts.TaskResult = (int)ScriptResults.Success;
            }
        }
    }
}

any help would be great

Here is a link ... with some solutions out of which one maybe yours

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0ca6d5af-3aa5-499a-8893-ef1aeada6913/variable-is-used-as-a-source-or-destination-and-is-empty?forum=sqlintegrationservices

please google search .. lots of articles .. can help you

thanks for the awesome information.