x

datareader source sql command

Hi there,

I'm using SSIS and I'm entering a sql command in a datareader source. I want to get the data for date and date+1 I have a variable in my package @[User::date] how do I write the below command in there ?
 SELECT sum (a.1) aa
, sum (a.2) bb
, sum (a.3) cc
, a.4
, a.date data
FROM table
WHERE date in ('(DATEADD("day",+1,"@[User::date]")','(DATEADD("day",+2,"@[User::date]")')
and a.4 = 3
Group By date, a.4
more ▼

asked May 21, 2012 at 08:26 AM in Default

SQL USER gravatar image

SQL USER
12 6 6 6

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

3 answers: sort voted first

You cannot do it directly. For that you need to use "Expressions" property of the Data Flow Task.

-> Click the Data Flow Task

-> In the properties section, click the ellipsis (…) button against the Expressions

-> An Expression Editor will be prompted

-> Select [DataReader Source].[SqlComamnd] property from the drop down

-> Put the above query as the expression

Since it would need a string type as the expression to execute, and you are dealing with date, you need to use double quotes / quotes to make it work

So the query to be put as expression would be something like

"SELECT sum (a.1) aa, sum (a.2) bb, sum (a.3) cc, a.4, a.date data FROM table 
WHERE date in ( '" + (DT_WSTR, 25) DATEADD( "Day", 1,  @[User::date]  ) + 
"' , '" + (DT_WSTR, 25) DATEADD( "Day", 2,  @[User::date] ) + "')"
You may or may not face some date-time conversion issue. For that you are always welcome to seek the resolution on this same thread.
more ▼

answered May 24, 2012 at 09:44 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

I did the above suggestion and got the below error:

[DataReader Source [1325]] Error: System.Data.Odbc.OdbcException: ERROR [HY000] [Informix][Informix ODBC Driver][Informix]Invalid year in date at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader) at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute() at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper)
May 27, 2012 at 02:02 PM SQL USER

I presume, you are using Informix as the DataReader Source. So you need to either change the date-time string to a format which informix can process, OR use Informix date-time functions like TO_DATE() in which you can specify the format explicitly e.g.

TO_DATE("2012-05-28 10:24","%Y-%m-%d %H:%M")
May 28, 2012 at 10:39 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left

If your date string is a string variable then you can use the following as is:

"SELECT sum (a.1) aa , sum (a.2) bb , sum (a.3) cc , a.4 , a.date data FROM table WHERE date in ((DATEADD(day,+1,"+@[User::date]+"),(DATEADD(day,+2,"+@[User::date]+")) and a.4 = 3 Group By date, a.4 "

You do not need double quotes or single quotes around day. If you have any more problems just post.
more ▼

answered May 28, 2012 at 07:57 AM

JpFernhout gravatar image

JpFernhout
10 1 1 1

Yes, the quotes may not be needed if it is of type string, but the error will still be prompted unless the format is according to Infomix's processable format.
May 28, 2012 at 10:42 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left
Thanks. I changed the date format and it worked.
more ▼

answered May 30, 2012 at 11:56 AM

SQL USER gravatar image

SQL USER
12 6 6 6

Great..it worked for you. But could you please mark the correct answer as accepted? At the moment, no idea which one you are talking about?
May 30, 2012 at 12:21 PM Sacred Jewel
Seems pretty straight forward. It was my solution ;)
May 31, 2012 at 05:06 AM Usman Butt
(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.

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:

x946
x742

asked: May 21, 2012 at 08:26 AM

Seen: 1343 times

Last Updated: May 31, 2012 at 05:06 AM