x

getdate() function in a SS Integration services derived table transformation- new column- expression. How do I force the result of a sql script task value into a variable

The requirement is to show the contents of the getdate() function in a derived table- new column- expression. I have tried entering the getdate() into a variable and then reference the variable in the expression but the value only shows getdate() as text, but not the actual date.

Is there way to do this?
more ▼

asked Aug 11 '11 at 07:46 AM in Default

jmb4 gravatar image

jmb4
41 10 12 13

(comments are locked)
10|1200 characters needed characters left

3 answers: sort oldest

how are you declaring the variable?

 DECLARE @DATE datetime  
 SET @DATE = getdate()  
 SELECT @DATE
more ▼

answered Aug 11 '11 at 07:58 AM

Tim gravatar image

Tim
35.5k 32 40 138

within the variable at package level, I have a select getdate()as the value and type of string. I have also tried your suggestion but the expression outputs the script you have provided rather than the getdate() value.

Please note the variable is the SSIS variable holder as opposed to a sql task.
Aug 11 '11 at 08:06 AM jmb4
(comments are locked)
10|1200 characters needed characters left

Do you mean this?

DECLARE @tbl TABLE
    (
      ID INT ,
      DateDefault DATETIME DEFAULT ( GETDATE() )
                           NOT NULL
    )

-- an insert that specifies a value for the column
INSERT  INTO @tbl
        ( [ID], [DateDefault] )
VALUES
        ( 0, -- ID - int
          '2011-08-11 15:00:49'  --  datetime
          )

-- an insert that leaves the column to recieve the default value
INSERT  INTO @tbl
        ( [ID] )
VALUES
        ( 1 -- ID - int
          )


SELECT
    [t].[ID] ,
    [t].[DateDefault]
FROM
    @tbl AS t
more ▼

answered Aug 11 '11 at 08:11 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

Guys thanks for your responses.

My query is in regards to SQL server integration services. I think the derived table comment maybe somewhat misleading. There is a derived table transformation(not the same as a sql derived table)in SSIS in which a placeholder for a variable is not showing the value of a script in an expression.

I am trying to extract the value of whats is the variable into an expression but it does not extract it. Just shows the sql script.

Hope this clears out some of the mud.

;-)
Aug 11 '11 at 08:17 AM jmb4
I think the title is misleading I will repost.
Aug 11 '11 at 08:18 AM jmb4
I think that @jmb4 is meaning the Derived Column Transformation in SSIS, right? As I wrote in my answer.
Aug 11 '11 at 08:19 AM Pavel Pawlowski
@jmb4 - just edit this question to be more specific, by name, description and tags. Dont start a new question please.
Aug 11 '11 at 08:35 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

The GETDATE() is a SQL Server function so to get the value, you need to evaluate it by SQL SERVER.

In case you want to store the value in a variable, you have to declare a variable of DateTime type and then run a 'SELECT GETDATE()' from withing Execute SQL Task and put the result into the variable, which you can then use in whatever transformation (eg. Derived Column).

Other option could be to use the SSIS GETDATE() function in the expression or use the 'System::StartTime' system package level variable which returns the timestamp when the package has started and will be constant during package execution. The GETDATE() function will be different during the flow.

However the 'System::StartTime' and GETDATE() can return different results from the SQL GETDATE() as the SSIS functions return date time from machine on which the package is being executed and the SQL function returns date time from the machine on which the SQL Server instance is running.
more ▼

answered Aug 11 '11 at 08:18 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.2k 8 11 21

HI Pavel,

That exactly what I mean. How do I push the value of getdate from a sql task into a variable?
Aug 11 '11 at 08:35 AM jmb4
If you have OLE DB connection, then set the ResultSet property of the SQL Task to SingleRow. Then in the Result Set add a new asignment. The Result Set Name set to 0 and choose your variable with DateTime type. After the SQL Task executes, you will have the value in your variable.
Aug 12 '11 at 01:05 AM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x899

asked: Aug 11 '11 at 07:46 AM

Seen: 1588 times

Last Updated: Aug 11 '11 at 08:54 AM