SSIS Write to object variable through script task

问题:

I have some code where i wanna end up with 2 lists. Startings and endings.

They contain start date of month and enddate of month.

These 2 lists i wanna put in an object variable so i can use the object in a foreachloop container in ssis,and loop through each row with startofmonth and endofmonthdates (variables: min and max) - But i dont know how to

Here are my codes:

String s = "2013-01-01";
         String b = "2014-01-01";

    using (SqlConnection connection = new SqlConnection("Server=localhost;Initial Catalog=LegOgSpass;Integrated Security=SSPI;Application Name=SQLNCLI11.1"))
    {
        connection.Open();
        string query = "select mindate,maxdate from dbo.dates";
        using (SqlCommand command = new SqlCommand(query, connection))
        {
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    s = reader.GetDateTime(0).ToShortDateString();
                    b = reader.GetDateTime(1).ToShortDateString();

                    //minDate.Add(reader.GetDateTime(0));
                    //maxDate.Add(reader.GetDateTime(1));
                }
            }
        }
    }

            DateTime startdate = Convert.ToDateTime(s);
            DateTime enddate = Convert.ToDateTime(b);
            DateTime parseDate;

            List<DateTime> minDate = new List<DateTime>();
            List<DateTime> maxDate = new List<DateTime>();

            List<DateTime> startings = new List<DateTime>();
            List<DateTime> endings = new List<DateTime>();


            startings.Add(startdate);
            parseDate = startdate.AddMonths(1);

            while (parseDate.Day != 1)
                parseDate = parseDate.AddDays(-1);
            parseDate = parseDate.AddDays(-1);


            endings.Add(parseDate);
            while (parseDate < enddate)
            {
                parseDate = parseDate.AddDays(1);


                startings.Add(parseDate);
                parseDate = parseDate.AddMonths(1);
                parseDate = parseDate.AddDays(-1);

               endings.Add(parseDate);

            }
            endings[endings.Count() - 1] = enddate;


            for (var x = 0; x < startings.Count; x++)
            {
                Dts.Variables["test"].Value = x;
            }


        Dts.TaskResult = (int)ScriptResults.Success;

回答1:

1. You need to create a variable that the package can use. In VS2010, you can click on the SSIS->Variables menu option to open the Variables window. Click 'Add New', and add your lists. I'll use the names minList and maxList. Their data types should be 'Object.'
2. In your script task, you could instantiate these objects as Lists. But first, you need access to them. Open your script task, and add them as ReadWriteVariables. Add checkmarks to each in the Select Variables modal dialog.

3. Now that you've added them as ReadWriteVariables, click Edit Script. Add the System.Collections.Generic namespace to use the List data type. Now, instantiate the Lists.

   Dts.Variables["User::minList"].Value = new List<DateTime>(); Dts.Variables["User::minList"].Value = new List<DateTime>();

4. You can create more manageable variable names for your variables by doing the following:

   List<DateTime> minDateList = (List<DateTime>)Dts.Variables["User::minList"].Value;

5. Finally, you could add these values to the list objects using List's Add method. I would add them inside of the loop where you are reading from reader.Read().

6. In your Foreach Loop Editor, you would then select the Foreach From Variable Enumerator, and one of your list variables.

回答2:

First create an SSIS variable "objListOfMinDates" of DataType Object. Then, when you right click your script task, on the Script Task Editor, choose that variable User::objListOfMinDates. It will be under the user variables section. Then, in the script task just create and use a local variable "localListOfMinDates". Finally at the end of the script just assign to the "objListOfMinDates" like so:

Dts.Variables["User::objListOfMinDates"].Value = localListOfMinDates;

Then you will be able to use the variable in a foreach loop later outside of the script task. Obviously, you can do this for two variables (min and max). Just create both, choose both as readWrite, and assign in the script task.