none
assigning values to variables in ssis

    Question

  • HI all,
            I'm upgrading packages from dts to ssis. In DTS there is an dynamic properties task that dynamically assigns last success date to a variable. Now, in ssis i've created variable. My question is how can i assign that last success date to this variable in ssis?
    Wednesday, September 10, 2008 3:47 PM

Answers

  • In the ExecuteSQL Task set the resultSet property to Single Row(since ur query is returning a single value).

     

    Once you do that, Click on the ResultSet tab in the left pane. You can set the value from the query to the "Variable Name" section. Make sure to change the value under the "Result Set Name" value to Zero(0). 

     

     

     

    Saturday, September 13, 2008 4:48 AM

All replies

  • You can use Script Task to do it. Here is an article telling about it: http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2005/12/30/57.aspx 

    Wednesday, September 10, 2008 3:56 PM
  • HI chang thanx for ur reply. but the thing is  I'm not aware  of vb. scripting can't we do the same using 'execute sql task'.

    If not can you give me the vb script to following,

    I've two variables PSProcessDate and BeaconJob

    I need the get the result of the query 'select convert(varchar(30),dtprocessdate,21) from chi_sync_processlog(nolock) where vchname='beaconchosuser'' into PSProcessDate and

    the result of the query 'select bprocess from chi_sync_processlog where vchname='beaconchosuser' into beaconjob.

    Can u please help me out.

    Thanks in advance.
    Wednesday, September 10, 2008 4:20 PM
  • Yes, you can assign results from Execute SQL Task into variables.  The way of doing that is dependent on the type of connection manager so you will probably want to take a trip with your favorite search engine to find an example that matches your connection.

    Wednesday, September 10, 2008 4:40 PM
  • In the ExecuteSQL Task set the resultSet property to Single Row(since ur query is returning a single value).

     

    Once you do that, Click on the ResultSet tab in the left pane. You can set the value from the query to the "Variable Name" section. Make sure to change the value under the "Result Set Name" value to Zero(0). 

     

     

     

    Saturday, September 13, 2008 4:48 AM
  • Hi Prashant ur reply solved my issue. Thanks so much.



    I've one more issue while migrating a DTS package having Data Driven query task to SSIS. In data driven query task the  mappings of coulumns of source and destination columns are done using 'ActiveX script' while transferring data from source to desination. And there are a few lookups in data driven query task. How can i migrate these to SSIS? 

    Can you explain how this can be written in ssis steps?

    Thanks in advance.
    Wednesday, September 17, 2008 7:42 PM