Expression Date Functions

by Darren Green 28 Oct 2008 07:58

Date Parts

Expressions support a range of date related functions such as DATEADD, with the same basic syntax to that found in T-SQL. Whilst the familiarity is very helpful, the difference that catches me out is the format of date part which must be quoted.

T-SQL allows this:

 DATEADD(n, -10, GETDATE())
 DATEADD(mi, -10, GETDATE())
 DATEADD(minute, -10, GETDATE())

The SSIS equivalent is:

 DATEADD("n", -10, GETDATE())
 DATEADD("mi", -10, GETDATE())
 DATEADD("minute", -10, GETDATE())

Related functions that use the same date part tokens -

  • DATEADD
  • DATEDIFF
  • DATEPART

Month Name Expressions

Here are some month name expressions, just waiting for a DATENAME function.

Get the month name, for the column RowDate:

 (MONTH(RowDate) == 1 ? "January" : MONTH(RowDate) == 2 ? "February" : MONTH(RowDate) == 3 ? "March" : 
  MONTH(RowDate) == 4 ? "April" : MONTH(RowDate) == 5 ? "May" : MONTH(RowDate) == 6 ? "June" : 
  MONTH(RowDate) == 7 ? "July" : MONTH(RowDate) == 8 ? "August" : MONTH(RowDate) == 9 ? "September" : 
  MONTH(RowDate) == 10 ? "October" : MONTH(RowDate) == 11 ? "November" : 
  MONTH(RowDate) == 12 ? "December" : "InvalidMonth")

Get formatted month and year, mmm (yyyy), from the column RowDate:

 (MONTH(RowDate) == 1 ? "Jan" : MONTH(RowDate) == 2 ? "Feb" : MONTH(RowDate) == 3 ? "Mar" : 
  MONTH(RowDate) == 4 ? "Apr" : MONTH(RowDate) == 5 ? "May" : MONTH(RowDate) == 6 ? "Jun" : 
  MONTH(RowDate) == 7 ? "Jul" : MONTH(RowDate) == 8 ? "Aug" : MONTH(RowDate) == 9 ? "Sep" : 
  MONTH(RowDate) == 10 ? "Oct" : MONTH(RowDate) == 11 ? "Nov" : MONTH(RowDate) == 12 ? "Dec" : 
  "ERR") + " (" + (DT_WSTR,4)YEAR(RowDate) + ")"

yyyy-mm-dd

The common yyyy-mm-dd format is often used in file names, for example:

 C:\Temp\ErrorCodes\2005-11-18.txt

A sample expression to achieve this is:

 "C:\\Temp\\ErrorCodes\\" + (DT_WSTR,4)YEAR(GETDATE()) + "-" 
    + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + "-" 
    + RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + ".txt"

A similar expression, but this time deriving the file name based on yesterday’s date, useful for loading the previous day's data:

 "C:\\Temp\\ErrorCodes\\" + (DT_WSTR,4)YEAR(DATEADD("dd", -1, GETDATE())) + "-" 
    + RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("dd", -1, GETDATE())), 2) + "-" 
    + RIGHT("0" + (DT_WSTR,2)DAY(DATEADD("dd", -1, GETDATE())), 2) + ".txt"

yyyy-mm-dd hh:nn:ss

Another simple time and date expression example:

 2006-06-22 11:48:52
 (DT_WSTR,4)YEAR(GETDATE()) + "-" 
    + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + "-" 
    + RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + " "
    + RIGHT("0" + (DT_WSTR,2)DATEPART("hh", GETDATE()), 2) + ":"
    + RIGHT("0" + (DT_WSTR,2)DATEPART("mi", GETDATE()), 2) + ":"
    + RIGHT("0" + (DT_WSTR,2)DATEPART("ss", GETDATE()), 2)

Or alternatively:

 (DT_WSTR, 10) (DT_DBDATE) GETDATE()  + " " + (DT_WSTR, 8) (DT_DBTIME) GETDATE()

dd-mm-yyyy

 RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + "-"
    + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + "-" 
    + (DT_WSTR,4)YEAR(GETDATE())
 18-07-2006

yyyymmdd

A simple yyyymmdd formatted string from a DateTime type variable

 (DT_WSTR,4)YEAR(@[User::DateTimeVar])
    + RIGHT("0" + (DT_WSTR,2)MONTH(@[User::DateTimeVar]), 2)
    + RIGHT("0" + (DT_WSTR,2)DAY(@[User::DateTimeVar]), 2)

An alternative yyyymmdd formatted string from a DateTime type variable.

 (DT_WSTR,8) (
   (YEAR(@[User::DateTimeVar]) * 10000) + 
    (MONTH(@[User::DateTimeVar]) * 100) + 
    DAY(@[User::DateTimeVar])
    )

yyyymmdd hh:nn:ss.mi

 
 (DT_WSTR,8) (
    (YEAR(@[User::MaxStartDate]) * 10000) + 
      (MONTH(@[User::MaxStartDate]) * 100) + 
      DAY(@[User::MaxStartDate]) 
    ) + " " + 
    RIGHT("0" + (DT_WSTR,2)DATEPART("hh", @[User::MaxStartDate]), 2) + ":"
 + RIGHT("0" + (DT_WSTR,2)DATEPART("mi", @[User::MaxStartDate]), 2) + ":"
 + RIGHT("0" + (DT_WSTR,2)DATEPART("ss", @[User::MaxStartDate]), 2) + "."
 + (DT_WSTR,3)DATEPART("Ms", @[User::MaxStartDate])
 20070511 09:40:38.123

ISDATE() workaround for date values

This checks against a string value where the source system used "00/00/00" as a lack of date. There were also columns that were out of the normal range for date values (AD 1, 0600, etc). This expression NULLs those values out. I figured that someone may have been struggling with this and the lack of an ISDATE function for expressions. It could be easily adapted to handle other date formats and checking for out of range dates.

 (Date_To_Check == "00/00/00" || (DT_Date)Date_To_Check < (DT_DATE)"1753-1-1") ? NULL(DT_DATE) : (DT_Date)Date_To_Check 

Get Date - Remove Time

If you wish to return the date only, so setting the time to 00:00 you can easily do this by casting to DT_DBDATE. The data type has limited support, so casting it back to a DT_DATE will allow you to use it more readily.

 (DT_DATE)(DT_DBDATE)@[User::WorkingDate]

Or, alternatively:

 DATEADD("day",DATEDIFF("day",(DT_DBTIMESTAMP)0,GETDATE()),(DT_DBTIMESTAMP)0)

Calculate the Beginning of a Previous Month

This expression starts from today, moves back three months (as an example), subtracts the day-count from the current day-of-month to get the first day, then converts the expression to a DT_DBDATE type (which does not support a time component) then converts it back to a regular DT_DATE, which does have a time component - but now it's truncated the time to 00:00 AM.

 (DT_DATE)(DT_DBDATE)DATEADD("dd",-1 * (DAY(GETDATE())-1),DATEADD("month",-3,GETDATE()))

Calculate the End of a Previous Month

To get Midnight on the last day of the previous month, we back up to the first day of the immediately following month, truncate the time to 00:00 (as in the previous example) and then subtract 1 minute to get the ending time of the previous day. (Note, in SQL Server 2005 SP2, subtracting 3ms to get the absolute last time-slice of the previous day did not work properly with the MONTH() function.)

 DATEADD("mi",-1,(DT_DATE)(DT_DBDATE) DATEADD("dd",0,DATEADD("dd",-1 * (DAY(GETDATE())-1),DATEADD("month",-2,GETDATE()))))

Getting the Fiscal Year for a Date

To get the fiscal year for a given date, use the conditional operator to check the month part of the date, and return either the year part of the date, or the year part of the date plus one depending on the cutoff of the fiscal year definition. This sample assumes a fiscal year that ends June 30th:

 MONTH( @[User::InputDate]  ) <= 6 ? YEAR ( @[User::InputDate]  )  : YEAR ( @[User::InputDate]  )  + 1

Comments (4) -

5/26/2010 4:39:16 PM #

Darren Comeau

A very helpful article.  I have something to contribute for solving a problem of adding two values together one being a date and the other a time but in this case both are stored as timestamps with Midnight or 1st Jan where data is missing.

I solved this by converting date to dbdate, time to dbtime, converting both to a string which can be concatenated and finally converting the string back to a date.

The expression being (DT_DATE)((DT_STR,20,1252)((DT_DBDATE)date) + " " + (DT_STR,20,1252)((DT_DBTIME)time))

date 2010-05-21 00:00:00.000
time 2010-01-01 00:01:10.000
dbdate 21/05/2010
dbtime 00:01:10

date 2010-05-21 00:01:10.0000000

Darren Comeau United Kingdom

6/30/2010 7:01:41 PM #

Eric

