SSIS package completes without errors in Visual Studio, but fails with errors in SQL

Question

Full Disclosure – not a DBA or a BI guy and we really don’t have either, so here I am. We have an issue where we can run a dtsx package to completion without errors in Visual Studio, but the package throws errors when running manually from SSMS under the SSISDB or with the SQL agent in a scheduled job. When opening the package in Visual Studio, the package produces no errors or warnings in any of the control flow or data flow tasks. I have used the same credentials when running in Visual Studio and running manually from SSMS. I’m using Visual Studio 2019 and the SQL server is running SQL 2014. I use the Project/Deploy menu in Visual Studio to deploy to the SSISDB and have verified it successfully deploys.

Here are the errors I get when running manually from SSMS or when the scheduled job runs via the SQL agent (in order of occurrence):

Load studrec_001_addr_contact_parsed:Error: The "Get spg_err and addrcln data" failed because truncation occurred, and the truncation row disposition on "Get spg_err and addrcln data.Outputs[ADO NET Source Output].Columns[addrcln_pgcon_addr_bndry_street_suffix]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

Load studrec_001_addr_contact_parsed:Error: The Get spg_err and addrcln data was unable to process the data. Pipeline component has returned HRESULT error code 0xC020902A from a method call.

Load studrec_001_addr_contact_parsed:Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Get spg_err and addrcln data returned error code 0xC02090F5. 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.

Looking at the data flow task for the Load studrec_001_addr_contact_parsed and viewing the mappings for the columns, the addrcln_pgcon_addr_bndry_street_suffix shows Length: 4 for the Input Column and Length: 20 for the Destination Column when hovering over the column name for each.

1) Why are we not seeing truncation warnings in Visual Studio if truncation is truly the issue?
2) Why does it complete from Visual Studio, but not from SQL?

Answer 1

@Brett Schapeler ,

Is the SSIS package in question connected to the same database in VS2019 vs. SSIS run-time server?

Answer 2

Thank you for the reply. I'm not sure I understand the question. When I open the solution in VS, it uses the connection managers to the database for the flow tasks. I would assume by deploying the package to the SSISDB that I'm using those same connection managers/databases. Our SSIS run-time server is on a server separate from our main database server.

Answer 3

Hi @Brett Schapeler ,

Load studrec_001_addr_contact_parsed:Error: The "Get spg_err and addrcln data" failed because truncation occurred, and the truncation row disposition on"Get spg_err and addrcln data.Outputs ***[ADO NET Source Output].Columns[addrcln_pgcon_addr_bndry_street_suffix]****" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.*

Could you please check if the length of data in the column [ADO NET Source Output].Columns[addrcln_pgcon_addr_bndry_street_suffix] is more than 4?

If so, please add the length of column in the external source.

Best regards,
Mona

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Hello, here is the preview. Thanks.

Answer 5

I've opened a case with Microsoft. I'll post the resolution here if we get one.