How to handle Special Characters of CSV file data using SSIS

Bala Narasimha Challa 466 Reputation points
2021-06-23T12:44:31.123+00:00

Hi Team,
Have a CSV input file and want to load into SQL database.
CSV file having some Special Characters is their, want to eliminate Special Characters while loading to DB. Please help on same

Sample Input data like bellow , please help on this
108654-image.png

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,446 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Olaf Helper 40,576 Reputation points
    2021-06-23T12:51:02.477+00:00

    You can use a Derived Column Transformation with the REPLACE (SSIS Expression) function to remove unwanted characters.

    0 comments No comments

  2. ZoeHui-MSFT 32,406 Reputation points
    2021-06-24T01:34:38.573+00:00

    Hi @Bala Narasimha Challa ,

    As Olaf said that you could eliminate Special Characters with derived Column expression in SSIS.

    Also you may directly import the csv to sql database with SSIS and then use the RemoveNonAlphabets function to eliminate Special Characters.

    CREATE Function [dbo].[RemoveNonAlphabets](@input VarChar(4000))  
    Returns VarChar(4000)  
    AS  
    Begin  
      
        Declare @Alphabets as varchar(50)  
        Set @Alphabets = '%[^a-z]%'  
        While PatIndex(@Alphabets, @input) > 0  
            Set @input = Stuff(@input, PatIndex(@Alphabets, @input), 1, '')  
      
        Return @input  
    End  
    

    A simple sample as shown below.

      select id,dbo.RemoveNonAlphabets([value]) as [value] from test  
    

    108727-625.jpg

    Regards,

    Zoe


    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.
    Hot issues October

    0 comments No comments