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;

sorrell · Accepted Answer · 2015-01-28T13:40:23.410

13

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.'
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.
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>();
You can create more manageable variable names for your variables by doing the following:

List<DateTime> minDateList = (List<DateTime>)Dts.Variables["User::minList"].Value;
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().
In your Foreach Loop Editor, you would then select the Foreach From Variable Enumerator, and one of your list variables.

edited Jan 28 '15 at 13:40

answered Jan 28 '15 at 13:20

sorrell

1,801
1
16
27

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 Jan 28 '15 at 13:31
if i use step number 4 List minDateList = (List)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 Jan 28 '15 at 13:34
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? – SqlKindaGuy Jan 28 '15 at 13:42
Sorry, step number 4 contained an error. I erroneously had a List in there. – sorrell Jan 28 '15 at 13:44
Look into multidimensional objects. See http://stackoverflow.com/questions/1596530/multi-dimensional-arraylist-or-list-in-c – sorrell Jan 28 '15 at 13:46
sooo create the object lists into one? or? That looks complicated, Im sorry, my C# skills are not that good! – SqlKindaGuy Jan 28 '15 at 13:52
Well, can you use two separate Foreach Loop Containers then? – sorrell Jan 28 '15 at 14:01
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 – SqlKindaGuy Jan 28 '15 at 14:03
Then maybe take a look at this (use a datatable): http://sqlblog.com/blogs/andy_leonard/archive/2007/10/14/ssis-design-pattern-read-a-dataset-from-variable-in-a-script-task.aspx or this http://www.timmitchell.net/post/2013/05/28/using-the-ssis-object-variable-as-a-result-set-enumerator/ – sorrell Jan 28 '15 at 14:11
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 – SqlKindaGuy Jan 28 '15 at 14:19
http://stackoverflow.com/questions/13870843/turning-a-sqlcommand-with-parameters-into-a-datatable – sorrell Jan 28 '15 at 14:23
and how would u use that in my case? – SqlKindaGuy Jan 28 '15 at 14:31
You would fill the DataTable in your Script Task, and iterate in a Foreach Loop Container as the Tim Mitchell link describes. – sorrell Jan 28 '15 at 15:25
but thats from an sqldatareader - this is not sql. I think your on the wrong track now. but thanks for the effort. – SqlKindaGuy Jan 29 '15 at 08:38
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. – sorrell Jan 29 '15 at 12:27
yes for reading two dates - thats the only thing, they could be hardcoded as well:) – SqlKindaGuy Jan 29 '15 at 13:14
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 :/ – sorrell Jan 29 '15 at 14:41
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. – SqlKindaGuy Jan 29 '15 at 14:54

score 0 · Answer 2 · answered Jan 28 '15 at 13:02

0

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.

answered Jan 28 '15 at 13:02

BBauer42

3,549
10
44
81

But i have 2 lists with data in? I need both data values? – SqlKindaGuy Jan 28 '15 at 13:04
Then use a list of objects like in this post: http://stackoverflow.com/questions/5069282/ssis-foreach-with-a-list-of-simple-objects – BBauer42 Jan 28 '15 at 13:06
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 Jan 28 '15 at 13:11

SSIS Write to object variable through script task

2 Answers2

Linked