November 16, 2011 at 8:49 am
Hi everyone,
I try to define job in sql server 2005 that execute SSIS package .
The package contain
1 )Connection to AS400 with Data Reader source through ODBC data provider and create simple select sentence.
2) Extract this select to Sql server table through the OLEDB destination .
When I run this process inside of SSIS it is work.
When I run this process through the job in SQL server I get this error :
Message
Executed as user: SQLCOGNOS\SYSTEM. ...n 9.00.4035.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 15:21:03 Error: 2011-11-16 15:21:56.40 Code: 0xC0047062 Source: RKSHAT#I_QFlow DataReader Source [1] Description: System.Data.Odbc.OdbcException: ERROR [42000] [IBM][System i Access ODBC Driver][DB2 for i5/OS] 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(Comma... The package execution fa... The step failed.
or
DataReader Source" (1)" failed validation and returned validation status "VS_ISBROKEN".
any Ideas ?
thanks
November 16, 2011 at 10:10 am
How do you have permissions to the AS400 setup? What is package encryption set at?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 16, 2011 at 12:04 pm
Without more information about the package I am purely guessing here...but I suspect you are not setting the username and/or password for this connection properly.
Are you using a config file to set these values? Sensitive information is not stored in SSIS packages, so you will have to use configurations and/or variables to set these at runtime when scheduled. (dependant on your encryption level)
Hope this helps.
Martin.
Edit: See bold text above
November 17, 2011 at 7:22 am
Hi Martin,
Thanks for aswer.
In a package properties I set EncryptSensytiveWithPassword and inserted password.
When I try to extract data from sql server to sql server the job running without problems.
I get exception only when the source is AS400.
Ruslan
November 17, 2011 at 7:39 am
vroslan (11/17/2011)
Hi Martin,Thanks for aswer.
In a package properties I set EncryptSensytiveWithPassword and inserted password.
When I try to extract data from sql server to sql server the job running without problems.
I get exception only when the source is AS400.
Ruslan
It sounds like the SQL Server to SQL Server packages are using Windows authentication which means nothing is encrypted.
The SQL Server to AS400 (or vice-versa) has to provide a user name and password which you have encrypted with the encryption password. You'll need to provide the password to decrypt the sensitive information. Check out these links:
http://support.microsoft.com/kb/918760
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply