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

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

Dts Package Variables Works in ScriptMain but Not DtsUtility.cs

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :

I have a few useful snipits that I had preferred to keep in a helper file named DtsUtility.cs

The identical code for the Read/Write variables was working in ScriptMain in one task, but again rather than paste that into the ScriptMain for each task I wanted to add the on cs file to each task.

The error essentially is that the variables cannot be found at line, vd.LockOneForWrite(varName, ref Vars);

ScriptMain snipit...

        using DtsUtility;
        ...........omitted code......
        if (dsX.Tables[0].Rows.Count >= 1)
        ParaID = (Guid)dsX.Tables[0].Rows[0]["ParaID"];
        CaseNo = (string)dsX.Tables[0].Rows[0]["CaseNo"];
        sXML = (string)dsX.Tables[0].Rows[0]["OrderXML"];
        DtsUtil u = new DtsUtil();
        bSucess = u.WriteDTSVar("RecID", RecID);
        bSucess = u.WriteDTSVar("ParaID", ParaID);
        bSucess = u.WriteDTSVar(&

View Complete Post

More Related Resource Links

Execute an SSIS package from ASP.net (C#) - my package keeps failing. Help! - Works on localhost but

I'm looking for any and all information on how to execute an SSIS package from my asp.net web site. I have written the call and when I run the application from (Localhost) it works, but when I run it from the server it does not.Here is my code to call and execute the package: public string ProcessFiles(int ClientID, string FileName) { //System.Security.Principal.WindowsImpersonationContext impersonationcontext; //impersonationcontext = ((System.Security.Principal.WindowsIdentity)ServiceContext.User.Identity).Impersonate(); string packagePath = "\\\\\\Customer File Import.dtsx"; Microsoft.SqlServer.Dts.Runtime.Application app; Microsoft.SqlServer.Dts.Runtime.Package pkg; Microsoft.SqlServer.Dts.Runtime.Variables vars; Application integrationServices = new Application(); DTSExecResult result; FileName = FileName.Substring(FileName.Length - 25 , 25); app = new Microsoft.SqlServer.Dts.Runtime.Application(); pkg = app.LoadPackage(packagePath, null); //pkg = integrationServices.LoadFromSqlServer("Customer File Import.dtsx", "", "healthtech\\brian.montfort", "Nightdragon8", null); vars = pkg.Variables; if (pkg.Variables.Contains("

Creating SSIS Package Variables Programmatically

Hi There, I am fairly new to SSIS.  I am creating a number of custom SSIS Tasks that I want to be able to share information between at runtime.  I figured that the best way to do this was through package variables, as in the Execute() method each custom task has access to the package variables through the VariableDispenser object.  I have managed to read variables at runtime that I create in the designer, however I am having trouble creating and writing to them.  I have created a very simple custom task below that I would expect to create the specified variable... [DtsTask(DisplayName = "TestVariableWriter")] public class Variab : Task { public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction) { try { string varName = "TestVariable"; string varValue = "TestValue"; Variables vars = null; variableDispenser.LockForWrite(varName); variableDispenser.GetVariables(ref vars); if (variableDispenser.Contains(varName)) vars[varName].Value = varValue; else vars.Add(varName, false, string.Empty, varValue); vars.Unlock(); return DTSExecResult.Success; } catch (Exception ex) {

Can multiple branches of a package use the same variables

Can multiple branches of a package use the same variables. I don't want to create new variables for each branch .I am getting an error in one of the script tasks  "The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there."  Assuming I dont have a naming error  what else should I look for.

Unable to build connection string using Package Level variables from the AcquireConnection fonction

Hi all! In SSIS 2008, within a script component trying to refer to a package level variables that was added to the ReadOnlyVariables collection of the component, the following fails within the AcquireConnnection function and I get this error: Error 1 Validation error. Test1: Test1: Microsoft.SqlServer.Dts.Pipeline.ReadOnlyVariablesNotAvailableException: The collection of variables locked for read access is not available at this point.     à Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)     à Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.AcquireConnections(Object transaction)     à Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction)   string connectionString = "DSN=" + this.Variables.MYDSN; Conn = new OdbcConnection(connectionString); Conn.Open();     If I move the previous code in the PreExecute function, it runs fine though.   Then, if I move the connectionString variable to the class definition, and populate it from within the PreExecute function and leave the last 2 lines in the AcquireConnection function, I get the following error: Error 1 Validation error. Test1: Test1: System.InvalidOperationException: The ConnectionString property

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

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!

Sharing the variables in a master package,



I have created an SSIS package that calls other packages.

This master ssis package contains a variable say MyVariable.

In the child packages I use this MyVariable in an expression to build a connection string.

@[User::MyVariable] + "Something"

This works fine, however each time I open the child packages - a couple of errors are shown in the errors pane notifying me that the variable does not exist in the child package. This is correct because it does not - however at runtime the child package can make use of the variables in the parent ssis package.

So how can i supress these errors?


I.W Coetzer

Accessing Package variables at design time


Hello All,

I have a question relating to the variables in SSIS.

I have an existing etl package with data flow components. One of the column is System_id to identify the Source System, the package is pulling the data from.  I plan to create a component which will validate the data based on the Rules stored in the database. These rules will be accessed based on the System_id + Target combination which needs to be set by the user at design time when the component is dropped.

In design time, i want to access this System_id field for the Custom data flow component so that i can access the database table and display the available Targets for this System_id which the user can set so that i can run the rules against this data.

I understand that i cannot access the value of the data flow at design time. My question is there other way, where i can set the variable value and access it at design time ?

Please let me know







Get list of package variables in SSIS code


Hi all,

i need to get list of package variable names during execution of a custom control flow task.

Best regards Crom

SSIS - Variables sharing from child to parent package



I'm a having a problem in passing the value of a variable from child package to the parent package in SSIS.

Can anyone please help me??? Any process would be helpful..either using configurations or using scripting (c# or vb.net)..



Error Running SSIS Package as SQL Agent Job loading a file from network drive, works fine in bids an

Having problems reading an excel file from a networked drive while running a package as a SQL Agent job step as a type "SQL Server Integration Services Package". I have agent running as an AD account, and I can use the execute package utility logged in as this account and it works. When I log into the server as this account, I can see the network drive, read/write/create to folders on the drive but when I try to run the agent job it cant find the file. I tried using the unc path in the config file rather than a mapped drive, still no luck. If I use a local drive (c) in the config file, the job works fine so it has to do with the network drive. Running windows server 2003 standard R2, SQL Server 2005 standard sp3. Any ideas out there?

Temporary tables in SQL Server vs. table variables

When writing T-SQL code, you often need a table in which to store data temporarily when it comes time to execute that code. You have four table options: normal tables, local temporary tables, global temporary tables and table variables. I'll discuss the differences between using temporary tables in SQL Server versus table variables.

Code works on aspx but not ascx


Not sure which category to put this question in, so I will put it in here.

Just to give you an idea of the layout of my solution, I have the main project, along with 2 class libraries, BLL and DAL.  (Business Logic Layer and Data Access Layer)

The BLL refers to the DAL and the main project refers to the BLL.

I had a test website and the code worked fine, but the same code will not work on the real project. The only difference is that in the test project, the code was on an aspx code behind, whereas in the real project, it is an ascx code behind (Web User Control )

The code is:

Dim connectionString As String = ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString").ConnectionString
        Dim recordEntry As New UserRecord(connectionString)
        recordEntry.UserName = "TestName"
        recordEntry.BMI = bMI
        recordEntry.BMR = bMR
        recordEntry.FTM = fTM
        recordEntry.TDEE = tDEE
        recordEntry.TodayDate = dateForToday

Dim connectionString As String = ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString").ConnectionString

Session and Application Variables


I have a web application in which i need to logout the user if he clicks the logout button or even if he closes his browser window. On Logout button click, i am doing session.Abandon(), but how to go about his browser window close.

Can anybody help with a code sample.

Problems posting to a package


Hey Y'all

I'm currently creating a program that accesses a mysql package to add data to a few tables.  I'm currently getting an error that says  "

ORA-01403: no data found
ORA-06512: at "JESSE.PKG_WORKPLAN", line 42
ORA-06512: at "JESSE.PKG_WORKPLAN", line 177
ORA-06512: at line 1


These lines are


and 177:add_team_members(V_PROJECT_ID, V_START_DATE, v_FISC_YEAR, V_MEMBERS ,V_NUM_MEMBERS,'add');

Checking these variables in my asp.net page they are all receiving parameters.

Is there a  way to see what the database is actually receiving from the asp.net page?

Or is there another thing to be looking at?


HttpHandler works with Visual Studio Development Server debugging, but not otherwise


Hi all,

I'm new to HttpHandlers.  I am writing an HttpHandler that reads uploaded files directly from the request stream to provide progress that can be queried using an AJAX request.  For large files, this seems to be faster than having ASP.NET load the file contents into Request.Files and then streaming the file from there to wherever it goes (Linq to SQL into a VARBINARY column in an SQL Server database.)

My HttpHandler works perfectly when I'm debugging using the Visual Studio Development Server, but doesn't work otherwise.  The application pool for this application in IIS 7 is set to run in Classic Mode, and the Handler Mappings screen in IIS says that I must manage managed handlers in system.web/httpHandlers. 

I set up in my applications Web.config file.

<add verb="GET,POST" path="*.upload" type="Namespace.For.UploadHandler, AssemblyForUploadHandler"/>

It works great, so long as I don't debug with the "Use Local IIS Web server" option.

What else do I need to do to get this to work?


SMTP not working if I use web deployment package to deploy my app.


Hi all

I use msdeploy to install my web app. deployment package. everything about web app works, but find out email was not working.

I checked telnet smtp is running.

I debug the project, and it was sending out email too.

So my solution was upload the whole project (not complied) to wwwroot, and email works too. Thats why I assume the deployment cause the problem.

at last, I also tried to copy the web deployment web.config to replace the project's web.config, and the email also work, so it wasn't the web.config problem.

Could anyone tell me whats going on?

My environment:

window 2003

iis 6

.net framework 4.0

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