Solved

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

Posted on 2006-11-30
8
313 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
 
LVL 8

Accepted Solution

by:
drydenhogg earned 500 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now