Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2006-11-30
8
Medium Priority
?
323 Views
Last Modified: 2012-08-14
Friends,

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.VariableDispenser.LockForWrite("Preamble")
        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
                ComboBox1.Items.Add(pInfo.Name)
            End If
        Next
    End Sub

Thanks in advance!

Regards,
Eric
0
Comment
Question by:indy500fan
  • 5
  • 3
8 Comments
 
LVL 8

Expert Comment

by:drydenhogg
ID: 18045122
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.

hth.
0
 

Author Comment

by:indy500fan
ID: 18045167
dh,

Thanks!  Trying your suggestions now!
0
 

Author Comment

by:indy500fan
ID: 18045226
dh,

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!

Thanks,
Eric
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 8

Accepted Solution

by:
drydenhogg earned 2000 total points
ID: 18045268
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.
0
 

Author Comment

by:indy500fan
ID: 18046144
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:

Name:
      PreambleConfigFilter

Type:
      SQL Server

Connection name:
      SourceConnectionOLEDB

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

Configuration table name:
      [dbo].[SSISConfigurations]

Configuration filter:
      PreambleConfigFilter

Target Property:
      \Package.Variables[User::Preamble].Properties[Value]  

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

Thanks,
Eric

0
 
LVL 8

Expert Comment

by:drydenhogg
ID: 18046192
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.





0
 

Author Comment

by:indy500fan
ID: 18046253
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.

Thanks,
Eric
0
 

Author Comment

by:indy500fan
ID: 18047990
Okay, you have given me exactly what I have asked for, and I have learned a ton!

Thank you very much drydenhogg!!!
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question