.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

How to get the Package level Variable collections (Global Variable) in Script Task

Posted By:      Posted Date: September 29, 2010    Points: 0   Category :Sql Server

Hi All,

I have the requirement like read all the Variables in Script Component Task, which includes variables available at package level and their values, global variables declared at package.


Currently are two ways to read the variables but which are not suitable for my requirement


1) Mentioning the variables at Script designer window as ReadOnlyVariables or ReadWriteVariables



View Complete Post

More Related Resource Links

how to store the values in a object variable (package level scope) in a script component

I have declared a variable of object type with package level scope.  I want to assign values to this variable inside a script component.  My object should hold 5 columns in a row.  How to implement this ?

Cant see package level variable in script component

I am currently having a issue accessing a package level variable.

My package level variable is called RecordSet1 and type object.

I was able to use a Data Flow and populate the variable with data with a record set destination.

So, now I am writing a script component that makes a recordset as a source(I am new to ssis and I know this has performance issues, I am only doing this to get familiar with scripting cause my manager told me too) So I am not able to access any of the variables. My code is below. I am getting a error on this.Variables.RecordSet1. I looked all over online and unable to really find a solution. I know in script component, you can use the this.Variables.VariableName to access the variables but even my intellisense isn't showing anything.

Error 1 'Variables' does not contain a definition for 'RecordSet1' and no extension method 'RecordSet1' accepting a first argument of type 'Variables' could be found (are you missing a using directive or an assembly reference?) C:\Users\Dhinesh\AppData\Local\Temp\SSIS\ee6fc0aaa4f447e0b024451e97f51dec\main.cs 42 39 sc_95443d5e406b44d

Problem assigning a value to a variable in a Script Task

I've found loads of posts with the error I'm seeing: "The type of the value being assigned to variable [XXX] differs from the current variable type". However, I can't see anything that matches my scenario.  I'm simply trying to assign a value to a variable BEFORE passing the variable to an Execute SQL Task. I want to assign "true" or "false" to a Boolean variable called LogError, but as I got this error, I tried instead to assign 0 or 1 to it after converting it to an Int16 variable, but got the same result. I can see all manner of posts that make me wonder what I may have problems with next when I try to pass this variable to my stored proc that's expecting a BIT, but for now, I'd love SSIS to simply assign the value to the variable! Any ideas?

Error while trying to assign a value to a Read Write variable in SSIS package script component



       I am trying to develop a SSIS package which will read the records from the flat file and insert them into a destination table. I have some validations written in script component. I have declared two Read Write variables with package level scope. when i try to assign a value to the variable in the script component and run the package, the package throws me an error "The collection of variables locked for read and write access is not available outside of PostExecute".


What should be done to over come the problem please help me on this regard 




'variable' is not declared. It may be inaccessible due to its protection level.

I have been trying to get a value from my sql database to post to the form I have.  I have thought i was pretty close but it keeps giving me the error "User_EmailTextBox is not declared.  It may be inaccessible due to its protection level".  I have been trying to get this to work for a week and I can't seem to figure it out.  Any help would be appreciated.  My code is below.web.config ---------------------------------------------------------------------------- <?xml version="1.0"?> <configuration> <connectionStrings> <add name="ECDRConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ECDR.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True" providerName="System.Data.SqlClient" /> </connectionStrings> <system.web> <compilation debug="false" targetFramework="4.0" /> </system.web> <!--Mail settings--> <system.net> <mailSettings> <smtp> <network host="YOUR HOST HERE"/> </smtp> </mailSettings> </system.net> <!--Mail settings--> </configuration> --------------

How to load a new table with the value of a variable from SSIS package?

Hi, i have two variables in a SSIS package Var1 and Var2. Both of these variables have values. is there any way i can put the values of these two variables in a new table? e.g In New table col1 having value of Var1 and col2 having value of Var2. Thanks

SSRS 2005 SP3 - Instr function with Global Variable fails in Subscriptions

Hi I have a very simple expression in a text box in my report:  INSTR(Globals!ReportServerUrl,"Report").  This expression should return the integer value of where the string "Report" appears in the Global variable for the ReportServerUrl.  Simple enough.  This works fine for generating the report directly through report manager, and in my case returns a value of 20. However when run as a subscription and emailed to myself the value returned is 0. This suggests that the search string does not appear in the Global variable. In the reportserver config file the URLroot tag contains the correct server address - http://myserver/reportserver. Does anyone know why i should be experiencing this issue with my subscriptions?  Your help would be much appreciated! Thanks Guy  Guy

How to send record(which is a weblink) from a table to the value of the variable in SSIS package and

Hi Folks, I have table called Table1 with columns, col1 and col2 with col1 having weblinks for the report and col2 the name of the report. Now, i have a package with a variables var1 and var2 which should get the col1 and col2 values respectively from table1 and send it through an email. if the weblink gets updated in the table, package should send the updated link. i know the reverse way of it but trying to do somethig like this. Appreciate any help from you guys. Thanks

accessing package level variables in a Script Component in 2008 version


how to access the package level variables within a script component in SSIS 2008

Session variable in Java script


Please tell me how to read a session variable on to a Java script which is defined externally.

We can read this within the page.


Cannot create MDX calculation with one variable in the calculation from a leaf level


I am new to MDX/SSAS and have a problem with setting up two calculations. I have spent several days researching, endless rewriting of MDX queries, and did not find an answer (or didn’t recognize the post as an answer to my problem :)


I need help with two calculations.

One, a calculation, let’s call it  [Dollar Variance], that computes variance at all levels by this formula:

([Actual Work Billings] - [Budgeted Work Billings]) / [Work Days in Month] = [Dollar Variance]


The problem is that while the [Actual Work Billings] and the [Budgeted Work Billings] are summed at the particular dimension intersects chosen, for the variance to be calculated properly, the

start SSIS package via script task with package configuration via variables



it is possible to start ssis packages with dtexec and set variables as package configuration like this:

dtexec.exe /SQL "\mart1\import_fact_calls" /SERVER "R08" /SET \package.variables[FullImport].Value;True

Now I'm trying to start a package via script task with this code:

Public Sub Main()
    Dim pkg As New Package
    Dim app As New Application
    Dim pkgResults As DTSExecResult

    If Dts.Variables("package").Value <> "" Then
      pkg = app.LoadFromSqlServer(Dts.Variables("package").Value, "R08", "USER", "PASS", Nothing)
      pkgResults = pkg.Execute()
    End If

    Dts.TaskResult = ScriptResults.Success
End Sub

How can I add a package configuration like the one with dtexec to the script code?

I like any ideas, links, docus ... Thanks!

Accessing static variable in global.ashx file


Hi All ,

I want to access the static variable in my global.ashx file  I am using classname.variable name for accessing the variable but the variable name dose not appear.Can someone tell me how to access static varialbe in *.ashx file.

Global variable


Hello all!

I need to declare a variable that is global so i can evaluate roles permissions on page load.

How can i do this?

Thanks in advance!

Msg 137, Level 15, State 2, Procedure AddCMEXperience, Line 49 Must declare the scalar variable "@Re


Hi folks, can someone please help me with this error?

Msg 102, Level 15, State 1, Procedure AddCMEXperience, Line 15
Incorrect syntax near ','.

Msg 137, Level 15, State 2, Procedure AddCMEXperience, Line 49
Must declare the scalar variable "@ReasonLeft".

Here is the Stored Procedure that is in question here

USE [ResponsedDatabase]
/****** Object:  StoredProcedure [dbo].[AddNewEmployer]    Script Date: 10/03/2010 14:24:51 ******/

CREATE PROCEDURE [dbo].[AddCMEXperience]
    @MemberID bigint,
    @EmployerID bigint,
    @DateStarted smalldatetime,
    @IsContinuing bit,
    @DateLeft smalldatetime,
    @PositionStarted nvarchar(50),
    @PositionLeft nvarchar(50),
    @StartingSalary nvarchar(20),
    @EndingSalary nvarchar(20),
    @TimeWorked float,
    @JobResponsibilities text,
    @ReasonLeft, nvarchar(200)


INSERT CMExperience

Script Component - Assigning value to a variable problem


I have a Script Component and in it I want to assign a value from a row to a variable. I am iterating through a For Each Loop, so I am only getting 1 row at a time. I have a variable declared as ScriptedSaleID and the following code in my Input0_ProcessinputRow method:



myvars As IDTSVariables100

        'We lock the variable for writing. 1st parameter is the name of the variable
        VariableDispenser.LockOneForWrite("ScriptedSaleID", myvars)

        'Now we can write the value to the variable
        myvars(0).Value = Row.SaleID

        'Now we unlock the variables

My problem is

SSIS Parent/Child Package variable passing



I have having trouble getting my hands around how to retrieve variables from a parent package.  I read about the Environment variables and Configuration File at the parent package level and the Parent Package variable at the child level.

Here are my questions:

1.  Can you only store/retrieve 1 variable in a config file at a time?

2.  Does the child package have to define the variables and if so, do they have to be the same names as the parent package?

This seems so more more complex then the DTS2000 way of passing variables to and from packages.

Any help would be appreciated.

Thanks in advance,

Gordon Radley



ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend