June 18, 2008 at 3:06 am
Hi,
I have a problem with the OLE DB Destination.
I'm reading data from ORACLE (or somewhere, it doesn't matter) and try to insert it into a OLE Destination.
The destination has an OLE DB Connection to a SQL Server 2005 DB (Service Pack 2). I'm using data access mode "Table or view - fast load". The name of the table is the name of a synonym linking to a table on a different machine.
e.g.
Name of the synonym: Tab1
It stands for: LinkedServer.db.schema.Tab1
LinkedServer is Type SQL Server and in security a remote login is set with password (Windows Authentication caused other problems)
By hand I can insert into the synonym with SSMS using the same credentials as SSIS.
The user used for the linked server has all permissions on the table on linked server.
Calling the package results in the following error:
Error: 0xC0202009 at Data Flow Task, OLE DB Destination [298]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unspecified error".
Error: 0xC0209029 at Data Flow Task, OLE DB Destination [298]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (311)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (311)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (298) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC02020C4 at Data Flow Task, OLE DB Source [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. 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.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: 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.
All kind of advices are appreciated!
Thanks in advance
Helmut
P.S. If the synonym is linking to another table on the same machine, everything is working fine.
June 18, 2008 at 5:20 am
If the destination is a linked SQL server, why not simply use an OLE DB destination with your userid and password?
June 18, 2008 at 5:28 am
Hi pduplessis,
good question!! That is definitely working.
But I just wanted to try out if it would work with linked server.
Do you see any reasons why it's not working.
Thanks
Helmut
June 18, 2008 at 5:43 am
None that I can think of, but I wouldnt consider inserting using a linked server if I had a proper OLE DB option at my disposal
June 18, 2008 at 5:53 am
Ok,
I tried an SQL Server Destination and the following error occurred:
Unable to prepare the SSIS bulk insert for data insertion.
With this error I came to an article written by Jamie Thomson
(http://blogs.conchango.com/jamiethomson/archive/2006/08/14/4344.aspx)
There I read that the user who's performing such kind of bulk insert has to have the policy "Create global object". But in my case the Linked Server has a SQL Server Login not Windows user as security setting.
Maybe this is the reason.
I will stop trying now and follow your suggestion.
Thanks
Helmut
June 18, 2008 at 5:59 am
A SQL Destination will certainly not work with a synonym to a linked server. The SQL Destination needs to be a local SQL Server because of the way it uses the file system to bulk load into the database file. This will not work against a synonym to a linked table.
I would suspect your initial issue is a similar problem with bcp. The Fast Load option for an OLE DB Destination component is a bulk insert. I would assume (I have never tried it) that a bulk copy command cannot insert into a table on another server using a 4-part name. Remember, when you use a synonym, that is really what is happening. Don't use the fast-load option and see what happens.
June 18, 2008 at 6:17 am
Hi Michael,
I already tried it without fast load option but there's another error:
Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E37 Description: "Invalid object name ..."
But the object name should be correct because if you use "Preview" in the connection manager of OLE DB Destination the table in the linked server is shown correctly.
Thanks
Helmut
July 9, 2008 at 6:18 pm
Hello,
I get the same error.
I could copy one of the tables yes'day but I see the error again 🙁 .
I am using the SQL command to copy the data from Oracle to SQL 2005.
I can preview the data and it seems perfect.
Please help me.
SSIS package "SS_to_Staging.dtsx" starting.
Information: 0x4004300A at Populating Agency table, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Populating Agency table, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Populating Agency table, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at Populating Agency table, DTS.Pipeline: Execute phase is beginning.
Error: 0xC0202009 at Populating Agency table, Agency Source [506]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-01403: no data found".
Error: 0xC0047038 at Populating Agency table, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Agency Source" (506) 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.
Error: 0xC0047021 at Populating Agency table, DTS.Pipeline: 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.
Error: 0xC0047039 at Populating Agency table, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
Error: 0xC0047021 at Populating Agency table, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
Information: 0x40043008 at Populating Agency table, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x40043009 at Populating Agency table, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Populating Agency table, DTS.Pipeline: "component "Agency Destination" (957)" wrote 0 rows.
Task failed: Populating Agency table
Warning: 0x80019002 at SS_to_Staging: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "SS_to_Staging.dtsx" finished: Failure.
The program '[5380] SS_to_Staging.dtsx: DTS' has exited with code 0 (0x0).
Thanks in advance!
July 9, 2008 at 6:21 pm
oops, similar error and not same error!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply