December 12, 2013 at 10:05 pm
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?
December 12, 2013 at 10:48 pm
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
December 12, 2013 at 11:05 pm
I tried doing that too, excluding the column99 in the select list in the ADO NET source. But it still throws the same error.
December 12, 2013 at 11:34 pm
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.
December 12, 2013 at 11:52 pm
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
December 13, 2013 at 12:04 am
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.
December 14, 2013 at 6:54 pm
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.:crazy:
December 16, 2013 at 12:24 am
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?
December 16, 2013 at 5:33 am
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.
December 16, 2013 at 8:46 am
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 17, 2013 at 12:17 am
Steve, Not sure how to provide table schema.
My table structure is
[dbo].[table1](
[column1] [nvarchar](3) NULL,
[column2] [nvarchar](4) NULL,
[column3] [nvarchar](10) NULL,
[column4] [datetime] NULL,
[column5] [nvarchar](10) NULL,
[column6] [nvarchar](4) NULL,
[column7] [nvarchar](10) NULL,
[column8] [int] NULL,
[column9] [datetime] NULL,
[column10] [datetime] NULL,
[column11] [nvarchar](5) NULL,
[column12] [nvarchar](16) NULL,
[column13] [nvarchar](2) NULL,
[column14] [int] NULL,
[column15] [nvarchar](2) NULL,
[column16] [nvarchar](1) NULL,
[column17] [nvarchar](4) NULL,
[column18] [nvarchar](2) NULL,
[column19] [int] NULL,
[column20] [numeric](13, 2) NULL,
[column21] [numeric](13, 2) NULL,
[column22] [numeric](13, 2) NULL,
[column23] [numeric](13, 2) NULL,
[column24] [nvarchar](50) NULL,
[column25] [nvarchar](16) NULL,
[column26] [nvarchar](12) NULL,
[column27] [nvarchar](4) NULL,
[column28] [nvarchar](10) NULL,
[column29] [datetime] NULL,
[column30] [nvarchar](1) NULL,
[column31] [datetime] NULL,
[column32] [nvarchar](1) NULL,
[column33] [numeric](13, 2) NULL,
[column34] [numeric](13, 2) NULL,
[column35] [nvarchar](6) NULL,
[column36] [nvarchar](5) NULL,
[column37] [nvarchar](1) NULL,
[column38] [numeric](13, 2) NULL,
[column39] [numeric](13, 2) NULL,
[column40] [numeric](13, 2) NULL,
[column41] [numeric](13, 2) NULL,
[column42] [numeric](13, 2) NULL,
[column43] [numeric](13, 2) NULL,
[column44] [nvarchar](1) NULL,
[column45] [nvarchar](24) NULL,
[column46] [nvarchar](10) NULL,
[column47] [nvarchar](1) NULL,
[column48] [nvarchar](1) NULL,
[column49] [numeric](13, 2) NULL,
[column50] [nvarchar](1) NULL,
[column51] [int] NULL,
[column52] [nvarchar](10) NULL,
[column53] [nvarchar](20) NULL,
[column54] [nvarchar](4) NULL,
[column55] [numeric](13, 2) NULL,
[column56] [numeric](13, 2) NULL,
[column57] [numeric](13, 2) NULL,
[column58] [nvarchar](3) NULL,
[column59] [nvarchar](40) NULL,
[column60] [datetime] NULL,
[column61] [nvarchar](13) NULL,
[column62] [nvarchar](20) NULL,
[column63] [nvarchar](16) NULL,
[column64] [nvarchar](24) NULL,
[column65] [nvarchar](16) NULL,
[column66] [nvarchar](10) NULL,
[column67] [nvarchar](10) NULL,
[column68] [nvarchar](1) NULL,
[column69] [nvarchar](4) NULL,
[column70] [nvarchar](2) NULL,
[column71] [nvarchar](10) NULL,
[column72] [nvarchar](10) NULL,
[column73] [nvarchar](10) NULL,
[column74] [nvarchar](20) NULL,
[column75] [nvarchar](24) NULL,
[column76] [nvarchar](10) NULL,
[column77] [nvarchar](10) NULL,
[column78] [nvarchar](24) NULL,
[column79] [int] NULL,
[column80] [nvarchar](2) NULL,
[column81] [nvarchar](13) NULL,
[column99] [ntext] NULL
) ON [PRIMARY]
GO
MY query against this is
select
column1,column2,column3,column4,column5,column6,column7,column8,column9,column10,column11,column12,column13,column14,column15,column16,column17,column18,column19,column20,column21,column22,column23,column24,column25,column26,column27,column28,column29,column30,column31,column32,column33,column34,column35,column36,column37,column38,column39,column40,column41,column42,column43,column44,column45,column46,column47,column48,column49,column50,column51,column52,column53,column54,column55,column56,column57,column58,column59,column60,column61,column62,column63,column64,column65,column66,column67,column68,column69,column70,column71,column72,column73,column74,column75,column76,column77,column78,column79,column80,column81,column99 from table1
December 17, 2013 at 3:12 am
this is your destination table or source table?
December 17, 2013 at 4:00 am
Sorry, that's my destination.
December 17, 2013 at 4:06 am
sgmunson (12/16/2013)
How about providing the table schema and your query against that source?
Please also share the source table schema or query.
December 17, 2013 at 5:48 am
What is the nature of your destination? Is it an actual database table in an RDBMS, or is it something else, like an Excel spresadsheet?
There wouldn't be a query for a destination, so I'm still not sure what that query represents.. Is it the query against your source?
Finally, if your destination is a spreadsheet, why is there a column with a datatype of NTEXT?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply