0

I have a query that executed fine in SSMS but gives error when I run it as a task in ssis package DTS_E _INDUCEDTRANSFORMFAILUREERROR

My query is something like this 

Select t1.*
From t1 
Inner join t2
On t1.c1 = t2.c1
And t1.date between ? And ? 
Inner join t3
on t3.c2 = t2.c2
Where 
Date between ? And ?
and c4 like ‘%name%’

I am using execute sql task, data access mode is sql command and passing 4 parameters.

SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "OLE DB Destination.Inputs[OLE DB Destination Input]" failed because error code 0xC020907B occurred, and the error row disposition on "OLE DB Destination.Inputs[OLE DB 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.

SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (2) failed with error code 0xC0209029 while processing input "OLE DB 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.

The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on OLE DB Source 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.

These are my detailed error messages

Even I thought it’s something to do with truncation but my columns are exactly same as I am just syncing data from one table to exactly the same table in another server. I created exactly the same table in server 2 from server 1

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
Data Enthusiast
  • 37
  • 1
  • 2
  • 4
  • I googled `DTS_E _INDUCEDTRANSFORMFAILUREERROR` and it seems to do with data truncation. Please post the full error. Also take a look at the column lengths of your varchar fields in the source and target. In this example, it actually tells you the column with the issue: https://stackoverflow.com/questions/13459570/ssis-cannot-convert-because-a-potential-loss-of-data – Nick.Mc Apr 19 '18 at 23:03
  • Please look at the question I linked to and also note the repeated statement in the error: _There may be error messages posted before this with more information about the failure._. Now go take another look at your log and find the error message before all of this stuff that actually tells you what the problem is. – Nick.Mc Apr 24 '18 at 00:12
  • Data Enthusiast, please use your enthusiasm and go through your old questions, make an effort to understand the answers and accept them if they are correct. – Nick.Mc Apr 24 '18 at 00:15

1 Answers1

0

Sorry about that, I thought I posted my comments but looks like I did not. Apparently there were duplicate rows in my select query, I was able to find this in SSDT after locally running the package, went to view and output, there was a detailed error message saying that there are duplicate rows and cannot insert. Thank you all for your help

Data Enthusiast
  • 37
  • 1
  • 2
  • 4