Access Object variable in Script Component in SSIS 2012

Question

I am using SSIS in VS 2012, and a lot of the answers I'm finding appear to be working for older or newer versions.

I have declared a ReadWriteVariables called User::Weather_XML (I hope this displays - my company policy doesn't allow access to imgur, so it looks like it works, but I can't tell.)

I've looked at a lot of answers: Reading object variable values in SSIS script component source - this refers to Dts.Variables, which apparently is no longer available in 2012?

So my code has this, but it won't compile because Dts.Variables doesn't exist in the current context:

    DataTable dt = new DataTable();
    OleDbDataAdapter oleda = new OleDbDataAdapter();
    oleda.Fill(dt, Dts.Variables("Weather_XML").Value);

How do I get the set of data into the Script Component so I can use it?

I have a data set, with an URL string - I want to go through the list and use each URL, get the corresponding XML, and then save that back to my object. Or write it to a database, or something. It seems like it's just the syntax, but I can't figure it out.

This uses a IDTSVariables100, but then references it to a string, and I have an entire object, with strings within it. If I add the code

    IDTSVariables100 varCollection = null;
    this.VariableDispenser.LockForRead("User::Weather_XML");

it does compile, so it's progress.

I am very weak in C#. Obviously. What is the syntax needed in 2012 to get the Object variable into something I can use in the Script Component? With, perhaps some guidance on accessing a specific part of the object. Pretend that the URL is the first string field in the object, and I currently have 4 rows.

Also, I include the following namespaces, probably way more than I need:

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Net;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Web.Script.Serialization;
using System.Xml;
using System.Xml.Serialization;
using System.Collections.Generic;
using System.Data.OleDb;

David Tansey · Accepted Answer · 2019-07-24 18:02:24Z

2

In SSIS, the Script Task and the Script Component are similar but definitely not identical.

When using the Script Component Dts.Variables is not available (although in Script Task it is available).

The following MSDN article compares Script Task and Script Component clarifying the differences between them: Comparing Script Task and Script Compnent

The relevant info from that article is as follows:

Using variables

The Script component uses typed accessor properties of the autogenerated based class, created from the component's ReadOnlyVariables and ReadWriteVariables properties. For example:

[Visual Basic]
Dim myVar as String 
myVar = Me.Variables.MyStringVariable
[C#]
string myVar; 
myVar = this.Variables.MyStringVariable;

In your case the following syntax should work:

var xml = this.Variables.Weather_XML;

The following TechNet article is another good source with examples of using Variables in the SSIS Script Component.

https://social.technet.microsoft.com/wiki/contents/articles/22198.use-ssis-variables-and-parameters-in-a-script-component.aspx

edited Jul 24, 2019 at 18:02

answered Jul 24, 2019 at 17:57

David Tansey

6,0104 gold badges39 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Hadi Over a year ago

Well explained!

thursdaysgeek Over a year ago

Why 'var xml'? It's an object, but I have to pull out a URL/string. Does this need to go in the PreExecute method? Even there, I get the error "'Variables' does not contain a definition for 'Weather_XML' and no extension method 'Weather_XML' accepting a first argument of type 'Variables' could be found (are you missing a using directive or an assembly reference?)"

David Tansey Over a year ago

I just created a new SSIS project, added a dummy DataFlow task with an Excel file as input to satisfy the designer tool and get it to allow me to add a Script Component. I then added a Package level string variable called Weather_XML (represented in GUI as User::Weather_XML, then I edited the Script Component adding Weather_XML as a ReadWriteVariable. Then I chose 'Edit Script` to get the template C# code -- I was able to reference this.Variables.Weather_XML in any of the methods in the template: PreExecute() PostExecute() CreateNewOutoutRows().

thursdaysgeek Over a year ago

Maybe I'll start a fresh project. Maybe there's so many tries in this one that it's just messed up.

thursdaysgeek Over a year ago

A fresh project, just adding that one line, and it gives an error. Am I missing a directive or assembly reference? Does this work in 2012?

|

Collectives™ on Stack Overflow

Access Object variable in Script Component in SSIS 2012

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related