Note: the colons are not vaild in a file name.

Eric United States

9/8/2010 12:53:01 AM #

Gyhant

Thanks for very interesting tips ... problem I have though is while my expression provides me with a previous date ... it fails when moving into another month for example on September 1, 2010 I would expect the expression to evaluate to August 31, 2010, instead it evaluates to September 30, 2010 ... it handles the date difference within a given month correctly e.g. for today 09/07/2010 it will correctly evaluate to 09/06/2010 but not when the current month goes into the next month ... just cant figure what the problem is... I'd appreciate your showing me where I'm going wrong ... Thanks.

(DT_DATE) MONTH( DATEADD("mm", 0, GETDATE()) )> (DT_DATE) MONTH( DATEADD("mm", -1, GETDATE()) ) ?

"X:\\WNTS\\Reporting\\List28old "+ RIGHT( "0"+ (DT_WSTR, 2) MONTH( DATEADD("mm", 0, GETDATE()) ) , 2 ) + "-"+ RIGHT( "0"+ (DT_WSTR, 2) DAY( DATEADD("dd", -1, GETDATE()) ) , 2 ) + "-"+ (DT_WSTR, 4) YEAR( GETDATE() )+".csv" :

"X:\\WNTS\\Reporting\\List28old "+ RIGHT( "0"+ (DT_WSTR, 2) MONTH( DATEADD("mm", -1, GETDATE()) ) , 2 ) + "-"+ RIGHT( "0"+ (DT_WSTR, 2) DAY( DATEADD("dd", -1, GETDATE()) ) , 2 ) + "-"+ (DT_WSTR, 4) YEAR( GETDATE() )+".csv"

Gyhant Canada

10/1/2010 9:24:04 PM #

Isabella

Thank you very much!!!!
You helped to delete yesterday files (as first step) when starting to run job today.

Isabella United States

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Tags

10/31/2008 9:13:07 PM #

Allan Mitchell

Hi Steve
The resultset can, as you know, take a number of forms but will also be read into a variable.  Once in the variable you can do with it as you will.

If you use this transform www.sqlis.com/post/Row-Number-Transformation.aspx then it is very possible/probable that you will want to seed it with the MAX(Existing Value) which you could retrieve using an ExecuteSQL task and reading into a variable.  Using a property expression you would assign this variable to the Seed property of the transform (we expose this property through the parent Data Flow task)

Another use would be to read a resultset into a variable, of type object, and use that variable in a ForEachLoop enumerator.   Very similar to www.sqlis.com/post/Shredding-a-Recordset.aspx

hope this makes sense.

Allan Mitchell United Kingdom

11/5/2008 11:27:13 AM #

Jorge

You have saved many hours of my time!!. Thanks.

Jorge Argentina

11/5/2008 4:51:52 PM #

PedroCGD

Hi Alan!
How are you!? I hope you are good!

And how can I get the message returned from SQL statment like :
"
Msg 208, Level 16, State 1, Line 1
Invalid object name 't_data_notificacaso'.
"

OR
"
(13 row(s) affected)
"

PedroCGD Portugal

11/5/2008 6:17:10 PM #

Allan Mitchell

Pedro

For your first request.  Have a look in the Output Window and it will give you a big hint as to where to find this (Event Handler)

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "select * from INoThere" failed with the following error: "Invalid object name 'INoThere'

Your second request

Imagine the statement

UPDATE dbo.dimCurrency
SET CurrencyName = CurrencyName

Now add

SELECT @@ROWCOUNT as rtn

set your resultset to Single Row and read into a INT32 variable.

Allan Mitchell United Kingdom

11/7/2008 7:37:18 PM #

PedroCGD

Thanks Allan...
The need is not the error message or the count of rows... what i need is exactly the message inside SQL Server... the first line... Msg 208, Level 16, State 1, Line 1
regards!
See you next SQLBits! :-)
Pedro

PedroCGD

11/19/2008 7:18:16 AM #

Sriwantha Sri Aravinda Attanayake

Great article, keep up with the work! Contains all the information. Thanks buddy, by the way your country list does not have my country, Sri Lanka. hic hic

Sriwantha Sri Aravinda Attanayake

12/6/2008 3:07:34 PM #

Manikandan

Hi,

This is really very much helpful. What i want to get to know is, in case of script task following by the Execute SQL Task and if the Execute SQL task is returning full result set how can i use it Script Task???

Let me know as soon as possible.

Thanks,
Manikandan

Manikandan India

