Devart Forums

We started getting the error below when trying to run our SSIS packages. It's happening across all packages using the Devart BigQuery Destination. It was all working fine until this morning. We tried on a couple machines and get the same error. We are running the latest version of the SSIS components and have tried uninstalling and reinstalling everything. Any ideas on what we can try to resolve this?


SSIS package "C:\Development\SSISOpenPackage\SSISOpenPackage\TD_GCP_COPY.dtsx" starting.
Information: 0x4004300A at sd_dg_parameters, SSIS.Pipeline: Validation phase is beginning.
Error: 0xC0047062 at sd_dg_parameters, Devart BigQuery Destination [20]: Devart.Data.BigQuery.BigQueryException (0x80004005): The underlying connection was closed: An unexpected error occurred on a receive. ---> Devart.Data.BigQuery.BigQueryException (0x80004005): The underlying connection was closed: An unexpected error occurred on a receive.
at Devart.Data.t.a[a](Boolean A_0, Int32 A_1, Func`1 A_2)
at Devart.Data.t.bm3()
at Devart.Data.t.bm3()
at Devart.Data.z.a(v A_0)
at Devart.Data.w.bm5(k A_0, Object A_1, d A_2)
at Devart.Common.Devart.Data.SqlShim1293377.i.a(l A_0, k A_1, d A_2)
at Devart.Common.Devart.Data.SqlShim1293377.l.a(d A_0)
at Devart.Common.Devart.Data.SqlShim1293377.l.b(d A_0)
at Devart.Common.Devart.Data.SqlShim1293377.i.c(d A_0)
at Devart.Common.Devart.Data.SqlShim1293377.h.bni(d A_0)
at Devart.Common.Devart.Data.SqlShim1293377.d.Open()
at Devart.Data.BigQuery.y.Open()
at Devart.SSIS.SqlShim.CommonConnectionManager.AcquireConnection(Object txn)
at Microsoft.SqlServer.Dts.Runtime.ManagedWrapper.AcquireConnection(Object txn)
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction)
at Devart.SSIS.SqlShim.BaseComponent.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction)
Error: 0xC0047017 at sd_dg_parameters, SSIS.Pipeline: Devart BigQuery Destination failed validation and returned error code 0x80004005.
Error: 0xC004700C at sd_dg_parameters, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at sd_dg_parameters: There were errors during task validation.
SSIS package "C:\Development\SSISOpenPackage\SSISOpenPackage\TD_GCP_COPY.dtsx" finished: Failure.

We were able to resolve the issue by adding a proxy server and port into the Devart Connection manager. It was very strange as the package would run fine as long as you were connect to the virtual machine (VM) that it was running on (through remote desktop). It would run in the SSDT 2017 development environment, it would run when you manually kicked off the batch script (dtexec) that runs the job, and it would even run when task scheduler kicked of the batch script on schedule. As soon as you disconnected from the VM (even if you were still logged into the VM) you would get that error on any jobs that ran after that or any jobs that were in the middle of running. There must be a change in network behavior when you are connected vs. disconnected to the VM that gets resolved when going through the proxy server. Again, all of this was working fine up until a week ago so there must have been some networking change that was implemented. Wanted to get this information into the forums in case anyone else runs into a similar issue.

All of our scheduled packages were working fine and we suddenly started getting the error below across all of our scheduled SSIS packages when they attempt to run. Our packages are scheduled to run by windows task scheduler and they kick off a batch script that uses dtexec to execute the package. When we run the packages manually through the development environment they work fine. Has anyone run into anything like this before?

Error Description: Devart.Data.BigQuery.BigQueryException (0x80004005): The underlying connection was closed: An unexpected error occurred on a send. ---> Devart.Data.BigQuery.BigQueryException (0x80004005): The underlying connection was closed: An unexpected error occurred on a send.

I looked through all of the documentation for the DevArt BigQuery components as well as the google documentation around quotas and am still struggling to understand why it is happening. There are a total of 20 million rows we are inserting with the BigQuery destination component. We have the UploadBatchSize set to the default of 31457280 which should break the data up into 30MB csv files while uploading to google cloud storage. The BatchSize was set to 314572800 which should be doing a single load of all of the 30MB csv every time its gets to 300MB of data and deleting the csv files at that point. The interesting thing is that if I monitor the google cloud storage and keep refreshing while the SSIS package is running I only ever see one file in there that keeps changing with each refresh and the file always seems to be under 1MB. That is telling me that it is doing loads more often than it should be based on the way I have the parameters set. I would expect to see between 1 and 10 csv files in there at a time each around 30MB if it was working correctly. If its doing individual loads of a single csv file under 1MB it will definitely hit the quota. Does it matter what type of source I am using? I am using an ODBC connection source set to a batch size of 100,000 records. It is also going through a data conversion control between the source and destination. Other than that its a fairly simple source to destination without anything complicated. Any idea why it wouldn't be creating larger CSV files and waiting until it get to the BatchSize set before it does the load? Would switching the source to an OLEDB or ADO.NET connection help in any way or should the function of the BigQuery Destination be totally independent of the source I am using? I also tried switching the BatchSize up to the max values it lets me enter which is 999999999. In that case it should be doing a load every 953.67MB but that didn't help either. The total size of the 20 million records in the database is 2.5GB so even when the BatchSize is set to 314572800 (300MB) it should only be doing just over 8 loads, correct?

We have a couple SSIS packages that are throwing errors when inserting data into BigQuery with the errors below. Most of our packages are running fine so these errors are specific to a couple packages:


Error Description: An exception has occurred during insert operation, the message returned from the provider is:
Quota exceeded: Your table exceeded quota for imports or query appends per table.

Error Description: System.Exception: An exception has occurred during insert operation, the message returned from the provider is:
The underlying connection was closed: An unexpected error occurred on a send.


Has anyone else also experience these errors and found a solution? We are using bulk insert.