April 10, 2009 at 1:45 pm
Hi
I have a SQL statement for example SELECT path from Servers where serverPath = 'FilePath' and I need to pass the 'path' value to an input variable. Any ideas how should I go about doing it. Please help. THanks.
I have created a Variable name = Path
Scope = Package
DataType = String
Value = ---- trying to get the path value from the SQL
April 12, 2009 at 1:51 pm
You can use Execute SQL Task to achieve this. Set the Resultset to "Single row", SQLSourceType to "Direct input" if you'll be typing in the sql statement instead of using a stored
sql code, and set BypassPrepare to "True".
You also have to go to the Resultset tab, and Add the path parameter you created as the "VariableName" and give the "ResultName" the fieldname you are returning in your select statement (from your query its - SELECT path).
I hope this helps.
Cheers!
April 12, 2009 at 2:08 pm
Hi Famson,
I did what you stated but when I tried to pass it to the Script task and display in the msgbox using VB.NET abd it gaves me an error. Do you know why I could not pass it as a input varibale and display in the scrip task? Below is the VB.NET syntax.
Msgbox (Dts.variable("Path"))
April 13, 2009 at 10:48 am
try
Dts.Variables("Path").Value
April 13, 2009 at 11:31 am
Hi Vishal
It was my typo mistake. I did use DTS.variables("Path") but I now I am getting the error "failed to lock variable" on the script task. Any helps is apprceiated. Thanks.
April 13, 2009 at 11:53 am
Did u input the variable name "Path" in the ReadOnlyVariables property of the script task? If not you need to do this. Otherwise, instead of the Dts.Varaibles use the Dts.VariableDispenser
Hope this helps.
April 13, 2009 at 12:42 pm
Hi Vishal,
I tried both ways with and without the Readvariables and still return an error. Also I have an expression as ReadonlyVariable on my script task as @[User::Path]
Below is the my code without the Readvariables User::Path
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
'
' Add your code here
'
Dim oFile As System.IO.File
Dim oWrite As System.IO.StreamWriter
Dim vars As Variables
oWrite = oFile.CreateText("C:\Test1.txt")
oWrite.WriteLine("Test")
oWrite.Close()
Dts.VariableDispenser.LockForRead("User::Path")
Dts.VariableDispenser.GetVariables(vars)
vars.Unlock()
MsgBox(vars("User::Path").Value)
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
----------------------------------
Error I got is Failed to Unlock Variable. Please advise. Thanks.
April 13, 2009 at 2:13 pm
In your ReadOnlyVariables do u have it as @[User::Path] or User::Path
The latter is the correct one.
April 13, 2009 at 2:36 pm
I have it as User::Path on ReadonlyVariables and on the Expression I have it as @[User::Path]. Still got the same error.
April 14, 2009 at 12:28 am
If you are using the VariableDispenser method to set/retrieve variables, you do not need to add the variables to ReadOnlyVariables or ReadWriteVariables - in fact, you should not. Just trying deleting that property and re-running.
-- Edit, OK, I ju re-read this thread and it seems a bit more complex than I thought.
Here is a function that I use for retrieving the value of a variable in script. Maybe it will help you.
Private Function GetVariable(ByVal varName As String) As String
'Sub to set the value of a variable
Try
Dim vars As Variables
If Dts.VariableDispenser.Contains(varName) = True Then
Dts.VariableDispenser.LockOneForRead(varName, vars)
Try
GetVariable = vars(0).Value.ToString
Catch ex As Exception
' Write out an error, with the exception generating target site
' and the exception message
Dts.Events.FireError(1, ex.TargetSite.ToString(), ex.Message, "", 0)
' Always execute this code and return gracefully.
' Never return with an exception from script tasks.
Dts.TaskResult = Dts.Results.Failure
Finally
vars.Unlock()
End Try
End If
Catch ex As Exception
Throw ex
End Try
End Function
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 14, 2009 at 12:40 am
To fix the error, set the ReadOnlyVariables property of the Script Task editor to "Path" and not
"User::Path". You don't need to qualify it with the namespace.
Also, you don't need to create an expression since the value you needi s stored in the path variable and not the result of evaluating an expression.
Lastly, you can retrieve the value in two ways:
using the variable name e.g:
MsgBox(Dts.Variables("path").Value.ToString)
or using the variable index, should you have more than one variable in the ReadOnlyVariables property
MsgBox(Dts.Variables(0).Value.ToString) - index starts from 0.
But to make your approach work, you don't need to set the ReadOnlyVariables property. Your code is already doing the job.
I hope this helps.
Cheers!
April 14, 2009 at 6:44 am
Hi All,
Thanks for your help. I have used Famson last post and it works. Thanks for this site.
pcq0125
December 29, 2009 at 12:24 pm
Actually I have another question related to this post. I have each connection.dtsconfig connection file that located on Test and and Prod servers resding on E:\. The purpose is to move the package from Test server to Prod server and still calling the local E:\ configuration file and pull the correct path value. On the Execute SQL task I have set to use below
SQLSourceType = File Connection
File Connection = connection.dtsconfig
however once I set that the Build Query button is gray out and I am not able to create a SQL statement to retieve my path value and pass it to the variable. Any ideas?
Thanks,
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply