Urgent!!! SQL Server 2005 - Is there someway to make Variables Visible outside the server


I have used the Export Wizard to create a package (SSIS), and the source data is determined by a Query.  Now, the Query has a parameter, and that parameter is mapped to a user created variable.  Now, if I have a value, for the Value property, the SSIS package will run, and the right data will be transfered to the destination.

However, if I try to use my VB code, that does find the package and execute it, it cannot find the any of the variables.  It's almost like the variables are not visible, outside of the SQL Server.

How do I make the variables "Visible" Now, before you say this is a VB Question and not DB, it might be, but I want to determine if there is something I need to do to make them visible.

If it helps, here is my VB Code that I use to execute the package, and the line where I try to change the value of the variable:

Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Smo.Agent
Imports Microsoft.SqlServer.Management.Common

Imports System.Data.SqlClient

Public Class Form1

    Dim app As Microsoft.SqlServer.Dts.Runtime.Application = New Microsoft.SqlServer.Dts.Runtime.Application
    Dim pInfos As PackageInfos = app.GetPackageInfos("\\", "SQLServer", "UserName", "Password")

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Sub btnExeSelSSIS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExeSelSSIS.Click
        Dim app As Microsoft.SqlServer.Dts.Runtime.Application
        app = New Microsoft.SqlServer.Dts.Runtime.Application
        Dim pkgResults_Sql As DTSExecResult
        Dim pkgErrors As DtsErrors
        Dim pkgWarnings As DtsWarnings
        Dim vars As Variables

        Dim SSISPackage As String = "IC_Results"

        Dim pkgIn As Package = New Package
        pkgIn = app.LoadFromSqlServer("\\" + ComboBox1.SelectedItem.ToString().Trim(), "SQLServer", "UserName", "Password", Nothing)

        pkgIn.Variables("CreatorComputerName").Value = "Anything"

        pkgResults_Sql = pkgIn.Execute()

        pkgErrors = pkgIn.Errors()

        pkgWarnings = pkgIn.Warnings()

        'Message box will show either Success/Failure

        MessageBox.Show(pkgResults_Sql.ToString() + vbCrLf + pkgErrors.ToString() + vbCrLf + pkgWarnings.ToString())

    End Sub

    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.Click
        For Each pInfo As PackageInfo In pInfos
            If Trim(pInfo.Name) <> "Maintenance Plans" Then
            End If
    End Sub

Thanks in advance!

Who is Participating?
drydenhoggConnect With a Mentor Commented:
Since the value is sitting in a normal SQL Table, the syntax is just a standard update statement such as

UPDATE tblSSISConfigurations
SET ConfiguredValue = YourValue
WHERE configurationFilter = 'FilterValueYouSpecifiedInPackageWizard'
AND PackagePath =  '\Package\YourTaskName.Variables[User::MyVariableName].Properties[Value]'

Nothing out of the ordinary, just update it like any other table, prior to executing the package.
Configuration of packages can be exposed in a variety of ways, under the SSIS menu you will find an option called 'Package Configurations', There are a massive variety of options, but in terms of exposing the variable, when you get to select which properties are exposed, under your pacakge, in the executables folder, there is a sub folder for the task (such whatever name you gave to a data flow task) under that are two folders, properties and variables.

This will place the value into a sql table of your choice, and it reads this value before executing the package. In terms of setting values and then kicking off the package, I have used this mechanism - primarily because any language that can talk to SQL Server using normal T-SQL can then change parameters and execute it, it does not specifically rely on any DTS libraries to do this.

In terms of variables visible from code, I'm not sure on the mechanism when doing it like you are, but I think you might find the scope of the variiable has to prefix the name of it whe accessing it, such as User::CreatorComputerName

The entry in the table package config creates is \Package\ODs Import Task.Variables[User::MyVariableName].Properties[Value] , which supports that concept, but as I write, I am not sure on that one.

indy500fanAuthor Commented:

Thanks!  Trying your suggestions now!
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

indy500fanAuthor Commented:

I notice you said that you could change the value of a variable with T-SQL.  Could you send me the Syntax for doing that?  That might be much easier!

indy500fanAuthor Commented:
I think I am so close...but still not working.  The T-SQL code you gave me works, and updates the ConfiguredValue, that is set in the table that you reference, but it doesn't update the Variable.  It's like there is some sort of disconnect.

Maybe I missed a step, so let me outline what I did:

1.  First I created the SSIS package, through the wizard.

2.  Then, I opened that package up in Business Studio.

3.  Under the Data Flow Tab, I right-clicked on the screen, and chose "Variables."

4.  I created a new Variable named "Preamble", Scope=AutoCreated(PackageName), Type=String, Value=Nothing

5.  Now, I modified the Source SQL Query, and mapped the parameter0 to the User::Preamble

Note:  At this point, if I were to manually set a value in the variable, 'Q.I' for example, and Execute the package from my code or in the IDE, the proper data WOULD BE Exported.  So, at this point, I know everything is okay.

Now, I set the value back to Nothing.

6.  Now, I go to SSIS -> Package Configurations

7.  I check Enable package configurations.

8.  Click Add

9.  Configuration Type: set to SQL Server

10.  Choose "Specify configuration settings directly"
          a:  Use the SourceConnectionOLEDB
          b:  Created a new configuration table SSISConfigurations
          c:  Named the configuration filter to: PreambleConfigFilter
       And click Next

11.  I navigate directly under the package name to Variables, put a single check next to Value, under the variable(Preamble)

12.  Name the configuration name

13.  Summary:


      SQL Server

Connection name:

Any existing configuration information for selected configuration filter will be overwritten with new configuration settings.

Configuration table name:

Configuration filter:

Target Property:

Now, when I update the SSISConfigurations table, the Value on the Variable doesn't change, or is that not it's purpose?


The value does change if the package is executed from fresh, what I think might be happening is that you have changed the value whilst the package is still open in the BI Studio? (It's always a guessing game on exactly how to read what has been written, by saying the Value on the Variable doesnt change, I think you have BI open)

In which case no, the value is not changed, BI loads the values once only when it loads the package and does not refresh them. If you were to shut the package, shut bi then reload the package in BI it would change to the new value you set via sql.

indy500fanAuthor Commented:
You must have done this a time or two.  Yes, BI is open.  Let me try this with it closed, and we'll see what happens.

indy500fanAuthor Commented:
Okay, you have given me exactly what I have asked for, and I have learned a ton!

Thank you very much drydenhogg!!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.