CODEWITHSUNDEEP
c#ssisssis-2012
thursdaysgeek
thursdaysgeek

Reputation: 7946

Access Object variable in Script Component in SSIS 2012

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 Script Transformation Editor (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;

Upvotes: 2

Views: 1518

Answers (1)

David Tansey
David Tansey

Reputation: 6023

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

Upvotes: 2

Related Questions