1/1/2009 9:23:55 AM #

Sanjeev

Can we more than on result sets for an XML result set.

Sanjeev India

1/19/2009 3:19:23 PM #

Brian Cooper

How do you pass a variable into a SQL Task that has executes inline SQL within the task?

Brian Cooper

1/22/2009 1:10:39 PM #

Allan Mitchell

personally Brian I would be inclined to create a variable that holds the Expression (EvaluateAsExpression) of the statement you want to run.  In the ExecSQL task you use that instead of a statement

Allan Mitchell Germany

2/7/2009 7:40:21 PM #

Mani V S

Excellent Article... Great Job!!!!

Thanks,
ManI V S

Mani V S India

2/12/2009 2:25:16 AM #

Bob Fidelman

Hello,
What I would like to do is handle a full resultset (created by the execute sql task in the control flow) in a data flow, which has more sophisticated transformation items and variable definitions. Currently there is no way to 'bridge' the gap from control flow to data flow without writing the resultset to disk. What I would like to see is the equivalent of a 'dataset' source in a dataflow. I realize that saving the data as an 'object' loses any metadata, so each column would have to be manually defined, if possible.

Also, using a resultset as a datasource would allow me to use the 'decimal' variable value, which is not defined in the control flow foreach control.

Any suggestions would be appreciated.

Bob F.

Bob Fidelman United States

2/18/2009 9:57:37 AM #

DesC

Hi, great article, but how did you set the watch variables?  I can only get the watch window to appear (there are 4 of them) but I cannot set anything.  Neither can I see anything in the locals window.

DesC

DesC United Kingdom

2/18/2009 10:03:33 AM #

DesC

Also, if instead of CountOfEmployees in the above example I create a datetime variable and try to populate by (for simplicity) a statement like 'SELECT GETDATE() 'MyDate'' it does not work.  I think the datetime format must be somehow intrinsically wrong or incompatible between SQL and SSIS but I'm not sure how.

DesC United Kingdom

3/17/2009 4:57:51 AM #

SDangol

Great article.
For some reason Returning a Rowset from Stored Procedures didn't work.

SDangol United States

3/18/2009 11:15:05 PM #

Mamata

How can I change the value of the input variable to the stored procedure at run time without opening the package. The input variable I need to pass is a varchar field and I'm not sure how to set this in the expression. Can you help?

Mamata United States

3/26/2009 8:14:44 PM #

Jerry

Thank you for the fine discussion of the workings of this container.
It was very helpful.

Jerry United States

4/17/2009 1:08:33 PM #

Laurent

Thank - Merci

Laurent France

5/22/2009 8:22:50 AM #

Jeswanth

Good work, really help full thanks a million

Jeswanth India

5/28/2009 8:18:40 PM #

Puran

Great!!! Thanks a lot...it made my life very easy...

Though I am still facing an issue. I run the query to get single row, also while executing the sql I have added breakpoints and watch. I see it returns the right value, but just before the task turns green it initiliazes to -1.

Any body any idea???

Puran United States

6/10/2009 1:27:56 PM #

Rashmi Patankar

I found the article quite useful, however...I coudnt see any output in the Watch window. Probably, I am missing something on setting the breakpoint part. Can you please brief on it?

Rashmi Patankar India

6/11/2009 4:57:00 PM #

Pat

This exemple was exactly what i needed.
Well done and clear.
i'll use this site a lot in the next few month i think.
Thank

Pat Canada

6/15/2009 6:26:43 PM #

Hemant patel

Just what I was looking for. Thanks

Hemant patel United Kingdom

6/16/2009 8:47:42 PM #

Herbert Rogelj

Hi, great article  -  but my "execute sql task" doesn´t work? Do you have an idea? i just want to get returned from the query -1 or null. True or False: My Query:
SELECT    
(CASE WHEN do_export = 0  AND  data_controlling = 1
THEN -1
ELSE 0
END)  AS IMP  
FROM        Tablexxx
WHERE     (LEFT(userid, 2) = ?)

In the Result Set i named IMP to the VariableName - ist that correct? IMP ever showed -1.
I would be so lucky if you or anyone could help me? Thanks for the answer!!
Herb

Herbert Rogelj Austria

6/17/2009 7:50:16 AM #

Allan Mitchell

Herbert

Does it return a resultset in Management Studio?

In your example you will need a parameter in "Parameter Mapping" for the "?" in your statement.
You will need to define the "Resultset" as "Full Result set"

