Ascii Range 32 to 126 - How do I search if a column has characters out of this range
-
Ok , so this particular issue, i am clueless.
Select top 10 ProviderFirstLineBusinessPracticeLocationAddress,LEN(ProviderFirstLineBusinessPracticeLocationAddress),
ProviderFirstLineBusinessMailingAddress, LEN(ProviderFirstLineBusinessMailingAddress)
FROM
[dbo].[NPIDATA_20170715154343]I have to send data via SSIS from a SQL server to a IDM DB2 table. The process errors out.
IThe error message says :
/*
[SSIS.Pipeline] Error: 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.
[OLE DB Source [78]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
[OLE DB Destination [2]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "OLE DB Destination.Inputs[OLE DB Destination Input]" failed because error code 0xC0209077 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.
[OLE DB Destination [2]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "OLE DB Destination.Inputs[OLE DB Destination Input]" failed because error code 0xC0209077 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.
[OLE DB Destination [2]] Error: There was an error with OLE DB Destination.Inputs[OLE DB Destination Input].Columns[ProviderFirstLineBusinessMailingAddress] on OLE DB Destination.Inputs[OLE DB Destination Input]. The column status returned was: "The value could not be converted because of a potential loss of data.".
[OLE DB Destination [2]] Error: There was an error with OLE DB Destination.Inputs[OLE DB Destination Input].Columns[ProviderFirstLineBusinessMailingAddress] on OLE DB Destination.Inputs[OLE DB Destination Input]. The column status returned was: "The value could not be converted because of a potential loss of data.".[OLE DB Destination [2]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "IBMDA400 Command" Hresult: 0x80004005 Description: "CWBZZ5014 Value of parameter PLADD1 could not be converted to the host data type.".
An OLE DB record is available. Source: "IBMDA400 Data Convert" Hresult: 0x80004005 Description: "CWBNL0107 - Converted 180 bytes, 1 errors found beginning at offset 23 (scp=13488 tcp=37 siso=1 pad=0 sl=180 tl=110) ".
An OLE DB record is available. Source: "IBMDA400 Data Convert" Hresult: 0x80004005 Description: "CWBNL0107 - Converted 90 bytes, 1 errors found beginning at offset 23 (scp=1202 tcp=37 siso=1 pad=0 sl=90 tl=110) ".
An OLE DB record is available. Source: "IBMDA400 Command" Hresult: 0x80004005 Description: "CWBZZ5014 Value of parameter BMADD1 could not be converted to the host data type.".
An OLE DB record is available. Source: "IBMDA400 Data Convert" Hresult: 0x80004005 Description: "CWBNL0107 - Converted 104 bytes, 1 errors found beginning at offset 24 (scp=13488 tcp=37 siso=1 pad=0 sl=104 tl=110) ".
An OLE DB record is available. Source: "IBMDA400 Data Convert" Hresult: 0x80004005 Description: "CWBNL0107 - Converted 52 bytes, 1 errors found beginning at offset 24 (scp=1202 tcp=37 siso=1 pad=0 sl=52 tl=110) ".
*/The ProviderFirstLineBusinessPracticeLocationAddress maps to the PLADD1 column ( Both cols have a length of 55 )
The ProviderFirstLineBusinessMailingAddressmaps to the BMADD1 column ( Both cols have a length of 55 )I suspect there is some illegal character that is causing the error. How can we find out which entry has an illegal char
-
Do not reply,.... I am ok with the following SQl that I found in some website. Works well!
So my next question:
I need to convert those illegal ( out of range ) ascii characters to a space char
Can someone help me write the UPDATE statement please ( i am thinking that creating a function might be useful )
Otherwise it is all yours..... Help me folks !
select ProviderFirstLineBusinessMailingAddress,
patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,ProviderFirstLineBusinessMailingAddress) as [Position],
substring(ProviderFirstLineBusinessMailingAddress,patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,ProviderFirstLineBusinessMailingAddress),1) as [InvalidCharacter],
ascii(substring(ProviderFirstLineBusinessMailingAddress,patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,ProviderFirstLineBusinessMailingAddress),1)) as [ASCIICode]
from npidata
where patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,ProviderFirstLineBusinessMailingAddress) >0 - mw112009 - Thursday, July 20, 2017 6:52 AM
What I mean is, thise SQl statement shows me all the illegal ascii chars int he field ProviderFirstLineBusinessMailingAddress.
So my next step is to update all those rows ( that have an illegal ascii char ) to something more meaningful ( lets say a space char )
I need your help here writing the UPDATE statement ( I guess we will need to use the replace function ? ) .. This is where I need help. -
Actually I found a function written in another post....
https://www.sqlservercentral.com/forums/topic1001736-391-1.aspxCREATE FUNCTION RemoveNonASCII
(
@nstring nvarchar(255)
)
RETURNS varchar(255)
AS
BEGINDECLARE @Result varchar(255)
SET @Result = ''DECLARE @nchar nvarchar(1)
DECLARE @position intSET @position = 1
WHILE @position <= LEN(@nstring)
BEGIN
SET @nchar = SUBSTRING(@nstring, @position, 1)
--Unicode & ASCII are the same from 1 to 255.
--Only Unicode goes beyond 255
--0 to 31 are non-printable characters
IF UNICODE(@nchar) between 32 and 255
SET @Result = @Result + @nchar
SET @position = @position + 1
ENDRETURN @Result
END
GO -
NO NO,, The above function does not work well.. So we will need your help to modify the function .....
See the output I got ( I still see illegal ascii chars )
-
The function you found doesn't remove characters in the 127-255 range. Your post says you need just 32 - 126. So have you tried changing the line in the function to use
IF UNICODE(@nchar) between 32 and 126
instead ofIF UNICODE(@nchar) between 32 and 255Sue
- Sue_H - Thursday, July 20, 2017 7:22 AM
Did that works fine... Also instead of using the UNICODE function I am using the ASCII function. It works fine....
-
Please ignore this .. This is just one extra step that I had to do in the SSIS package to make sure that I get an accurate count of how many records are there in the file.
This was a very large file having 6 million records. Have to count to make sure we accounted for all the entries.
So we can put this code inside a script object and then pass the value of [count] to a SSIS variable
string TheFile = "S:\\MIS\\Provider NPI file\\Processed\\npidata_20050523-20161009.csv";int count = 0;using (System.IO.StreamReader sr = new System.IO.StreamReader(TheFile))
{ while (sr.ReadLine() != null) count++;}
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply