SSIS Script Source Component in a ForEachLoop
-
Hi, this is not a script TASK, it is a script source component. I have a few hundred XL files to process and import, some rows I need to ignore but the "main details of the XL" I need to import.
The c# script source component I have written works fine and opens each XL file, extracts the data and passes it onto the output buffer and then into the SQL table.
BUT if an error occurs, all the rows from all files are rolled back. I can see the different data going into the landing table, but once it errors, empty table again.I have set the OLEDB destination Rows per Batch = 1 and Maximum insert commit size = 1.
I have set the transaction properties of the package, foreach loop and dataflow task to ReadCommitted and Supported.Am I missing a "commit" inside the script component to get each files contents committed to the SQL table ?
Pre-Execute opens the XL with the filename from the foreachloop
Post-execute closes the connection and releasesconnections, sets end of rowset on the outputbuffer
CreateNewOutputRows, walks through the OLEDBDataAdapter that is filled with the XL contents and processes each row, assigning values to the output buffer
public override void CreateNewOutputRows()
{
DateTime sheetDate = new DateTime();
Boolean InHeadCodeTable = false;
int rowcount = 0;try
{foreach (DataRow dr in ds.Tables[0].Rows)
{//Grab the Date of the Spreadsheet
if (dr[1].ToString().ToLower() == "date:")
{
sheetDate = Convert.ToDateTime(dr[2].ToString());
}//if headcode found and blank found then end of table
if (dr[1].ToString().ToLower() == "headcode" && !(InHeadCodeTable))
{
InHeadCodeTable = true;
}if (string.IsNullOrEmpty(dr[1].ToString()) && (InHeadCodeTable))
{
InHeadCodeTable = false;
}//MessageBox.Show(dr[1].ToString());
// if (dr[1].ToString().ToLower() != "headcode" && (!string.IsNullOrEmpty(dr[1].ToString())) && dr[1].ToString().ToLower() != "date:" && rowcount <= 50)
if (InHeadCodeTable && dr[1].ToString().ToLower() != "headcode" && rowcount <= 30)
{
TrainDetailsBuffer.AddRow();TrainDetailsBuffer.IntegratorSheetDate = sheetDate.ToString("yyyy-MM-dd HH:mm");
TrainDetailsBuffer.TrainNumber = int.Parse(dr[0].ToString());
TrainDetailsBuffer.HeadCode = dr[1].ToString();rowcount += 1;//increment teh row counter to limit to 100 rows
}
}TrainDetailsBuffer.SetEndOfRowset();
}
catch (Exception e)
{
Console.WriteLine("{0} Exception caught.", e);
bool pbCancel = false;
ComponentMetaData.FireError(0, "Opening XLS File " + this.Variables.ExcelFile.ToString(), "An error occurred: " + e.Message.ToString(), "", 0, out pbCancel);
return;
}
}I have tried to set the XL in Connection managers Tab of the script component and re-writing the c# to use that instead of creating the connection in code, but this never picks up the filename set by the foreach loop, it always uses the default value set in the variable at design time.
I have tried creating the source component as a asynch transformation with an OLEDB Excel source component, but that only reads the first file and never signifies the end of the dataflow task, so that the foreach loop can go and get the next filename.I have also tried different transaction settings within the package , dataflow and foreach loop, such as the default ones of serializable, and NotRequired, but sadly I get the same behaviour.
It's as though it is opening a transaction at the beginning of the for each loop, and any error, rolling the whole processing back.
If there is nothing I can do in code, how can I handle the errors gracefully and continue to process the next file, this ignoring files that fail to import ?
or am I missing a "commit" inside the script component to get the contents of each file committed to the SQL table ?Any help much appreciated.
Phil -
Not sure, but have you tried making an explicit transaction such that the SQL command you supply does the BEGIN TRAN followed by an INSERT that includes your SELECT from the spreadsheet data, and finally a COMMIT TRAN ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Thanks Steve, but sadly no explicit transaction declared, it's as though the foreach loop is opening a trans, then when the Dataflow errors, it rolls the lot back. It's a foreach loop with a dataflow task inside it, that's all. The DF task has a script component source, passing the data through to a landing table in SQL.
- phil.mccormack 72103 - Friday, November 9, 2018 6:43 AM
Ummm... that's not quite what I was referring to... I was suggesting that your script component perform those steps to create an explicit transaction.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Ah,,,,,, ok. So the script c# component should issue a begin tran, but I'm not connecting the script component to SQL, it opens the Excel file and process rows through to the Output buffer. The script component isn't currently performing the insert into SQL, it is passed as a dataflow to the OLE DB destination, so are you suggesting, just make the script component do the whole thing, Open XL, process rows, inset into SQL ?
If not, how can the script component do a Begin trans or a Commit after all rows of the current Excel file have been inserted ? - phil.mccormack 72103 - Wednesday, November 14, 2018 6:32 AM
Yep. that is what I was suggesting. Let the script do the whole shebang. Not much point in adding in the other pieces as you'll suddenly have control of the transaction processing, and you can then do pretty much whatever you need to do without having to learn a bunch of SSIS, or get challenged by it. The additional flexibility is tremendously useful.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply