1

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;
    }
Tshepiso Selepe
  • 67
  • 1
  • 9
  • You probably have more than one database due to connection string connecting to a different server or mdf file. – jdweng Apr 04 '19 at 10:27
  • 1
    The default value for `Dts.TaskResult` is 0, which is success so if `Directory.CreateDirectory(newpath);` raises an exception `Dts.TaskResult` will not be set. Put a `try...catch` block round `Directory.CreateDirectory(newpath);` and see what happens. `Directory.CreateDirectory()` returns a `DirectoryInfo` object which you can also inspect. – spodger Apr 04 '19 at 10:28
  • 1
    Is the catalog on a SQL Server that is the same as your workstation? It creates that folder wherever the package is running. Which may not be your workstation. – Nick.Mc Apr 04 '19 at 11:27
  • Hi, I created a different package ,just a simple one that loads a text file and it loads successfully when i deploy it.on the same environment – Tshepiso Selepe Apr 04 '19 at 11:43
  • and is that text file in Users\Documents? it's a bad idea to use User folders for server type operations – Nick.Mc Apr 07 '19 at 22:07

0 Answers0