Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Ignored column in SSIS package throws conversion errors Expand / Collapse
Author
Message
Posted Thursday, December 12, 2013 10:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 25, 2014 11:43 PM
Points: 9, Visits: 40
Hello all

I'm a little new to SSIS and I need help. I have a data flow task in which I have a ADO NET source and OLE DB Destination. I have in the ADO NET source a sql command which pulls all the columns in a table. My requirement is to ignore a particular column,say column99. I opened advanced editor and deleted the mapping between the external and output columns for column99. I had also set the Error and Truncation to "Ignore Failure" for column99. I had also mapped the destination column to <Ignore> in OLD DB destination.

But this still throws the error-

Description: The ADO NET Source was unable to process the data. Field table-column99 missing an escape character for a quote.Unable to update PK WHERE clause.Error processing data batch.

How do I solve this?
Post #1522568
Posted Thursday, December 12, 2013 10:48 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 632, Visits: 1,152
Best practice is to only fetch the required columns from source its speedup things, so that you do not get into these kind of issues. Furthermore, there is no issue if there is are thousand of column in the source, you do not need to delete any of that column. just ignore the mapping of that column on the destination level and there would be any issue.

hope it helps

Post #1522571
Posted Thursday, December 12, 2013 11:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 25, 2014 11:43 PM
Points: 9, Visits: 40
I tried doing that too, excluding the column99 in the select list in the ADO NET source. But it still throws the same error.
Post #1522574
Posted Thursday, December 12, 2013 11:34 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 632, Visits: 1,152
Tanya_mj (12/12/2013)
I tried doing that too, excluding the column99 in the select list in the ADO NET source. But it still throws the same error.


what is your source type? can you share the complete error message.
Post #1522578
Posted Thursday, December 12, 2013 11:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 25, 2014 11:43 PM
Points: 9, Visits: 40
Description: The ADO NET Source was unable to process the data. Field table1-column99 is missing an escape character for a quote.Unable to update PK WHERE clause.Error processing data batch. End Error Error: 2013-12-13 03:04:39.90 Code: 0xC0047038 Source: table1 SSIS.Pipeline Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on ADO NET Source 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<c/> 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: 3:00:01 AM Finished: 3:24:42 AM Elapsed: 1481.21 seconds. The package execution failed. The step failed.,00:24:43,0,0,,,,0

Post #1522583
Posted Friday, December 13, 2013 12:04 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 632, Visits: 1,152
try following

1: Remove the Old ADO.NET Source and place the new one.
2: Select the Connection (Also test the connection).
3: Place the query, without Column99 and click the "Preview" button available in the ADO.NET Source, press that and check if you able to get the result set. (if there would be an error with the query then we will get the error over there.)
(a) if there is any WHERE Clause remove it and test the query.

4: Map the column with the destination and run the package.
Post #1522585
Posted Saturday, December 14, 2013 6:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 25, 2014 11:43 PM
Points: 9, Visits: 40
Sorry for the delay....but I followed what you'd said and it returned this:

[ADO NET Source [2]] Error: The ADO NET Source was unable to process the data. Field table-column99 is missing an escape character for a quote.Unable to update PK WHERE clause.Error processing data batch.

The thing is it runs fine a day and then fails the next. I'm not just able to somehow ignore this column.
Post #1523003
Posted Monday, December 16, 2013 12:24 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 632, Visits: 1,152
Ok, thats a news for me as well. Anyways if is running 1st time and failing on the 2nd then there is some issue.

Whats is your Source type means (FlatFile, Excel, MSSQL or Oracle db)? it the package is failing while processing the "Source". or its fails when data is pushed in the destination?
Post #1523102
Posted Monday, December 16, 2013 5:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 25, 2014 11:43 PM
Points: 9, Visits: 40
Source is data connection from a PC from which ETL is performed.

I tried various other things, like deleting the mapping between external and output columns in the ADO NET source, deleting the column99(in external/output) from input/output properties.

I also tried changing the datatype of the column to something that doesn't have any length limit (Text/NText).

But to no avail.
Post #1523172
Posted Monday, December 16, 2013 8:46 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:55 AM
Points: 1,615, Visits: 2,118
How about providing the table schema and your query against that source? It may be possible that something data-related is causing an issue based on some other part of the query.


Steve
(aka sgmunson)

Internet ATM Machine
Post #1523279
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse