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

2

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;
c#
sql
sql-server
csv
ssis
asked on Stack Overflow Apr 23, 2019 by S.Vojnovic • edited Apr 23, 2019 by Hadi

1 Answer

1

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.


Other methods

(1) SQL BULK INSERT

(2) SQL OPENROWSET

(3) C# parameterized query


Helpful post

answered on Stack Overflow Apr 23, 2019 by Hadi • edited Apr 23, 2019 by Hadi

User contributions licensed under CC BY-SA 3.0