ToddFields2
asked on
What is the Syntax for Select variables for iSeries OLE Sources
Hi,
I'm getting the syntax error below when I try to use a variable for AS400 (iSeries) selects. When I type the command in directly it works fine.
I'm just surrounding the SQL in quotes and changing my OLE Db Source to variable. My variable has been set to Evaluate Expression = True.
Thanks for any help you can lend me.
Todd
AS400-Error.jpg
AS400-Variable-Select.jpg
I'm getting the syntax error below when I try to use a variable for AS400 (iSeries) selects. When I type the command in directly it works fine.
I'm just surrounding the SQL in quotes and changing my OLE Db Source to variable. My variable has been set to Evaluate Expression = True.
Thanks for any help you can lend me.
Todd
AS400-Error.jpg
AS400-Variable-Select.jpg
ASKER
Thanks Gary.
I went ahead and removed the quotes.
I'm having trouble with the OLE Db Source config. I type in the SQL command direclty without using a variable and get the error below. When I click on columns, I see the resulting columns from my select.
I had been using a data reader source, but I don't see a variable option.
Thanks,
Todd
AS400-Reader.jpg
I went ahead and removed the quotes.
I'm having trouble with the OLE Db Source config. I type in the SQL command direclty without using a variable and get the error below. When I click on columns, I see the resulting columns from my select.
I had been using a data reader source, but I don't see a variable option.
Thanks,
Todd
AS400-Reader.jpg
What happens when you click "OK"?
ASKER
I get the following error.
AS400-Codepage.jpg
AS400-Codepage.jpg
ASKER
Error Message:
Warning 1 Validation warning. Data Flow Task: OLE DB Source [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used. SSIS_ODSLoadFull.dtsx 0 0
Warning 1 Validation warning. Data Flow Task: OLE DB Source [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used. SSIS_ODSLoadFull.dtsx 0 0
That just looks like the same warning. Unless you need to do code page conversion it shouldn't matter.
ASKER
I trimmed down the source to just two fields and got an error:
[OLE DB Source [1]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E00.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
[OLE DB Source [1]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E00.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
The code page warning is normal:
http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/937d7f64-8403-4731-8d8f-f76cb9a9ee13/
Looks like the 0X80040E00 error is a know problem. IBM has a PTF:
http://www-01.ibm.com/support/docview.wss?uid=nas2a802b6eb29d32cac8625726c0041efc9
- Gary Patterson
http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/937d7f64-8403-4731-8d8f-f76cb9a9ee13/
Looks like the 0X80040E00 error is a know problem. IBM has a PTF:
http://www-01.ibm.com/support/docview.wss?uid=nas2a802b6eb29d32cac8625726c0041efc9
- Gary Patterson
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
My AS400 driver is from 2005. The new one in this solution is only good for Enterprise edition, not standard.
The latest IBM drivers are a component of the IBM iSeries Access (Client Access, System i Access, whatever) program. A media kit should have come with your system or latest OS upgrade.
- Gary Patterson
- Gary Patterson
ASKER
Thanks Gary. This verifies what IBM indicated. It still does not offer a solution to creating a dynamic query on the AS400 platform in SQL Server 2005 Standard Edition. I may just be out of luck.
Todd
Todd
Have you seen this thread? I don't have SQL 2005 and VS 2005 any more, so I can't walk you through it myself:
http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/4128e62f-92d1-4125-baa3-fdd9fed59d48
- Gary Patterson
http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/4128e62f-92d1-4125-baa3-fdd9fed59d48
- Gary Patterson
ASKER
I did review this link. I do have a working AS400 connection, I just can't create a dynamic query.
- Gary Patterson