You will need a variable of type "Object" to hold the resultset.

On the Resultset tab you map the "Result Name" of "0" (zero, null) to the Object Variable.

Allan Mitchell Germany

7/14/2009 2:54:46 PM #

Amit goyal

Hi,
I just wanted to assign the maximum of one of my col. to a variable. I did the following:
Created a variable activity_max
Writing a SQL task
query: select max(id) as id from dbo.activities
Nothing in parameter mapping
Result set : Result name = ID , Variable name = User::activity_Max

My result set = single row

I ran the query which runs fine but the value of my variable is still zero ??

Help please !

Amit goyal India

7/21/2009 9:44:22 PM #

Marianne Daye

I had to pass parameters to a stored procedure and an update statement within a ForEach Loop Container.  This article helped me figure that out.  Thanks!

Marianne Daye United States

9/18/2009 11:11:17 PM #

c_kenth

How do I capture the result set from the following SQL task? I'm running multiple statements, but the Execute SQL Task wants to grab the result set from the first SQL statement, not the last.

create table #Temp (
   value int
   );

insert into #Temp select top 1 col from Permanent;

select value from #Temp;

c_kenth United States

10/22/2009 8:05:46 AM #

Fabien Lonardi

Very good document.
But how to call an Oracle stored procedure. When I put "exec my_proc" in "sql statment", it is not working.
Hhave you an answer or URL links "ssis-oracle"

Fabien Lonardi France

12/8/2009 9:38:18 PM #

Adela

Excellent article! Unfortunately, although I followed the instructions to the letter, I am receiving the following error:
[Execute SQL Task] Error: Executing the query "EXEC usp_helloworld ?,? output" failed with the following error: "Value does not fall within the expected range.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I have created a dummy stored proc:
create table mytest (stmt as varchar(50), stat as int);
CREATE procedure dbo.usp_helloworld
@proj_task_key as bigint,
@proc_status as int output
as
begin
declare
@myproj as bigint;
set @myproj = @proj_task_key;
set @proc_status = 1;
insert into mytest values('helloworld' + cast(isnull(@myproj,'empty') as varchar),@proc_status);
end;
go
then, in SSIS, configured an EXECUTE SQL TASK transform as follows:
ResultSet: None
ConnectionType: OLE DB
Connection: to the db with the usp_helloworld and the table needed forstored proc
SQLSourceType: Direct Input
SQLStatement: EXEC usp_helloworld ,? output
Parameters-
Var Name            Direction Datatype  Parm Name      Parm Size
User::proj_task_key Input LARGE_INTEGER @proj_task_key 0
User::proc_status Output LONG @proc_status 1
As Variables User::proj_task_key is declared as Int64, Value 0 and User::proc_stat is Int32, Value 0.

I do not know where I have gone wrong. Can u help?
Adela

Adela United States

12/8/2009 10:15:04 PM #

Adela

I figured it out! My version of SSIS has Parameter Name and Parameter Size. I was putting name strings in the Parameter Name column when I should have been putting the parameter numbers (0 & 1)!
Yay!

Adela United States

12/10/2009 6:29:30 PM #

Scott C

I made the same mistake as Adela - putting names in the Parameter Names columns instead of 0,1,2...   My next mistake was putting anything in Parameters when what I really wanted was the user variables in the Result Set tab, not the Parameter tab. Once I cleared out Parameters, it ran like a charm and I used my variables to build dynamic flat file names.  :)

Scott C United States

12/15/2009 2:06:51 AM #

Neel

Nice one!!..It helped me a lot. Now I am thinking redesign my data conversion SSIS packages. Thanks again for the efforts.

Neel United States

12/28/2009 9:43:24 PM #

Nits

When I assign my result set to a user variable (int32) which was defined at the package level, the value of the variable became {-1} once the execution was done. I did see correct value in the same value on execution when it stopped at a breakpoint.

Am I missing something here??

Nits

1/6/2010 5:09:28 PM #

vidyasagar

Hi Allan Mitchell,
This article is great and helped a lot.
I have small doubt, now iam using Rowset as FullResutltSet where iam able to get multiple rows in a object variable like Com_Object but how to read the data from the com_object

vidyasagar India

1/13/2010 11:16:55 PM #

Christian

Gracias, Thank you, the tutorial it's very cool and easy.
saludos.

Christian Mexico

1/20/2010 11:01:16 AM #

Darren Green

vidyasagar, the output of using FullResultSet is an ADO Recordset object. Use the ADO library to access it, or try something like the ForEach Loop which has an ADO enumerator built in. An example of this is shown in the post www.sqlis.com/post/Shredding-a-Recordset.aspx

Darren Green United Kingdom

1/21/2010 12:13:50 PM #

Bruno Pimenta

Hello Allan,

I have a question about the execute SQL Task regarding the TIMEOUT. My objective is to unpack a data file from an receive data folder, read it's content into a DB and them compact the result to a archive folder (in case of error a message is set).

So far I was able to do all the steps, if you need details feel free to ask.

The problem starts when the file becomes large (I'm using one with 2 GB of size) because the SSIS execute task does not wait for the full unpacking and sets the error task. I think this is a timout problem and I have setted the value to 3600 (being in seconds this would mean an hour and it does not take that long) aldo without any success, it keeps falling.

If you can help me or if you have had a similar problem plesa respond.

Thanks in advance,

Complements,

Bruno Pimenta Portugal

1/21/2010 3:05:31 PM #

Javier Mora

Hello everybody ...

I have a problem when execute this qry in Execute sql task:

[Execute SQL Task] Error: Executing the query "select *     from   SRVCP.SerivaCapaDatos.dbo.CDH_ADMINISTRACIONPORFORMAPAGO" failed with the following error: "Error no especificado (Excepción de HRESULT: 0x80004005 (E_FAIL))". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I used Linked Server to connect to other server (Link server name : SRVCP). When i execute de sentence ( elect *     from   SRVCP.SerivaCapaDatos.dbo.CDH_ADMINISTRACIONPORFORMAPAGO) in Sql query analyzer it works great ...

Thanks for your time ...

Javier Mora Colombia

1/28/2010 8:53:07 AM #

Darren Green

Javier, the error you quote is the generic one the task raises. I would expect there another error message that gives more detail. It is common for a single problem to be reported across several messages in SSIS. The source will of course be the task.

Start by simplifying the issue, don’t use any input or output variables or any result set options, just try and get the SQL to execute. Since you mentioned linked servers, security is an obvious candidate. Think what is different about the manual query test compared to when using SSIS. Is the location or user different for example?

Darren Green United Kingdom

4/22/2010 11:52:02 AM #

Darren

Vani, the lack of images was no doubt all part of our recent Hosting woes (http://www.sqlis.com/post/Hosting-woes.aspx).

Darren United Kingdom

5/3/2010 10:10:23 PM #

jenny

This was of great help. But i have one question:
What if you have a SQL command in ADO.NET source in data flow task and you have to query against oracle DB. How can you pass date as a variable in that dataflow without writing a stored Procedure.

jenny

5/5/2010 5:18:51 PM #

Kari Suresh

I can see help only for of type OLEDB. I don't find anything replated to ADO.NET. Is this so difficult?

Kari Suresh India

5/24/2010 10:46:06 PM #

Ravi

Can some one provide sample SSIS links to connect to Oracle using OLEDB provider passing parameterized query to Oracle tables * buidling dynamic variables then executing as variables using Oracle OLEDB have buffer size issues.. (it works fine in DTS using ODBC)

Ravi United States

6/13/2010 6:05:08 PM #

Vlad

SSis package contains two tasks . First creates .csv file with varaible name based on  user::Filename (generated as Expresion Name+Timestamp) type string. When I try to pass this parameter to SQL task to execute SP with parameter I got conversion errors.
Which Data type should I assign. Lengh of Filename>50. I tried varchar,nvarchar different lengths - all fails with errors of conversion

Vlad Australia

6/17/2010 9:21:54 AM #

Sivaprasad S

Getting errors below:

[Execute SQL Task] Error: Failed to acquire connection "MulitServer". Connection may not be configured correctly or you may not have the right permissions on this connection.

Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

[Connection manager "MulitServer"] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Login timeout expired".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. ".


Sivaprasad S Australia

6/18/2010 7:12:17 AM #

Jorge

Excelent tutorial. Thanks

Jorge Mexico

6/25/2010 9:34:51 AM #

glaudie

hi there,

I've followed the steps describe above 100%.  I've declared a value which i want to populate with the result of a query to use as a starting point for my etl flow.  However , when i look at the variable value at breakpoint execution the value just DOES NOT get updated. I've tried to convert, cast , alias , not alias , using 0 as the result set name. NOTHING works. My variable simply keep its initial value i assigned it.  Got the advice that leaving the variable blank when defined, it could create issues. But even when i left its initial value blank, it still just kept the value "blank" at runtime, and never populated it with the result from my query. I've been struggling with this for 5 days now, and i'm so frustrated. it seems so simple, but somehow i must be doing something wrong that is preventing the value from being populated to the variable.

I found this post from someone else having the exact same issue, and i have done the exact steps this guy did.

I am trying to populate variables in a new SSIS package using the Execute SQL Task but I am having an issue to get the actual values to update! Steps I have completed....Created Variables with Package wide scopeAdded an Execute SQL task.Pointed connection to ServerEdited (& Tested!) the SQL StatementChanged Results Set to Single RowUpdated the Results Set tab to correct column and variableIf you run the package the Execute SQL Task works fine but watching the variables shows that the value does not change at all..I need these to do some dynamic stuff later on..Seems pretty straight forward..Am I missing something??Extra Details of an example..--Execute SQL Task--SQL Statement:SELECT FinancialYear --Works fineFROM [RemusV2].[dbo].[tbl_Structural_Dates]WHERE DATE = DATEADD(dd, DATEDIFF(dd,0,GETDATE())-7, 0)Result Set:SingleRowResultName=FinancialYearVariableName:FinancialYear

glaudie South Africa

7/13/2010 12:35:06 PM #

Akshay

@Sivaprasad S:
Are you sure the data source server is accessible? Are you able to connect to it via SSMS?

Akshay India

7/20/2010 10:15:55 AM #

manoj N

Hi

I am using SQL  task control
where in first i am checking if exist table <table1> then delete it
and next sql stmnt is Create the table <table1>.
i have included  both sql stmnt one next the other

but Sql task is failing

any help

manoj N India

8/3/2010 1:51:47 PM #

Albert

Здравствуйте!

Не могу разобраться, помогите пожалуйста!

ExecuteSQLTask выставил настройки:  result set = None , ConnectionType = OLE DB, Connection = BD, SQLSourceType = Direct input, SQLStatement = "select Columns from Table"
Все работает. По крайней мере ошибок нет. Далее. Хочу увидеть выходные данные. меняю:
result set = Full result set
ResultSet.ResultName = 0
ResultSet.VariableName = User::Columns
Запускаю. Ошибка:
[Execute SQL Task] Error: Executing the query "select Columns from Table" failed with the following error: "Неопознанная ошибка". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Что не так? Хочу лицезреть данные по Columns

Albert Russia

8/3/2010 3:22:02 PM #

Albert

Всем спасибо :) разобрался. Как иногда с Вами полезно пообщаться....

Albert Russia

8/4/2010 6:05:24 PM #

Beth

glaudie, did you find a solution for your issue? I am having the same issue, did it the same way as instructions and value is not updating.

This is my SQL statement and I defined my Variable as String even though it is defined as bigint in the database because I was getting an error and another post said you had to define bigint resultset variables as string.

SELECT MAX(EventLogID)AS MaxEventLogID
from STAGING.ImportHistoryEventLog

Beth United States

10/15/2010 2:07:11 AM #

Sam

Even with SQL 2010 R2 out this is still badly needed.  I agree that the high level docs read fine and everything seems sensible and logical ...until you try to actually do it and notice that the details are very vague. This article saved me lots of frustration.
Thanks much!

Sam United States

11/12/2010 8:26:49 PM #

Mike Milligan

So frustrating...

I am trying to do this:
select asdf from sometable where somecol = 'something'
union all
select asdf from sometable where somecol = 'something2'
union all
select asdf from sometable where somecol = 'something3'

then I go to the result set and have 3 ResultNames 0,1,2 ea. going to differ user::variables.

It works perfectly in one package but for the life of me I can't get it to work in another.

Arrrgh!

Any ideas?

Thanks!

Mike

Mike Milligan United States

11/12/2010 8:53:53 PM #

Mike Milligan

I stand corrected.  It wasn't working in either.

Ended up doing something like this:

select sum(one),sum(two),sum(three)
from
(select one = asdf,two = 0,three = 0 from sometable where somecol = 'something'
union all
select one = 0, two = asdf, three = 0 from sometable where somecol = 'somethingelse'
....)  aliasy

Mike Milligan United States

11/15/2010 2:43:11 AM #

tom xie

Hi,
Can you pass parameters to the file connection?

tom xie United States

12/1/2010 4:40:16 PM #

laban

how do you get values from rowset in the script task?
ie select col1, col2 from table where col1 = value
set resultset in sql task to 0 and user::test

how do you use dts.variable(user::test) in sql script task
or how do you read

laban Kenya

12/9/2010 4:29:22 AM #

yoyo

nice example for execute sql task..
if it quiet good with a finish result in the last step or is there any other step ?

yoyo Indonesia

12/14/2010 8:59:31 AM #

Sid

very nice article. It helped me a lot. Keep up the good work.

Thank you so much.

Sid India

1/10/2011 5:37:38 PM #

Chris H

Excellent article and just what I was looking for.

Keep up the good work.  

Chris H Ireland

1/14/2011 10:39:54 AM #

Sid

this is the article i was looking for to understand execute sql task

Sid India

2/9/2011 12:13:37 PM #

Surendra Yadav

i really appreciate this site and author.

I need one help in execute sql task

its conditional

if file exists
then raise error and fail the task toward fail flow
else
flow the task in success direction

i have tried to raise the error through raiseerror in the sqlstatement however its not caching and forwarding the flow to task fail direction

please help

thanks in advance

Surendra Yadav India

3/28/2011 3:31:00 PM #

Biju Koshy

Hi

Can you please tell me how we can acheive the below scenario.

1. I have a txt file with 3 values(policy, term date, Name).
2. I need to UPDATE term date field in the table WHERE policy = ???(value from txt file) AND Name = ???(value from txt file).

Pleaseee Help....

Biju Koshy United States

3/30/2011 7:16:36 AM #

someguy198650

Hi,

how do you pass the value from the variable of the first Task to the second task ?

someguy198650 Philippines

4/7/2011 3:21:16 PM #

raj


Needed help...have a DTS package, where an execute store proc is executed and then "isql" statement is written to output the results from executed storeproc to External file (text file).

My question is how should i do this in SSIS....

I have a plan of putting Storeproc in execute sql task and then from there,  have no idea how should i result them, my aim is to FTP it(results)...can anyone help me

raj Lithuania

4/29/2011 8:58:01 AM #

Iñigo

Please help me!
I've a DTS package in SQL Server 2000 and i've converted to SSIS (SQL Server 2008) but I need to know how to run it from the SQL Server Query Analizer because when I did it in SQL Server 2000 I ran it with the next transaction:

exec master.dbo.xp_cmdshell dtsrun /s (local) /e /n PACKAGENAME

Could anybody tell me how can I run the SSIS now in SQL Server 2008?

Thanks in advance

Iñigo Spain

5/1/2011 12:28:50 PM #

Piyush

@Raj,

Just add a Data FLow task and execute the procedure in OLDEDB Source. U will get the resultset which u can pass to Flat FIle Destination

@Iñigo,
U can use the same xp_cmdshell command to execute an ssis package too using dtexec.exe utility. Just goto MSDN and search for dtexec.exe and u will get the answer to execute SSIS from SSMS.

Piyush India

5/1/2011 12:31:15 PM #

Piyush

@Biju Koshy,

Use the OLE DB Command transformation in your dataflow source.

Piyush India

5/10/2011 11:02:04 PM #

Eran

Hi Robert and Glaudie

I had the same problem. The Execute SQL Task has simply "decided" not to pass the values outside. I did everything right but the variables refused to be populated by any values, without any error message.

At the end i just deleted the Execute SQL task and the variables and build it from scratch. I built exactly the same like it was before (just did copy paste to the SQL statement and setting up again new variables... it solved the problem! now, everything was right.

I don't know what has created this problem at the first time. It seems to me like a "transparent" referencing to the cache memory where something forced the PC to get stuck with the initial values and do not let the SSIS do his job... Anyway, now its fine.

It worth trying.

Eran Israel

6/3/2011 9:11:15 AM #

Rufus

Great work.

Clearly expounds about passing variable in and out of SSIS tasks.

Rufus Kenya

5/15/2012 6:51:42 AM #

Manoj Gokhale

I want to execute a stored procedure with 2 input parameters and resultset and write the sqlstatement as
=> exe  dbo.test1 ?, ?
The procedure is compiled in SQLSERVER.
When I try to "parse query". The SSIS gives the message "The query failed to parse ...". Also, the user has the permissions to execute the procedure
Help e what cud be the reason

Manoj Gokhale United States

6/27/2012 4:51:11 PM #

Giulia Prandini

Hi all,
I follow the example but when I run the project the Sql task failed,cause:

The query failed to parse. Must declare the scalar variable "@variable".

Do you know solution?

Giulia Prandini Italy

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

RecentComments

Comment RSS