Problem Stement
I am trying to completely automate (via parametrization) my SSIS package. It uses the data flow that reads a .csv file and inserts its contents into SQL Server table. So, I need to do this without using the data flow task.
New setup
I have replaced the data flow task with a script task that does the same thing.
The .csv file is loaded into the DataTable object and then inserted into the destination table using SqlBulkCopy class and SqlConnection instance.
public void Main()
{
var atlas_source_application = (string)Dts.Variables["$Project::Atlas_SourceApplication"].Value;
var ssis_package_name = (string)Dts.Variables["System::PackageName"].Value;
var csv_path = (string)Dts.Variables["$Project::SVM_Directory"].Value;
var atlas_server_name = (string)Dts.Variables["$Project::AtlasProxy_ServerName"].Value;
var atlas_init_catalog_name = (string)Dts.Variables["$Project::AtlasProxy_InitialCatalog"].Value;
var connname = @"Data Source=" + atlas_server_name + ";Initial Catalog=" + atlas_init_catalog_name + ";Integrated Security=SSPI;";
var csv_file_path = @"" + csv_path + "\\" + ssis_package_name + ".csv";
try
{
DataTable csvData = new DataTable();
// Part I - Read
string contents = File.ReadAllText(csv_file_path, System.Text.Encoding.GetEncoding(1252));
TextFieldParser parser = new TextFieldParser(new StringReader(contents));
parser.HasFieldsEnclosedInQuotes = true;
parser.SetDelimiters(",");
string[] fields;
while (!parser.EndOfData)
{
fields = parser.ReadFields();
if (csvData.Columns.Count == 0)
{
foreach (string field in fields)
{
csvData.Columns.Add(new DataColumn(string.IsNullOrWhiteSpace(field.Trim('\"')) ? null : field.Trim('\"'), typeof(string)));
}
}
else
{
csvData.Rows.Add(fields.Select(item => string.IsNullOrWhiteSpace(item.Trim('\"')) ? null : item.Trim('\"')).ToArray());
}
}
parser.Close();
// Part II - Insert
using (SqlConnection dbConnection = new SqlConnection(connname))
{
dbConnection.Open();
using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
{
s.DestinationTableName = "[" + atlas_source_application + "].[" + ssis_package_name + "]";
foreach (var column in csvData.Columns)
{
s.ColumnMappings.Add(column.ToString(), column.ToString());
}
s.WriteToServer(csvData);
}
}
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
Dts.Events.FireError(0, "Something went wrong ", ex.ToString(), string.Empty, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
This setup works perfectly fine on my local computer. However, once the package is deployed on the server, the insertion part breaks since the database is nowhere to be found (at least that's what it tells me).
Therefore, I tried to imitate the visual SSIS component inside the data flow task [Destination OLE DB] that uses a connection manager.
Old Setup
OLE DB connection manager setup
This setup uses OLE DB driver with "SQL Server Native Client 11.0" provider (or simply SQLNCLI11.1), "SSPI" integrated security, "Table or view - fast load" mode of access to data. This setup works perfectly fine locally and on the server.
Desired Setup
Armed with this knowledge I have tried to use OleDbConnection and OleDbCommand classes using this stackoverflow question, but I can't see how to use these components to bulk insert data into the DB.
I have also tried to use the visual SSIS component which is called "Bulk Insert Task", but lo luck there either.
How can I possibly insert in bulk using OLE DB?
