SSIS display variable values in script task

Question

I'm doing some testing, outputting a full result set from a Execute SQL Task into a Foreach loop as below

enter image description here

and I just want to output my variable values to a message box, however it doesn't seem to work.

public void Main()
    {

        try
        {

            // TODO: Add your code here
            string Variables = Dts.Variables["User::ClientID"].ToString() +
            Dts.Variables["User::Passphrase"].ToString() +
            Dts.Variables["User::KeyFileName"].ToString() +
            Dts.Variables["User::InboundEncryptionRequired"].ToString() +
            Dts.Variables["User::SftpResponseRequired"].ToString() +
            Dts.Variables["User::OutboundDecryptionRequired"].ToString() +
            Dts.Variables["User::SftpHost"].ToString() +
            Dts.Variables["User::SftpPort"].ToString() +
            Dts.Variables["User::SftpUserName"].ToString() +
            Dts.Variables["User::Active"].ToString() +
            Dts.Variables["User::SftpDownloadFrom"].ToString() +
            Dts.Variables["User::SftpUploadTo"].ToString() +
            Dts.Variables["User::SftpDeleteFilesFromRemote"].ToString() +
            Dts.Variables["User::ConnectionProtocol"].ToString();

            MessageBox.Show(Variables);

        }



        catch (Exception Ex)
        {

            MessageBox.Show(Ex.Message);

        }
    }
}

}

I'm just getting "element cannot be found in a collection" or something, even though I know the query is outputting 2 rows.

I have also mapped my variables on the foreach loop and specified read only variables on scrip task.

* UPDATE *

This is driving me nuts. I've triple checked my variable names, can confirm I'm getting full result set.

enter image description here

I removed the User:: from my script task variables, but still no luck.

sorrell · Accepted Answer · 2014-07-24 19:21:31Z

9

You have to pass in the variables you are using. Double-click the Script Task and in the "Script Task Editor" modal window, enter the variables you want to display in "ReadOnlyVariables" or "ReadWriteVariables". Then you'll be able to reference them in your code.

script task editor

edited Jul 24, 2014 at 19:21

sorrell

1,8712 gold badges16 silver badges30 bronze badges

answered Nov 19, 2013 at 19:21

user3010305

1311 silver badge6 bronze badges

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

Comments

User1000547 · Accepted Answer · 2014-08-19 19:14:21Z

3

It sounds as if one of your variable names is not spelled correctly and it cannot be found in the array.

Also you could use

string Variables = String.Format("{0},{1},...",Dts.Variables["User::ClientID"],Dts.Variables["User::Passphrase"],...)

to display the values or set your variables string.

edited Aug 19, 2014 at 19:14

User1000547

4,3219 gold badges44 silver badges68 bronze badges

answered Aug 1, 2013 at 10:10

Fernando

6461 gold badge11 silver badges21 bronze badges

3 Comments

jhowe Over a year ago

sorry i don't really understand what you mean by using string.format, what is the 0,1 etc. can you post a small working example?

makciook Over a year ago

he means using string.format to merge vars into string instead of simple concatenation

Nigel Ainscoe Over a year ago

E.g. var archiveFile = string.Format("{0}, {1}", Dts.Variables["User::ExpPathArchiveFolder"].Value, Dts.Variables["User::ExpCurrentFileName"].Value); If you switched the 0 and 1 around then the strings would be concatenated in the switched order.

makciook · Accepted Answer · 2013-08-01 12:00:53Z

3

You don't have to specify the scope: User or System. Remove User:: from each name. If it still doesn't work - you must have misspelled one of the names.

Also you need to get the value and convert it to string. Correct format is: Dts.Variables["Passphrase"].Value.ToString()

answered Aug 1, 2013 at 12:00

makciook

1,56310 silver badges19 bronze badges

Comments

CalumH · Accepted Answer · 2015-02-16 15:55:49Z

2

I just stumbled upon this after trying to solve a similar problem. The first issue I found with your code is that you don't have

.value

before the .ToString in the string you are concatenating. I changed this on mine and it worked.

edited Feb 16, 2015 at 15:55

answered Feb 5, 2015 at 13:05

CalumH

214 bronze badges

1 Comment

J Richard Snape Over a year ago

Welcome to Stack overflow! This answer came to me to review as it is your first post. With regard to your comment about the try..catch - it probably is not required, but you can test it by adding the try and catch around your code and seeing whether it still works. If it does - I suggest removing the bit about the try...catch as not relevant to the answer.

Collectives™ on Stack Overflow

SSIS display variable values in script task

4 Answers 4

Comments

3 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related