Informix error in datareader source

Toutes les réponses

• dimanche 27 mai 2012 14:35

   

   
  

  0

  Hi there,

  I'm using SSIS and I'm entering a sql command in the expression builder of the data flow where I have a datareader source. I want to get the data for the dates of the week.

  I already got it from a table and put it in a string variable.

  So I have the dates like this in the variable name "@[User::date]"

  '5-15-2012', '5-16-2012', '5-17-2012', '5-18-2012', '5-19-2012', '5-20-2012', '5-21-2012'

   I have this  variable in my SSIS package @[User::date]

  HERE's the query for the datareader source:

  SELECT sum (a.1) aa, sum (a.2) bb, sum (a.3) cc, a.4, a.date data FROM table WHERE date in (+@[User::date] +)

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

  but the package fails at the data reader source step and I get this 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)

  
  Can you help me please?

  
  Thanks,

   

  ---

  sql user_m

  • Déplacé Vicky Song lundi 28 mai 2012 03:21 (From:Visual Studio Database Development Tools (Formerly "Database Edition Forum"))
  • Fusionné Eileen ZhaoMicrosoft Contingent Staff, Moderator vendredi 1 juin 2012 04:36 the same
  •  
  • Réponse
  • Citation

   
• dimanche 27 mai 2012 19:12

  Modérateur

   

   
  

  0

  You can't place SSIS variables directly into the SQL Statement.  You need to use Property Expressions to set a dynamic property like that.

  An expression to deliver the statement you're looking for would be something like this:

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

  I believe you're using SSIS 2005 which I don't have loaded at the moment, so my advice may be inaccurate in the details...  But it's one of a couple possibilities of how you address this:

  1. If you have an "expressions" tab in the editor for the Data Reader Source, go there, select the property most closely called "statement source", and enter your expression there.
  2. If one of the options for specifying the SQL statement in the Data Reader Source is "by variable", then create a new variable called "SQLStatement".  Set the EvaluateAsExpression property of that variable to true, and put the above expression in it.  Now use that variable in the Source.
  3. Your last resort, should the other two options not be there... click the background of the Data Flow and open the properties window (F4).  Look for a property similar to [Your Source Name].[SqlCommand] - remember it.  Select the Expressions property, click the ellipsis (...).  Pull down the property list and find that property.  Copy the expression into it.

  ---

  Talk to me now on

  • Réponse
  • Citation

   
• dimanche 27 mai 2012 21:53

   

   
  

  0

  Hi Todd,

  That's what I actually did (step 3 you suggested) but the package still fails at the data reader source step and I get this error :

  I get invalid year in the error message, do you have any resolution for this?

  [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)

   Thanks,

  ---

  sql user_m

  • Réponse
  • Citation

   
• dimanche 27 mai 2012 23:11

  Modérateur

   

   
  

  0

  OK - I was going to mention it, but figured you'd formatted the dates according to what your provider was used to.  Apparently not.

  The problem is with your list of dates.  They're formatted in an ambiguous fashion that your database can't figure out, so it fails.  You should be specifying dates in the unambiguous YYYY-MM-DD format.  See if that works...

  ---

  Talk to me now on

  • Marqué comme réponse Eileen ZhaoMicrosoft Contingent Staff, Moderator vendredi 1 juin 2012 04:34
  •  
  • Réponse
  • Citation

   
• lundi 28 mai 2012 01:39

   

   
  

  0

  Thanks,

  It's working now :)

  ---

  sql user_m

  • Réponse
  • Citation

   
• lundi 28 mai 2012 03:20

   

   
  

  0

  Hello sql user_m,

  I am moving your case to the SSIS forum so that you can get better support.

  Thanks.

  ---

  Vicky Song [MSFT]
  MSDN Community Support | Feedback to us
  

  • Réponse
  • Citation