How to handle Special Characters of CSV file data using SSIS

Bala Narasimha Challa 466 Reputation points

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

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

    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

    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)  
        Declare @Alphabets as varchar(50)  
        Set @Alphabets = '%[^a-z]%'  
        While PatIndex(@Alphabets, @input) > 0  
            Set @input = Stuff(@input, PatIndex(@Alphabets, @input), 1, '')  
        Return @input  

    A simple sample as shown below.

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




    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