2

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;
thursdaysgeek
  • 7,696
  • 20
  • 78
  • 115

1 Answers1

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

David Tansey
  • 5,813
  • 4
  • 35
  • 51
  • Well explained! – Hadi Jul 24 '19 at 18:07
  • 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?)" – thursdaysgeek Jul 24 '19 at 19:44
  • 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()`. – David Tansey Jul 25 '19 at 17:19
  • Maybe I'll start a fresh project. Maybe there's so many tries in this one that it's just messed up. – thursdaysgeek Jul 25 '19 at 17:35
  • 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? – thursdaysgeek Jul 25 '19 at 19:04
  • I did not have to add any additional assemblies or `using` statements etc. I do not have VS2012 installed -- sorry. When I choose 'Edit Script` from the Script Component properties dialog, what happens is this: long wait, then another instance of VS opens with just the C# code. I am curious if you see similar behavior? – David Tansey Jul 25 '19 at 19:40
  • Yes, that is the same. And I selected a Source Script Component, as did you, since there is a CreateNewOutputRows() method in the template. So perhaps it's the ancient version I have. – thursdaysgeek Jul 25 '19 at 19:49
  • I can access a string, using your code above. But not an object. I'm now working to modify my code so one record at a time is used, with strings, and skipping the object altogether. – thursdaysgeek Jul 29 '19 at 21:49