datareader source sql command - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

datareader source sql command

Author

Topic

maihabib
Starting Member

22 Posts

Posted - 2012-05-21 : 06:39:52

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 a WHERE date in ('(DATEADD("day",+1,"@[User::date]")','(DATEADD("day",+2,"@[User::date]")')

and a.4 = 3 Group By date, a.4

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-21 : 21:40:20

you need to use command from variable option and create this query inside a variable created in ssis using expression builder. Then use this variable in datareader source

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

maihabib
Starting Member

22 Posts

Posted - 2012-05-23 : 08:42:15

I did this but the package still fails at datareader source. Here's the query in the variable:
Select
sum (A.1) '1',
sum (A.2) '2',
sum (A.3) '3',
(A.4),
(A.5) data_date

from A
where
A.5 in ("+@[User::date_string]+") and
A.4 = "+ (DT_WSTR,10)@[User::acd_id] +"
Group By
A.5,A.4

And below is the error I receive in SSIS:

[DataReader Source [1325]] Error: System.Data.Odbc.OdbcException: ERROR [42000] [Informix][Informix ODBC Driver][Informix]A syntax error has occurred. 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)

Thanks,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources