Package fails because System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 's'

Question

My package was perfectly fine for couple of days and today whenever I tried to run it, it transferred few columns from the first file in the folder and failed.

The error that I'm getting is:

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 's'.

Unclosed quotation mark after the character string
'','C:\Users\svojnovic\Dropbox\test\2016-08-31 Race 3 Scale Sheet.csv')'.

My code in the script task is:

SqlConnection myADONETConnection = new SqlConnection();
myADONETConnection = (SqlConnection)(Dts.Connections["DBConn"].AcquireConnection(Dts.Transaction) as SqlConnection);

// Writing Data of File Into Table
int counter = 0;
string line;
// MessageBox.Show(fileName);

System.IO.StreamReader SourceFile = new System.IO.StreamReader(fileName);

while ((line = SourceFile.ReadLine()) != null)
{
    if (counter > 0)
    {
         string query = "Insert into " + TableName + " Values ('";
         query += line.Replace(FileDelimiter, "','") + "','" + fileName.Replace(SourceFolderPath,"") + "')";

         MessageBox.Show(query.ToString());

         SqlCommand myCommand1 = new SqlCommand(query, myADONETConnection);
         myCommand1.ExecuteNonQuery();
     }

     counter++;
 }

 SourceFile.Close();

 // move the file to archive folder after adding datetime to it
 File.Move(fileName, ArchiveFolder + "\\" + (fileName.Replace(SourceFolderPath, "")).Replace(FileExtension, "") + "_" + datetime + FileExtension);
 Dts.TaskResult = (int)ScriptResults.Success;

The error is telling you that your SQL code has incorrect syntax. So you're going to want to look at your SQL code. What is the runtime value of `query`? — David, Apr 23 '19 at 16:34
One or other of `TableName`, `FileDelimiter` or `SourceFolderPath` presumably contains a single quote. use parameterised queries. You won''t be able to parameterise `TableName` but the other values in the insert statement should be parameters. You should read up on SQL injection — Martin Smith, Apr 23 '19 at 16:34
I suggest having a read of the documentation on [SqlCommand.Parameters Property](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.parameters?view=netframework-4.8). Fix the injection issue, and the problem you're having goes away. — Thom A, Apr 23 '19 at 16:59

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

Why importing flat file in Script Task??

The question can be asked in a different way: What is the benefit of using SSIS if you are importing the flat file in a script task rather than Data Flow Task?

You can simply add a Data Flow Task and use a Flat File Source and an OLEDB Destination to import data from a Flat File into an SQL Table. And on the other hand this will eliminate the SQL injection risk caused by concatenating the SQL command.

SQL SERVER – Import CSV File into Database Table Using SSIS

Other methods

(1) SQL BULK INSERT

SQL Server insert from flat file

(2) SQL OPENROWSET

(3) C# parameterized query

Helpful post

What's the most efficient way to load a csv file into SQL Server 2016?

Package fails because System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 's'

1 Answers1

Why importing flat file in Script Task??

Other methods

Helpful post