CODEWITHSUNDEEP
c#ssis
jhowe
jhowe

Reputation: 10828

SSIS display variable values in script task

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.

Upvotes: 2

Views: 62055

Answers (4)

CalumH
CalumH

Reputation: 21

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.

Upvotes: 2

Fernando
Fernando

Reputation: 646

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.

Upvotes: 3

user3010305
user3010305

Reputation: 131

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

Upvotes: 9

makciook
makciook

Reputation: 1555

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()

Upvotes: 3

Related Questions