SSIS Write to object variable through script task

Question

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;

Solution

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.

Comments

[SqlKindaGuy]

But i have 2 lists with data in? I need both data values?

[BBauer42]

Then use a list of objects like in this post: stackoverflow.com/questions/5069282/…

[SqlKindaGuy]

i am not very good at C#, but the two dimension list looks like one approach that would be right. But how would i write it if we go out from my example? i how should i make the list right and assign the 2 values to the list?

[SqlKindaGuy]

how do i add the values you say? I can only loop throug one variable, what do i do what the other variable? I need both values.

[SqlKindaGuy]

if i use step number 4 List<DateTime> minDateList = (List<DateTime>)Dts.Variables["User::minlist"].Value; - i get this error: Unable to cast object of type 'System.Object' to type 'System.Collections.Generic.List`1[System.DateTime]'.

[SqlKindaGuy]

i fixed this error, is was my bad! But how do we get the values from both list to my loop so i can assign both values to variables in the foeachloop?

[sorrell]

Sorry, step number 4 contained an error. I erroneously had a List<string> in there.

[sorrell]

Look into multidimensional objects. See stackoverflow.com/questions/1596530/…

[SqlKindaGuy]

sooo create the object lists into one? or? That looks complicated, Im sorry, my C# skills are not that good!

[sorrell]

Well, can you use two separate Foreach Loop Containers then?

[SqlKindaGuy]

No, that will make the whole idea fall to the ground. The reason why i haave two values in two list is because i need to loop over a set og range data where i have mindate and maxdate values paramatized into a sql select statement. so i need both values in one loop

[sorrell]

Then maybe take a look at this (use a datatable): sqlblog.com/blogs/andy_leonard/archive/2007/10/14/… or this timmitchell.net/post/2013/05/28/…

[SqlKindaGuy]

the last link is though an sql task - this is not possible for me, due to the coding is really comples - i want to learn how to do it by using script task - and andy leonard tells how to read an object not how to write to an object

[sorrell]

stackoverflow.com/questions/13870843/…

[SqlKindaGuy]

and how would u use that in my case?

[sorrell]

You would fill the DataTable in your Script Task, and iterate in a Foreach Loop Container as the Tim Mitchell link describes.

[SqlKindaGuy]

but thats from an sqldatareader - this is not sql. I think your on the wrong track now. but thanks for the effort.

[sorrell]

Um, line 10 of your code shows you using a SqlDataReader. using (SqlDataReader reader = command.ExecuteReader()) , but good luck, and you're welcome for the effort.

[SqlKindaGuy]

yes for reading two dates - thats the only thing, they could be hardcoded as well:)

[sorrell]

No... just because your code only stores the last read s and b doesn't mean you are only reading two dates. Your SqlDataReader reader holds the full result set from your query. reader.read() iterates the full result set because it is in a while loop. I'm starting to feel like this is a code review and would be glad to take this offline, but your reputation of 1 doesn't allow us to use the SO chat feature :/

[SqlKindaGuy]

Again no - there is only 1 row in my sql data table. IT is again only used to read a min and maxdate so i can do my while loop to create month date ranges. My problem is that i need to store these value to my object variables in ssis. And yes your right this can be done by using datatable but i dont know how.