Text Qualifier in flat file connection

Question

Hi All,

I have business scenario as

1. source files (text files) comes to load into SQL database so I used the flat file connection manager as normal process.

2. My requirement as, we are getting source files with qualifier (") so we used the text qualifier property in the connection manager.

3. For the same set of files we are getting without text qualifiers in the files there SSIS packages failing.

Here I need information on SSIS package handle the files

• if text qualifier exists in the source data need to eliminate while loading
• if text qualifier does not exist in the source then load the data as normal process

Could we implement the both scenarios in the same package.

thanks

prav

Answer 1

You could pre-process the files using something like powershell. There's an article here talking about how to implement a file wide replace. Of course if you're comfortable with writing VB.NET you could implement the same thing in a scripting task in the package.