using package variables as parameters in Execute Sql Task

Hot Topic

Connection to Oracle
Modify SSIS package saved on server
Sql Question
Establishing a remote connection..!
How to get the SessionID from the html viewer?
How to publish reports to production environment?
SSIS Fuzzy Matching
SQL 2000 Publisher and SQL 2005 Suscriber
Pocket PC database connection
Suitability for Serach intensive web app

SQL Server

Resources about Load/Run SSIS packages using .NET Remoting.
ReportServer giving me a 404????
Is Installing SQL05dev and VS05pro on the same machine supported?
Password field (very important)
Using the RDL Export function from Proclarity Desktop Professional
Automatic Trimming Transformation
Report Sort Order
import xml data with & character
SQL Express Advanced commandline install
Install Error with Sept CTP

using package variables as parameters in Execute Sql Task

Hi,

I need to run an SQL command, in which one of the fields is based on the current time, represented as a string in a specific format.

I used a script task to create the string from the current time, and store it in a package variable named "newDate". Later, in the execute sql task, I have the following update statement: "update table_1 set field_1 = " (OLE DB connection is used)

and in the parameters mapping I set parameter 0 to the string package variable "newDate".

The problem is that on runtime, the sql executes with the default value of the variable - i.e. it doesn't take the new value.

I checked it and placed another script task after the first one, that simply tries to read the variable "newDate" and print it to screen, but even here the default value is used.

how can I use the new value of the variable

thanks.

Answer this question

using package variables as parameters in Execute Sql Task

  • Anonymous Date

    WEIRD!

    When I set the variable to raise an event when value changed, it started working correctly.

    I then re-changed it to false, and it is still working...

    smells like a bug

    anyway, it's working now. thanks :-)

  • Brendonv

    Can you repro it in a brand new package

    If you can then its probably a bug in which case you can submit it as such at the feedback center (http://lab.msdn.microsoft.com/productfeedback/default.aspx) with repro steps.

    -Jamie



  • Chris Dufour

    here it is:

    Public Sub Main()

    '

    ' Add your code here

    '

    Dim newDate As String

    newDate = Date.Now.ToString("yyyyMMddTHHmmss") + ".000000000000 "

    Dts.Variables("User::NewDate").Value = newDate

    Windows.Forms.MessageBox.Show(CStr(Dts.Variables("User::NewDate").Value))

    Dts.TaskResult = Dts.Results.Success

    End Sub

    as you can see I added another message box with the value after the assignment from within the same script, and it shows the correct value. running the same line from another script task (that executes after this one is completed successfully) gives the default value.

  • Mythran

    Well that looks OK. What is the type of @[User::NewDate]

    Did you use the Watch window to watch the value and see if it changed

    I also suggest you implement the OnVariableChanged eventhandler to see if that event is fired.

    -Jamie



  • Padrick

    Firstly, look into using the Watch window in conjunction with breakpoints in order to examine the variable values.

    I would suggest that you are not setting the variable correctly in the script task. Perhaps you could post your code

    -Jamie



  • viral@excellenceinfonet.com

    nope, couldn't reproduce it.

    oh well...

    now it works :-)
  • using package variables as parameters in Execute Sql Task
Answer this question