SSIS package working in SSDT and not working in SQL server after I deploy it

140 Views Asked by At

I have an SSIS package that creates a next day folder based on the previous day folder in a directory, when i run the package directly from SSDT it generates the folder,however when i deploy the package to Sql server intergration catalogue and excute,i get a success message but the folder is not created.

variables on the script task enter image description here

variable declaration enter image description here enter image description here

Please see the script task code below:

   public void Main()
    {
        // TODO: Add your code here
        string DataLocation = Dts.Variables["User::FolderLocation"].Value.ToString();

        string[] Folders = Directory.GetDirectories(DataLocation);


        List<String> listFolders = new List<String>();
        List<String> listFoldersonly = new List<String>();
        List<int> lens = new List<int>();
        List<String> dates = new List<String>();
        List<DateTime> dd = new List<DateTime>();

        //get list of folders in the Directory
        if (Folders.Length > 0)
        {

            for (int x = 0; x < Folders.Length; x++)
            {

                listFolders.Add(Folders[x].ToString());
                lens.Add(Folders[x].Length);


            }
        }
        //store list of folders in an array
        string[] arrayFolders = listFolders.ToArray();
        int[] arrayLens = lens.ToArray();


        DateTime minDate = DateTime.MaxValue;
        DateTime maxDate = DateTime.MinValue;
        DateTime nextdate;

        for (int i = 0; i < arrayFolders.Length; i++)
        {
            //subtring the date from the folderlocation string
            dates.Add(arrayFolders[i].ToString().Substring(arrayLens[i] - 10));

            dd.Add(DateTime.Parse(arrayFolders[i].ToString().Substring(arrayLens[i] - 10)));

            //get the max and min date
            if (dd[i].Date < minDate)
                minDate = dd[i];
            if (dd[i] > maxDate)
                maxDate = dd[i];

        }

        nextdate = maxDate.AddDays(1);
        string nxtdate = nextdate.ToString("yyyy-MM-dd");

        String newpath = DataLocation + "\\" + nxtdate;


        Dts.Variables["User::CopyFolder"].Value = newpath.ToString();
        Dts.Variables["User::ReturnDate"].Value = nextdate.ToString();


        Directory.CreateDirectory(newpath);

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

There are 0 best solutions below