SSIS Lookup issues

More
6 years 3 months ago #9927 by psimonis
psimonis created the topic: SSIS Lookup issues
When using the PGNP Native OLEDB Provider in Microsoft SQL Server Integration Services (SSIS), it shows strange behaviour.

I’m constructing a SSIS package with an OLEDB DB Source which connects to a PostgreSQL database, nothing out of the ordinary here.
This source provides the expected records from the database. All seems well.

After this step however, the records move towards a lookup Transaction. This transaction causes the SSIS Package to stop.
No errors or warnings in advance, it just stops.

When examining this package in Visual Studio 2008, the lookup transaction does show a correct preview for the results from the database.
So the lookup is able to connect to the database.

Does anyone have any idea's on how to debug or possibly even fix the lookup transaction?

Please Log in or Create an account to join the conversation.

More
6 years 3 months ago #9928 by Moderator
Moderator replied the topic: Re: SSIS Lookup issues
We are troubleshooting the issue. Will update shortly.

Please Log in or Create an account to join the conversation.

More
6 years 3 months ago #9929 by psimonis
psimonis replied the topic: Re: SSIS Lookup issues
Below the content of "SQLDUMPER_ERRORLOG.txt" created after running a package with a lookup.

(1828:16F8) 06/09/13 21:48:33, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, AdjustTokenPrivileges () completed with status (00000514)
(1828:16F8) 06/09/13 21:48:33, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters: 4 supplied
(1828:16F8) 06/09/13 21:48:33, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Parameter 1: 5652
(1828:16F8) 06/09/13 21:48:33, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Parameter 2: 0
(1828:16F8) 06/09/13 21:48:33, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Parameter 3: 0:0
(1828:16F8) 06/09/13 21:48:33, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Parameter 4: 00000000006D0B58
(1828:16F8) 06/09/13 21:48:33, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Parsed parameters:
(1828:16F8) 06/09/13 21:48:33, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ProcessID = 5652
(1828:16F8) 06/09/13 21:48:33, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ThreadId = 0
(1828:16F8) 06/09/13 21:48:33, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Flags = 0x0
(1828:16F8) 06/09/13 21:48:33, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpFlags = 0x0
(1828:16F8) 06/09/13 21:48:33, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, SqlInfoPtr = 0x00000000006D0B58
(1828:16F8) 06/09/13 21:48:33, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, DumpDir = <NULL>
(1828:16F8) 06/09/13 21:48:33, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExceptionRecordPtr = 0x0000000000000000
(1828:16F8) 06/09/13 21:48:33, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ContextPtr = 0x0000000000000000
(1828:16F8) 06/09/13 21:48:33, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExtraFile = <NULL>
(1828:16F8) 06/09/13 21:48:33, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, InstanceName = <NULL>
(1828:16F8) 06/09/13 21:48:33, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ServiceName = <NULL>
(1828:16F8) 06/09/13 21:48:33, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11 not used
(1828:16F8) 06/09/13 21:48:33, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 15 not used
(1828:16F8) 06/09/13 21:48:34, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 7 not used
(1828:16F8) 06/09/13 21:48:34, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDump completed: C:\Program Files\Microsoft SQL Server\100\Shared\ErrorDumps\SQLDmpr0003.mdmp
(1828:16F8) 06/09/13 21:48:34, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Location of module 'dbghelp.dll' : 'C:\Program Files\Microsoft SQL Server\100\Shared\dbghelp.dll'
(1828:16F8) 06/09/13 21:48:34, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, File version of module 'C:\Program Files\Microsoft SQL Server\100\Shared\dbghelp.dll' : '6.8:4.0'
(1828:16F8) 06/09/13 21:48:34, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Product version of module 'C:\Program Files\Microsoft SQL Server\100\Shared\dbghelp.dll' : '6.8:4.0'
(1828:16F8) 06/09/13 21:48:34, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Location of module 'sqldumper.exe' : 'C:\Program Files\Microsoft SQL Server\100\Shared\SQLDUMPER.EXE'
(1828:16F8) 06/09/13 21:48:34, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, File version of module 'C:\Program Files\Microsoft SQL Server\100\Shared\SQLDUMPER.EXE' : '2009.100:1600.1'
(1828:16F8) 06/09/13 21:48:34, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Product version of module 'C:\Program Files\Microsoft SQL Server\100\Shared\SQLDUMPER.EXE' : '10.50:1600.1'
(1828:16F8) 06/09/13 21:48:34, ACTION, DtsDebugHost.exe, Watson Invoke: No

Please Log in or Create an account to join the conversation.

More
6 years 2 months ago #9931 by psimonis
psimonis replied the topic: Re: SSIS Lookup issues
Any updates yet?

Please Log in or Create an account to join the conversation.

More
6 years 2 months ago #9933 by Moderator
Moderator replied the topic: Re: SSIS Lookup issues
The issue is caused by internal error when matching column identifiers. We are working on the fix.

Please Log in or Create an account to join the conversation.

More
6 years 1 month ago #9941 by psimonis
psimonis replied the topic: Re: SSIS Lookup issues
Still nothing?

Please Log in or Create an account to join the conversation.

More
6 years 1 month ago #9945 by Moderator
Moderator replied the topic: Re: SSIS Lookup issues
Sorry for delay in updating the thread. We found a workaround: setting property hasSideEffect="true" on Lookup Input Column (and a few other components) makes the package run. Could you try the workaround and see if it worked on your system?

However, we have not been able to find why the package execution failed. This issue is temporarily on hold. We will return to investigation shortly.

Please Log in or Create an account to join the conversation.

More
5 years 11 months ago #10293 by geoftak
geoftak replied the topic: Re: SSIS Lookup issues
Hello, this error is also a problem for us.

Could you give us some explaination on how to set property hasSideEffect="true", as this property seems to be read only ?

Please Log in or Create an account to join the conversation.

More
5 years 11 months ago #10311 by Moderator
Moderator replied the topic: Re: SSIS Lookup issues
Method 1 "From BIDS UI". Right mouse click on "OLE DB Destination" and click "Show Advanced Editor...". On the "Input and Output properties" tab select "OLE DB Destination Input" in the left pane. HasSideEffect will be shown in Common Properties on the right.
Method 2. Edit dstx file in notepad. Find hasSideEffects property and change it to true.

Please Log in or Create an account to join the conversation.

More
5 years 11 months ago #10316 by geoftak
geoftak replied the topic: Re: SSIS Lookup issues
Hello, thank you, but the method 1 is not possible for me as the property is read-only in the BIDS. Is there a way to make it editable ?

The method 2 is not convenient and I had problems while testing it.

Are you planning to fix the issue ?

Please Log in or Create an account to join the conversation.

More
5 years 11 months ago #10317 by Moderator
Moderator replied the topic: Re: SSIS Lookup issues
We will work with Microsoft in order to find solution.

In the meantime please use the workaround (edit dstx file to set hasSideEffects=true).

Please Log in or Create an account to join the conversation.

More
5 years 8 months ago #10728 by geoftak
geoftak replied the topic: Re: SSIS Lookup issues
Hello, any news on this issue ?

Please Log in or Create an account to join the conversation.

More
5 years 8 months ago #10735 by Moderator
Moderator replied the topic: Re: SSIS Lookup issues
I'm sorry for delay, but we had not been able to find solution yet. I will try to double check status of the issue, and will update the thread.

In the meantime please change hasSideEffects=true, as a workaround.

Please Log in or Create an account to join the conversation.

More
5 years 5 months ago #10946 by erdem.ozkan
erdem.ozkan replied the topic: Re: SSIS Lookup issues
I have the same issue. Lookup connection to PostgreSQL causes the same issue mentioned above. For now I transfer the lookup table to cache and set the connection to cache table.

I set the property hasSideEffect="true" (I did it textually since it is read only in the Lookup component editor) on Lookup Input Column, but it did not help, same problem occurs.

Please Log in or Create an account to join the conversation.

More
5 years 5 months ago #10953 by jasperkock
jasperkock replied the topic: Re: SSIS Lookup issues
Hi,

We have a somewhat different problem. We use a MsSQL DB as source and PostGres as destination. Before inserting, I want to make sure the rows do not exist via a lookup. When no rows are in the destination, the flow runs fine, but when there are already rows in the destination, it throws errors at pre-processing:

[Lookup P2S [805]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
[Lookup P2S [805]] Error: OLE DB error occurred while populating internal cache. Check SQLCommand and SqlCommandParam properties.
[Lookup P2S [805]] Information: The component "Lookup P2S" (805) failed to process the rows in the cache. The processing time was 0.125 second(s).
[SSIS.Pipeline] Error: component "Lookup P2S" (805) failed the pre-execute phase and returned error code 0xC020824E.

I changed the hasSideEffect="true", but that did not work.

I also changed to "no cache", but that returns, while processing:

[Lookup P2S [805]] Error: OLE DB error occurred while fetching parameterized row. Check SQLCommand and SqlCommandParam properties.

Please Log in or Create an account to join the conversation.

More
5 years 4 months ago #10986 by Moderator
Moderator replied the topic: Re: SSIS Lookup issues
Hi,
could you please send a test package that reproduces the issue to support at pgoledb.com?

Thank you!

Please Log in or Create an account to join the conversation.

Time to create page: 0.105 seconds
Powered by Kunena Forum