question

BrettSchapeler-2595 avatar image
0 Votes"
BrettSchapeler-2595 asked JeffZuerlein-7515 answered

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

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?

sql-server-integration-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@BrettSchapeler-2595,

What is the reason to use ADO.NET instead of the OLEDB?

0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

@BrettSchapeler-2595,

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BrettSchapeler-2595 avatar image
0 Votes"
BrettSchapeler-2595 answered YitzhakKhabinsky-0887 edited

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@BrettSchapeler-2595,

VS SSIS project could be connected to a dev. instance of the database.
SSIS Catalog, in run-time server environment could be connected to a different instance of the db. Let's say a production instance.

0 Votes 0 ·
Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft commented

Hi @BrettSchapeler-2595 ,

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.


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

If I look at the ADO.NET Source Editor for the 'Get spg_err and addrcln data' item and navigate to Columns, it shows a length of 4 when I hover over both the External Column and Output Column for addrcln_pgcon_addr_bndry_street_suffix.

0 Votes 0 ·

Hi @BrettSchapeler-2595 ,

Could you please share the data by clicking preview in the ADO.NET source?

92514-adonet-preview.png

Best regards,
Mona


0 Votes 0 ·
adonet-preview.png (28.0 KiB)
BrettSchapeler-2595 avatar image
0 Votes"
BrettSchapeler-2595 answered

Hello, here is the preview. Thanks.92645-ado-source.jpg



ado-source.jpg (27.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BrettSchapeler-2595 avatar image
0 Votes"
BrettSchapeler-2595 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JeffZuerlein-7515 avatar image
0 Votes"
JeffZuerlein-7515 answered

Did you ever find a resolution to this problem?

I’ve got the same situation, after making an alteration to the SSIS package. What is odd is that looking at the changes made to the package in change control, there are no modifications that would be relevant. The problem seems to be in the deployment process. (Visual Studio 2019).

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.