Exporting To Azure SQL from Local SQL Database Failure

2

I have a local SQL Server 2012 database that used to export tables and views to Azure SQL daily. It's been working flawlessly for 3 months.

The export tasks were created by the SQL Server Studio Import/Export Wizard. They were saved into a SQL Server SSIS package and ran daily by a SQL Server Job.

Issue I am having:

For the past few months, the export process have been failing randomly with multiple errors. It mentions that the connection has dropped. I don't think this is the problem.

I can run SELECT statements against the Azure database in SSMS with no problem. The Azure database is read by Microsoft Power BI with no problem too. So I think the problem is local to my network.

Executed as user: HIDDEN.
Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit
Copyright (C) Microsoft Corporation. All rights reserved.
Started: 10:43:37
Error: 2016-07-05 10:43:37.84
Code: 0xC0016016
Source: Azure - Table - INV1
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2016-07-05 10:45:05.82 Code: 0xC0202009 Source: Data Flow Task 1 Destination - INV1 [2] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "[DBNETLIB][ConnectionWrite (send()).]General network error. Check your network documentation.". End Error Error: 2016-07-05 10:45:05.82 Code: 0xC0209029 Source: Data Flow Task 1 Destination - INV1 [2] Description: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Destination - INV1.Inputs[Destination Input]" failed because error code 0xC020907B occurred, and the error row disposition on "Destination - INV1.Inputs[Destination Input]" 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: 2016-07-05 10:45:05.82 Code: 0xC0047022 Source: Data Flow Task 1 SSIS.Pipeline Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - INV1" (2) failed with error code 0xC0209029 while processing input "Destination Input" (15). 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. End Error Error: 2016-07-05 10:45:05.82 Code: 0xC02020C4 Source: Data Flow Task 1 Source - INV1 [97] Description: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020. End Error Error: 2016-07-05 10:45:05.82 Code: 0xC0047038
Source: Data Flow Task 1 SSIS.Pipeline Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Source - INV1 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. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:43:37 Finished: 10:45:05 Elapsed: 88.156 seconds. The package execution failed.

Sometimes it works fine, other times it fails. I've noticed that smaller tables export fine, but larger ones like the error above have about 12,000 records. It fails randomly in different amount of records.

Has anyone any experience with what is going on?

Thanks!

sql-server
ssis
sql-server-2012
azure-sql-database
asked on Stack Overflow Jul 5, 2016 by James • edited Jul 5, 2016 by Lukasz P.

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0