XML Data Loading - Issue

Question

XML Data Loading - Issue

sql2k8

Hall of Fame

Points: 3455
More actions
December 19, 2018 at 5:43 am

#369729

Hi,
SQL Server:
Microsoft SQL Server 2014 (SP2-GDR) (KB4019093) - 12.0.5207.0 (X64)
Jul 3 2017 02:25:44
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
Windows :
Windows Server 2012 R2 Standard Edition 6b bit
Clustering is being Used
We have Xml Data Stored in a SQL Server database (TEST_Source) and want to load the data into another SQL Server database(Test_Destination) on the same Server.
Error Message:
Error Message:
Started: 11:48:41 AM
Error: 2018-11-29 12:07:39.81
   Code: 0x80070070
   Source: DST_Table_Archive
   Description:
End Error
Error: 2018-11-29 12:07:40.05
   Code: 0xC0208266
   Source: DST_Table_Archive
   Description:
End Error
Error: 2018-11-29 12:07:40.27
   Code: 0xC0208265
   Source: DST_Table_Archive OLEDB_Src_tTable_Source [111]
   Description: Failed to retrieve long data for column "Output".
End Error
Error: 2018-11-29 12:07:40.50
   Code: 0xC020901C
   Source: DST_Table_Archive OLEDB_Src_tTable_Source [111]
   Description: There was an error with OLEDB_Src_tTable_Source.Outputs[OLE DB Source Output] on OLEDB_Src_tTable_Source. The column status returned was: "DBSTATUS_UNAVAILABLE".
End Error
Error: 2018-11-29 12:07:40.73
   Code: 0xC0209029
   Source: DST_Table_Archive OLEDB_Src_tTable_Source [111]
   Description: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "OLEDB_Src_tTable_Source.Outputs[OLE DB Source Output]" failed because error code 0xC0209071 occurred, and the error row disposition on "OLEDB_Src_tTable_Source" 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.
End Error
Error: 2018-11-29 12:07:40.96
   Code: 0xC0047038
   Source: DST_Table_Archive SSIS.Pipeline
   Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on OLEDB_Src_tTable_Source returned error code 0xC0209029. 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.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 11:48:41 AM
Finished: 12:12:32 PM
Elapsed: 1430.55 seconds
Thank You,

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sql2k8 Hall of Fame Points: 3455 More actions · Answer 1

When the data is getting loaded from a Table to another table. Temp file location in Windows is getting filled up and SSIS package is failing.

Tried to use BlobTempStoragePath and BufferTempStoragePath in the Package and run it. It is already taking 63 GB of temp file location.What is the effective way to load the xml data for a table from one database to another database on the same server.

I dont want to fill up temp file locations and add more disk space for this kind of issue.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 2

sql2k8 - Wednesday, December 19, 2018 5:48 AM
When the data is getting loaded from a Table to another table. Temp file location in Windows is getting filled up and SSIS package is failing.
Tried to use BlobTempStoragePath and BufferTempStoragePath in the Package and run it. It is already taking 63 GB of temp file location.What is the effective way to load the xml data for a table from one database to another database on the same server.
I dont want to fill up temp file locations and add more disk space for this kind of issue.

In the destination database, create a synonym pointing to the source table in the source database, then use a simple insert into/select script/task.
😎

Bojidar Alexandrov Old Hand Points: 396 More actions · Answer 3

You have to set low (say 10000) Rows per batch and Maximum insert commit size on the OLE DB Destination.

sql2k8 Hall of Fame Points: 3455 More actions · Answer 4

I will try your options and get back to you.Thank You

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 5

I have to agree with Eirikur... Keep It Super Simple. If you must use SSIS for this, have it call a stored procedure that does what he said.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)