asked
SSIS 2012 Visual C# Script Task
Script is failing because my text file has a string with a single quote as follows: O'Hearn. How can I handle this in the script? I have the following: string query = "Insert into dbo." + filenameonly + "(" + columname + ") VALUES('" + line.Replace("|", "','").Replace("''", " "); It is not working. I would like to insert the value of string with quote to sql table but if not possible, I can remove it. Thanks!
@red68 What is the purpose of the pipe character manipulation? It looks like you are trying to replace it with comma. Is this correct? Or something else needs to happen? Please let me know and I will type the corrected code. Also, is there any possibility that the **filenameonly** has some unwanted characters in it?
Assuming that the pipe character needs to be replaced with comma, and the single quote remained as a part of the value as is, the C# code should read something like this: string query = "Insert into dbo.[" + filenameonly + "] (" + columname + ") VALUES('" + line.Replace("|", ",").Replace("'", "''") + "')"; The idea is that every single quote needs to be replaced with two of them for T-SQL to be valid. Also, there is no need to add the single quotes around the pipe character in case if it needs to be replaced with just comma. The filenameonly variable is best if wrapped into brackets just in case if it has space in it or something which may render the table name to need brackets. It goes without saying that generating the SQL script like this is not safe as it becomes open to SQL injections. ***Edit*** It looks like I missed the reason why you have the pipe character replaced with **','**. This needs to happen because the line comes in as a pipe-delimited string. If this is the case then replacing the occurrences of the single quote with 2 such quotes needs to happen first. After that the replace of the pipe with **','** needs to take place. Here is the updated code: string query = "Insert into dbo.[" + filenameonly + "] (" + columname + ") VALUES('" + line.Replace("'", "''").Replace("|", "','") + "')"; If the original value of **line** is **1|Evette O'Hearn| 2038 Fairfax Rd||Toledo|OH|43613** then the value of the **query** becomes Insert into dbo.[Edit1] ([Uniseq] ,[Fullname] ,[Address1] ,[Address2] ,[City] ,[State] ,[Zip]) VALUES ('1','Evette O''Hearn',' 2038 Fairfax Rd','','Toledo','OH','43613') This is a valid insert which should work. It looks like some values might benefit from trimming but I am not sure whether this is needed or not. Hope this helps. Oleg
Thanks, I will give this a shot. Actually, using Script Task in SSIS to load all pipe delimited files dynamically by reading header record to create table and then load data. we receive a lot of files and ssis metadata is static meaning you have to update it if column names change, are added or removed. Trying to find a way to load without a lot of effort.
Here is the insert that it builds but errors out b/c of the single quote in the name "O'Hearn". It is still not being handled correctly. INSERT INTO [dbo].[Edit1] ([Uniseq] ,[Fullname] ,[Address1] ,[Address2] ,[City] ,[State] ,[Zip]) VALUES ('1,Evette O'Hearn, 2038 Fairfax Rd,,Toledo,OH,43613')
@red68 It looks like I missed the reason why you have the pipe character replaced with **','**. This needs to happen because the line comes in as a pipe-delimited string. If this is the case then replacing the occurrences of the single quote with 2 such quotes needs to happen first. After that the replace of the pipe with **','** needs to take place. I modified the answer, it should work now assuming that the line is a pipe-delimited string without leading or trailing pipes.
18 People are following this question.
