April 23, 2020 at 4:33 pm
Hi All,
I found a script task code and used it however i am a novice on it.
I know the bulkcopy is throwing an error
My Excel file contains 6 tabs but i am only interested in one tab ( report ) with 80 columns which i have hard coded.
and i have created a destination table for it and the mapped to the destination but throwing errors, i have changed the destination datatype to be from decimal to NVARCHAR (100) , still have error.
Is there a way i can just load the data and dynamically create the table in destination.
The error varies as i keep changing the data types in the destination table
'Error: The given value of type String from the data source cannot be converted to type nvarchar of the specified target column'.
find attached the script file and the part below i think the issue is coming from
using (SqlBulkCopy BC = new SqlBulkCopy(myADONETConnection))
{
BC.DestinationTableName = SchemaName + "." + TableName;
foreach (var column in dt1.Columns)
BC.ColumnMappings.Add(column.ToString(), column.ToString());
BC.WriteToServer(dt1);
public void Main()
{
// TODO: Add your code here
string FolderPath = Dts.Variables["User::FolderPath"].Value.ToString();
string TableName = Dts.Variables["User::TableName"].Value.ToString();
string SchemaName = Dts.Variables["User::SchemaName"].Value.ToString();
string StartingColumn = Dts.Variables["User::StartingColumn"].Value.ToString();
string EndingColumn = Dts.Variables["User::EndingColumn"].Value.ToString();
string StartReadingFromRow = Dts.Variables["User::StartReadingFromRow"].Value.ToString();
var directory = new DirectoryInfo(FolderPath);
FileInfo[] files = directory.GetFiles();
//Declare and initilize variables
string fileFullPath = "";
//Get one Book(Excel file at a time)
foreach (FileInfo file in files)
{
fileFullPath = FolderPath + "\\" + file.Name;
//Create Excel Connection
string ConStr;
string HDR;
HDR = "YES";
ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileFullPath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
OleDbConnection cnn = new OleDbConnection(ConStr);
//Get Sheet Name
cnn.Open();
DataTable dtSheet = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetname ="Report";
foreach (DataRow drSheet in dtSheet.Rows)
{
if (drSheet["TABLE_NAME"].ToString().Contains("Report"))
{
sheetname = drSheet["TABLE_NAME"].ToString();
//Load the DataTable with Sheet Data so we can get the column header
OleDbCommand oconn = new OleDbCommand("select top 1 * from [" + sheetname + StartingColumn + StartReadingFromRow + ":" + EndingColumn + "]", cnn);
OleDbDataAdapter adp = new OleDbDataAdapter(oconn);
DataTable dt = new DataTable();
adp.Fill(dt);
cnn.Close();
//Prepare Header columns list so we can run against Database to get matching columns for a table.
string ExcelHeaderColumn = "";
string SQLQueryToGetMatchingColumn = "";
for (int i = 0; i < dt.Columns.Count; i++)
{
if (i != dt.Columns.Count - 1)
ExcelHeaderColumn += "'" + dt.Columns.ColumnName + "'" + ",";
else
ExcelHeaderColumn += "'" + dt.Columns.ColumnName + "'";
}
SQLQueryToGetMatchingColumn = "select STUFF((Select ',['+Column_Name+']' from Information_schema.Columns where Table_Name='" +
TableName + "' and Table_SChema='" + SchemaName + "'" +
"and Column_Name in (" + @ExcelHeaderColumn + ") for xml path('')),1,1,'') AS ColumnList";
// MessageBox.Show(SQLQueryToGetMatchingColumn);
//MessageBox.Show(ExcelHeaderColumn);
//USE ADO.NET Connection
SqlConnection myADONETConnection = new SqlConnection();
myADONETConnection = (SqlConnection)(Dts.Connections["NABA_Staging"].AcquireConnection(Dts.Transaction) as SqlConnection);
//Get Matching Column List from SQL Server
string SQLColumnList = "";
SqlCommand cmd = myADONETConnection.CreateCommand();
cmd.CommandText = SQLQueryToGetMatchingColumn;
SQLColumnList = (string)cmd.ExecuteScalar();
//MessageBox.Show(" Matching Columns: " + SQLColumnList);
//Use Actual Matching Columns to get data from Excel Sheet
OleDbConnection cnn1 = new OleDbConnection(ConStr);
cnn1.Open();
OleDbCommand oconn1 = new OleDbCommand("select " + SQLColumnList + " from [" + sheetname + StartingColumn + StartReadingFromRow + ":" + EndingColumn + "]", cnn1);
OleDbDataAdapter adp1 = new OleDbDataAdapter(oconn1);
DataTable dt1 = new DataTable();
adp1.Fill(dt1);
cnn1.Close();
//Load Data from DataTable to SQL Server Table.
using (SqlBulkCopy BC = new SqlBulkCopy(myADONETConnection))
{
BC.DestinationTableName = SchemaName + "." + TableName;
foreach (var column in dt1.Columns)
BC.ColumnMappings.Add(column.ToString(), column.ToString());
BC.WriteToServer(dt1);
}
}
}
}
Dts.TaskResult = (int)ScriptResults.Success;
}
#region ScriptResults declaration
/// <summary>
/// This enum provides a convenient shorthand within the scope of this class for setting the
/// result of the script.
///
/// This code was generated automatically.
/// </summary>
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
}
}
April 23, 2020 at 6:27 pm
Not sure about your script - or how you are pulling the data, but the error you are getting is because SSIS cannot automatically convert a normal string to nvarchar.
If the script is outputting a normal string - and your table definition is nvarchar, then you will need a component between the source and destination to convert the string to unicode. Or - better yet, set the data type on the table to varchar...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply