I am working on an ssis integration and wrote a script task in c# for automating my imports from csv file to my DBs. It works great but I need help with removing commas from strings in pipes(|aaa,aaa| or |a|,|a|) in the csv. For example "Address, city wide". I want a function that can remove that comma(,). I would paste a snippet of my code and what I have done so far.
#
region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
//using CsvHelper.Configuration;
#
endregion
namespace ST_7ce5ad6fbc104157b534f4eb484a4417 {
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain: Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase {
public void Main() {
string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
try {
//Declare Variables
string SourceFolderPath = Dts.Variables["User::SourceFolder"].Value.ToString();
string FileExtension = Dts.Variables["User::FileExtension"].Value.ToString();
string FileDelimiter = Dts.Variables["User::FileDelimiter"].Value.ToString();
string ArchiveFolder = Dts.Variables["User::ArchiveFolder"].Value.ToString();
string ColumnsDataType = Dts.Variables["User::ColumnsDataType"].Value.ToString();
string SchemaName = Dts.Variables["User::SchemaName"].Value.ToString();
SqlConnection myADONETConnection = new SqlConnection();
myADONETConnection = (SqlConnection)
(Dts.Connections["moviesdb"].AcquireConnection(Dts.Transaction) as SqlConnection);
//Reading file names one by one
string[] fileEntries = Directory.GetFiles(SourceFolderPath, "*" + FileExtension);
foreach(string fileName in fileEntries) {
//Writing Data of File Into Table
string TableName = "";
int counter = 0;
string line;
string ColumnList = "";
//MessageBox.Show(fileName);
System.IO.StreamReader SourceFile =
new System.IO.StreamReader(fileName);
while ((line = SourceFile.ReadLine()) != null) {
if (counter == 0) {
ColumnList = "[" + line.Replace("\"", "").Replace(FileDelimiter, "],[") + "]";
//MessageBox.Show(ColumnList);
//"[" + line.Replace(FileDelimiter, "],[") + "]";
TableName = (((fileName.Replace(SourceFolderPath, "")).Replace(FileExtension, "")).Replace("\\", ""));
string CreateTableStatement = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[" + SchemaName + "].";
CreateTableStatement += "[" + TableName + "]')";
CreateTableStatement += " AND type in (N'U'))DROP TABLE [" + SchemaName + "].";
CreateTableStatement += "[" + TableName + "] Create Table " + SchemaName + ".[" + TableName + "]";
CreateTableStatement += "([" + line.Replace("\"", "").Replace(FileDelimiter, "] " + ColumnsDataType + ",[") + "] " + ColumnsDataType + ")";
SqlCommand CreateTableCmd = new SqlCommand(CreateTableStatement, myADONETConnection);
CreateTableCmd.ExecuteNonQuery();
//MessageBox.Show(CreateTableStatement);
} else {
string query = "Insert into " + SchemaName + ".[" + TableName + "] (" + ColumnList + ") ";
//query += "VALUES('" + line.Replace(FileDelimiter, "','").Replace("\"", "") + "')";
//query += "VALUES('" + line.Replace(FileDelimiter, "','").Replace("\"", "").Replace("\"'\"", "") + "')";
query += "VALUES('" + line.Replace("'", "").Replace(FileDelimiter, "','").Replace("\"", "") + "')";
// 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;
}
} catch (Exception exception) {
// Create Log File for Errors
using(StreamWriter sw = File.CreateText(Dts.Variables["User::LogFolder"].Value.ToString() +
"\\" + "ErrorLog_" + datetime + ".log")) {
sw.WriteLine(exception.ToString());
Dts.TaskResult = (int) ScriptResults.Failure;
}
}
}#
region ScriptResults declaration
enum ScriptResults {
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};#
endregion
}
}
It actually does what I want and imports successfully, but I need further assistance in tweaking the code to replace commas between strings in pipes in the csv file.
