SSIS Error: Violation of primary key constraint when no duplicate exists 0x80040E2F

Scenario:

  1. I have a source table with dbo.Codes with primary key combination(CodeID, CodeName).
  2. In order to create my destination table, I have used the SQL Server–>Database–>Tasks–>Generate Scripts, option.
  3. Then, in the SSIS package, I simply used the OLE DB Source and OLE DB Destination element. It was returning error: “Violation of PRIMARY KEY constraint ‘pkCodes’. Cannot insert duplicate key in object ‘dbo.Codes’. The duplicate key value is (106, da7A).”.

What I have tried to solve:

  1. I have tried to use the sql command as source: Select CodeID, CodeName from dbo.Codes GROUP BY CodeID, CodeName. It was still returning error. I got confused at this point.
  2. After searching online, I found a tips to add a SSIS Toolbox–>Common–>Sort element between OLE DB Source and OLE DB Destination. I checked the option “Remove rows with duplicate sort values” in the Sort element. I was still getting error.
  3. Then, I had enabled the data viewer between OLE DB Source and Sort element. I could see there are two rows: 106, da7a and 106, da7A in the source.

What is the real problem?

My source table c0lumn CodeName is case sensitive but my CodeName column in the destination table is not case sensitive. This has occured because, sql server generate script option–>Script Collation is set to false by default.

Solution:

I recreated my destination table with Script Collation option: True, which made my destination column case sensitive and it has solved my problem.

 

SSIS C#: Create Excel file, worksheet, header row dynamically

I need to

  1. create a excel file,
  2. which will multiple worksheets.
  3. every worksheet will have a header row.

Excel file name, worksheet name, header row column names will be provided dynamically.

ScriptMain.cs file

 

namespace ST_50e9d5ec3f17426585feb2e6ab8d41bb{  [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute] public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase {        public static StringBuilder informationLog = new StringBuilder();        //Log log;        public void Main() {
//Dts.TaskResult = (int)ScriptResults.Success;            try            {                SqlConnection sqlConnection = new SqlConnection();                sqlConnection = ConnectToDataSource(“ADONET_IntegratedCare”);
DataTable dtControl = new DataTable();                dtControl =GetControlInfo(sqlConnection);
ClearFolder(dtControl);                SQLToExcel(sqlConnection, dtControl);
//MoveFile(dtControl);                sqlConnection.Close();                dtControl.Clear();            }            catch(Exception x)            {                StringBuilder errorMessage = new StringBuilder();                Exception e = x;                while (e != null)                {                    errorMessage.AppendFormat(“Exception occured [{0}]\n”, e.Source, e.Message);                    errorMessage.AppendFormat(“Stack trace: \n{0}\n”, e.StackTrace);                    errorMessage.AppendLine();                    e = e.InnerException;                }                //log.Error(“Adhoc – exception”, errorMessage.ToString());            }        } } }

ScriptMainDataAccess.cs

using Microsoft.SqlServer.Dts.Runtime;
using System;
using System.Data;
using System.Data.SqlClient;

namespace ST_50e9d5ec3f17426585feb2e6ab8d41bb
{
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region private SqlConnection ConnectToDataSource(SqlConnection sqlConnection)
private SqlConnection ConnectToDataSource(String dataSource)
{
SqlConnection sqlConnection = new SqlConnection();
try
{
using (ConnectionManager adonetConnection = (ConnectionManager)Dts.Connections[dataSource])
{
sqlConnection = (SqlConnection)adonetConnection.AcquireConnection(Dts.Transaction);
if (sqlConnection.State == ConnectionState.Open)
{
sqlConnection.Close();
}
sqlConnection.Open();
//Log.Information(“Adhoc”, String.Format(“Connect to the data source: {0}.{1}”, sqlConnection.DataSource, sqlConnection.Database));
};
}
catch (Exception e)
{
throw e;
}
return sqlConnection;
}
#endregion

#region private DataTable GetControlInfo(SqlConnection sqlConnection)
private DataTable GetControlInfo(SqlConnection sqlConnection)
{
SqlDataAdapter da = new SqlDataAdapter();
DataSet dsControl = new DataSet();
DataTable dtControl = new DataTable();
SqlCommand sqlCommand;
try
{

String controlStatement = Dts.Variables[“$Package::ControlStatement”].Value.ToString();
String dataType = Dts.Variables[“$Package::DataType”].Value.ToString();

sqlCommand = new SqlCommand(controlStatement, sqlConnection);
da.SelectCommand = sqlCommand;
da.Fill(dsControl, “Adhoc_DataTable”);
dtControl = dsControl.Tables[“Adhoc_DataTable”];
//log.Information(“Adhoc – Information”, “Get control information from the package parameter: $Package::ControlStatement”);
}
catch (Exception e)
{
throw e;
}
return dtControl;
}
#endregion
}
}

ScriptMainFile.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;

namespace ST_50e9d5ec3f17426585feb2e6ab8d41bb
{
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region private void ClearFolder(DataTable dtControl)
private void ClearFolder(DataTable dtControl)
{
//delete all the files from a directory
try
{
foreach (DataRow dr in dtControl.Rows)
{
String path = dr[“DataProcessingDirectory”].ToString();
if (Directory.Exists(path))
{
DirectoryInfo di = new DirectoryInfo(path);
foreach (FileInfo file in di.GetFiles())
{
file.Delete();
//log.Information(“Adhoc-information”, string.Format(“Deleted file {0} “, path+file));
}
}
else
{
Directory.CreateDirectory(path);
};
}
}
catch (Exception e)
{
throw e;
}
}
#endregion

#region private void MoveFile(DataTable dtControl)
private void MoveFile(DataTable dtControl)
{
try
{
foreach (DataRow dr in dtControl.Rows)
{
String sourcePath = dr[“DataProcessingDirectory”].ToString();
String destinationPath = dr[“FilePath”].ToString();
if (Directory.Exists(destinationPath) == false)
{
Directory.CreateDirectory(destinationPath);
}
DirectoryInfo di = new DirectoryInfo(sourcePath);
foreach (FileInfo fileName in di.GetFiles())
{
if (File.Exists(destinationPath + fileName))
{
File.Delete(destinationPath + fileName);
}
fileName.MoveTo(destinationPath + fileName);
//log.Information(“Adhoc-information”, string.Format(“Move the file from {0} to {1}”, sourcePath + fileName, destinationPath + fileName));
}
}
}
catch (Exception e)
{
throw e;
}

}
#endregion
}
}

ScriptMainExcel.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Linq;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Runtime.InteropServices;

using Excel = Microsoft.Office.Interop.Excel;
using X14 = DocumentFormat.OpenXml.Office2010.Excel;
using X15 = DocumentFormat.OpenXml.Office2013.Excel;

namespace ST_50e9d5ec3f17426585feb2e6ab8d41bb
{
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
//MessageBox.Show(fileExtension);

 

#region private void SQLToExcel(SqlConnection sqlConnection, DataTable dtControl)
private void SQLToExcel(SqlConnection sqlConnection, DataTable dtControl)
{
try
{
foreach (DataRow dr in dtControl.Rows)
{
String filePath = dr[“DataProcessingDirectory”].ToString();
String workbookNamePostfix = dr[“WorkbookNamePostfix”].ToString();
String fileExtension = dr[“FileExtension”].ToString().ToLower();
String worksheetName = dr[“WorksheetName”].ToString();
String sqlStatement = dr[“SqlStatement”].ToString();
String fileName = filePath
+ workbookNamePostfix
+ DateTime.Now.ToString(“yyyyMMdd”)
+ fileExtension
;
SqlDataAdapter da = new SqlDataAdapter();
DataSet dsResult = new DataSet();
DataTable dtResult = new DataTable();
SqlCommand sqlCommand = new SqlCommand(sqlStatement, sqlConnection);
da.SelectCommand = sqlCommand;
da.Fill(dsResult, “Adhoc_Result”);
dtResult = dsResult.Tables[“Adhoc_Result”];
if (fileExtension == “.xlsx”)
{
if (File.Exists(fileName))//if file already exist
{
OpenExcelFile_OpenXml(dtResult, fileName, worksheetName);
}
else//if file not exist
{
CreateExcelFile_OpenXml(dtResult, fileName, worksheetName);
}
/*The auto-fit logic is something which is implemented by Microsoft Excel, and is not a part of the OpenXML spreadsheet format.
* Auto-fit involves measuring the width (or height) of the value in each cell and finding the maximum value.
* In order to implement auto-fit in your own code, you will have to manually measure the text.*/
ExcelStyle_InteropExcel(fileName, worksheetName);
}

}
}
catch (Exception e)
{
throw e;
}
}
#endregion

#region private void CreateExcelFile_OpenXml(DataTable dtResult, string fileName, string worksheetName)
private void CreateExcelFile_OpenXml(DataTable dtResult, string fileName, string worksheetName)
{
try
{
SpreadsheetDocument spreadsheetDocument;
WorkbookPart workbookPart;
WorksheetPart worksheetPart;
WorkbookStylesPart wbsp;
Sheets sheets;
Sheet sheet;
int rowNumber = dtResult.Rows.Count;
int columNumber = dtResult.Columns.Count;

 

// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
spreadsheetDocument = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook);

// Add a WorkbookPart to the document.
workbookPart = spreadsheetDocument.AddWorkbookPart();
workbookPart.Workbook = new Workbook();

// add styles to sheet
wbsp = workbookPart.AddNewPart<WorkbookStylesPart>();
wbsp.Stylesheet = GenerateWorkbookStylesPart1Content();

// generate rows
SheetData sheetData = CreateSheetData_OpenXml(dtResult);

// Add a WorksheetPart to the WorkbookPart.
worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
Worksheet worksheet = new Worksheet();
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;

wbsp.Stylesheet.Save();

/*set a auto filter*/
AutoFilter autoFilter = new AutoFilter() { Reference = (“A1:” + ExcelColumnNumberToName(columNumber.ToString()) + “1”) };
worksheet.Append(autoFilter);

// Add Sheets to the Workbook.
sheets = workbookPart.Workbook.AppendChild(new Sheets());

// Append a new worksheet and associate it with the workbook.
sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = worksheetName };

sheets.Append(sheet);
workbookPart.Workbook.Save();

//string cellReference = GetCellReference(@”c:\temp\test.xlsx”, “Sheet1”, 1, “Quantity”);
//Console.WriteLine(GetColumnName(cellReference)); //prints D for your example

 

// Close the document.
//spreadsheetDocument.WorkbookPart.Workbook.Save();
spreadsheetDocument.Close();

}// try

catch (Exception e)
{
throw e;
}
}
#endregion

#region private void OpenExcelFile_OpenXml(DataTable dtResult, string fileName, string worksheetName)
private void OpenExcelFile_OpenXml(DataTable dtResult, string fileName, string worksheetName)
{
try
{
SpreadsheetDocument spreadsheetDocument;
WorksheetPart worksheetPart;
Sheets sheets;
Sheet sheet;
int rowNumber = dtResult.Rows.Count;
int columNumber = dtResult.Columns.Count;

// open a spreadsheet document to modify
spreadsheetDocument = SpreadsheetDocument.Open(fileName, true);

// generate rows
SheetData sheetData = CreateSheetData_OpenXml(dtResult);

// Add a WorksheetPart to the WorkbookPart.
worksheetPart = spreadsheetDocument.WorkbookPart.AddNewPart<WorksheetPart>();
Worksheet worksheet = new Worksheet();
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;

/*set a auto filter*/
AutoFilter autoFilter = new AutoFilter() { Reference = (“A1:” + ExcelColumnNumberToName(columNumber.ToString()) + “1”) };
worksheet.Append(autoFilter);

// Add Sheets to the Workbook.
sheets = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>();
string relationshipId = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart);

// Get a unique ID for the new worksheet.
uint sheetId = 1;
if (sheets.Elements<Sheet>().Count() > 0)
{
sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}

// Append the new worksheet and associate it with the workbook.
sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = worksheetName };

sheets.Append(sheet);
//worksheetPart.Worksheet.Save();

// Close the document.
//spreadsheetDocument.WorkbookPart.Workbook.Save();
spreadsheetDocument.Close();

}// try

catch (Exception e)
{
throw e;
}
}
#endregion

#region private SheetData CreateSheetData_OpenXml(DataTable dtResult)
private SheetData CreateSheetData_OpenXml(DataTable dtResult)
{
SheetData sheetData = new SheetData();
//worksheetPart.Worksheet = new Worksheet(sheetData);

Row headerRow = new Row();

List<String> columns = new List<string>();
foreach (System.Data.DataColumn column in dtResult.Columns)
{
columns.Add(column.ColumnName);
Cell cell = new Cell() { StyleIndex = (UInt32Value)2U };
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(column.ColumnName);
//MessageBox.Show(“”);
headerRow.AppendChild(cell);
}

sheetData.AppendChild(headerRow);

foreach (DataRow dsrow in dtResult.Rows)
{
Row newRow = new Row();
foreach (String col in columns)
{
//Cell cell = new Cell();
Cell cell = new Cell() { StyleIndex = (UInt32Value)1U };
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(dsrow[col].ToString());
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
return sheetData;
}
#endregion

#region private Stylesheet GenerateWorkbookStylesPart1Content()
// Generates content of workbookStylesPart1.
private Stylesheet GenerateWorkbookStylesPart1Content()
{
Stylesheet stylesheet1 = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = “x14ac” } };
stylesheet1.AddNamespaceDeclaration(“mc”, “http://schemas.openxmlformats.org/markup-compatibility/2006&#8221;);
stylesheet1.AddNamespaceDeclaration(“x14ac”, “http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac&#8221;);

Fonts fonts1 = new Fonts() { Count = (UInt32Value)2U, KnownFonts = true };

Font font1 = new Font();
FontSize fontSize1 = new FontSize() { Val = 11D };
Color color1 = new Color() { Theme = (UInt32Value)1U };
FontName fontName1 = new FontName() { Val = “Calibri” };
FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 2 };
FontScheme fontScheme1 = new FontScheme() { Val = FontSchemeValues.Minor };

font1.Append(fontSize1);
font1.Append(color1);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontScheme1);

Font font2 = new Font();
Bold bold1 = new Bold();
FontSize fontSize2 = new FontSize() { Val = 11D };
Color color2 = new Color() { Theme = (UInt32Value)1U };
FontName fontName2 = new FontName() { Val = “Calibri” };
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering() { Val = 2 };
FontScheme fontScheme2 = new FontScheme() { Val = FontSchemeValues.Minor };

font2.Append(bold1);
font2.Append(fontSize2);
font2.Append(color2);
font2.Append(fontName2);
font2.Append(fontFamilyNumbering2);
font2.Append(fontScheme2);

fonts1.Append(font1);
fonts1.Append(font2);

Fills fills1 = new Fills() { Count = (UInt32Value)4U };

Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill() { PatternType = PatternValues.None };

fill1.Append(patternFill1);

Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill() { PatternType = PatternValues.Gray125 };

fill2.Append(patternFill2);

Fill fill3 = new Fill();

PatternFill patternFill3 = new PatternFill() { PatternType = PatternValues.Solid };
ForegroundColor foregroundColor1 = new ForegroundColor() { Theme = (UInt32Value)0U, Tint = -0.499984740745262D };
BackgroundColor backgroundColor1 = new BackgroundColor() { Indexed = (UInt32Value)64U };

patternFill3.Append(foregroundColor1);
patternFill3.Append(backgroundColor1);

fill3.Append(patternFill3);

Fill fill4 = new Fill();

PatternFill patternFill4 = new PatternFill() { PatternType = PatternValues.Solid };
ForegroundColor foregroundColor2 = new ForegroundColor() { Rgb = “FFFF0000” };
BackgroundColor backgroundColor2 = new BackgroundColor() { Indexed = (UInt32Value)64U };

patternFill4.Append(foregroundColor2);
patternFill4.Append(backgroundColor2);

fill4.Append(patternFill4);

fills1.Append(fill1);
fills1.Append(fill2);
fills1.Append(fill3);
fills1.Append(fill4);

Borders borders1 = new Borders() { Count = (UInt32Value)2U };

Border border1 = new Border();
LeftBorder leftBorder1 = new LeftBorder();
RightBorder rightBorder1 = new RightBorder();
TopBorder topBorder1 = new TopBorder();
BottomBorder bottomBorder1 = new BottomBorder();
DiagonalBorder diagonalBorder1 = new DiagonalBorder();

border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);

Border border2 = new Border();

LeftBorder leftBorder2 = new LeftBorder() { Style = BorderStyleValues.Thin };
Color color3 = new Color() { Indexed = (UInt32Value)64U };

leftBorder2.Append(color3);

RightBorder rightBorder2 = new RightBorder() { Style = BorderStyleValues.Thin };
Color color4 = new Color() { Indexed = (UInt32Value)64U };

rightBorder2.Append(color4);

TopBorder topBorder2 = new TopBorder() { Style = BorderStyleValues.Thin };
Color color5 = new Color() { Indexed = (UInt32Value)64U };

topBorder2.Append(color5);

BottomBorder bottomBorder2 = new BottomBorder() { Style = BorderStyleValues.Thin };
Color color6 = new Color() { Indexed = (UInt32Value)64U };

bottomBorder2.Append(color6);
DiagonalBorder diagonalBorder2 = new DiagonalBorder();

border2.Append(leftBorder2);
border2.Append(rightBorder2);
border2.Append(topBorder2);
border2.Append(bottomBorder2);
border2.Append(diagonalBorder2);

borders1.Append(border1);
borders1.Append(border2);

CellStyleFormats cellStyleFormats1 = new CellStyleFormats() { Count = (UInt32Value)1U };
CellFormat cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };

cellStyleFormats1.Append(cellFormat1);

CellFormats cellFormats1 = new CellFormats() { Count = (UInt32Value)4U };
CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
CellFormat cellFormat3 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyBorder = true };
CellFormat cellFormat4 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat5 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyFill = true, ApplyBorder = true };

cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
cellFormats1.Append(cellFormat4);
cellFormats1.Append(cellFormat5);

CellStyles cellStyles1 = new CellStyles() { Count = (UInt32Value)1U };
CellStyle cellStyle1 = new CellStyle() { Name = “Normal”, FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };

cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats() { Count = (UInt32Value)0U };
TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = “TableStyleMedium2”, DefaultPivotStyle = “PivotStyleMedium9” };

StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();

StylesheetExtension stylesheetExtension1 = new StylesheetExtension() { Uri = “{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}” };
stylesheetExtension1.AddNamespaceDeclaration(“x14”, “http://schemas.microsoft.com/office/spreadsheetml/2009/9/main&#8221;);
X14.SlicerStyles slicerStyles1 = new X14.SlicerStyles() { DefaultSlicerStyle = “SlicerStyleLight1” };

stylesheetExtension1.Append(slicerStyles1);

StylesheetExtension stylesheetExtension2 = new StylesheetExtension() { Uri = “{9260A510-F301-46a8-8635-F512D64BE5F5}” };
stylesheetExtension2.AddNamespaceDeclaration(“x15”, “http://schemas.microsoft.com/office/spreadsheetml/2010/11/main&#8221;);
X15.TimelineStyles timelineStyles1 = new X15.TimelineStyles() { DefaultTimelineStyle = “TimeSlicerStyleLight1” };

stylesheetExtension2.Append(timelineStyles1);

stylesheetExtensionList1.Append(stylesheetExtension1);
stylesheetExtensionList1.Append(stylesheetExtension2);

stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);

return stylesheet1;
}

#endregion

#region private void ExcelStyle_InteropExcel(String fileName, String worksheetName)
private void ExcelStyle_InteropExcel(String fileName, String worksheetName)
{
Excel.Application xlApp = null;
Excel.Workbook xlWorkBook = null;
Excel.Sheets xlSheets = null;
Excel.Worksheet xlWorksheet = null;

try
{
FileInfo file = new FileInfo(fileName);
// initialize interop excel objects
xlApp = new Excel.Application();
xlApp.DisplayAlerts = false;
xlApp.Visible = true;
xlWorkBook = xlApp.Workbooks.Open(fileName, ReadOnly: false, Editable: true);
xlSheets = xlWorkBook.Sheets;
xlWorksheet = xlWorkBook.Sheets[worksheetName];

//#################################
//create table
//xlWorksheet.Columns.ClearFormats();
//xlWorksheet.Rows.ClearFormats();
/*get the number of columns and rows in a excel file*/
//int colNumber = xlWorksheet.UsedRange.Columns.Count;
//int rowNumber = xlWorksheet.UsedRange.Rows.Count;
////Add some formatting

//Excel.Range rng1 = xlWorksheet.get_Range((Excel.Range)xlWorksheet.Cells[2, 1], (Excel.Range)xlWorksheet.Cells[1, colNumber]);
//rng1.Font.Bold = true;
//rng1.Font.ColorIndex = 3;
//rng1.Borders.ColorIndex = 1;
xlWorksheet.Columns.AutoFit();

//#################################
xlWorkBook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, false, Excel.XlSaveAsAccessMode.xlNoChange, false, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
xlWorkBook.Close();
xlApp.Quit();
}
catch (Exception e)
{
throw e;
}
finally
{
/*Release the resources. If you do not release the resources properly.
* File will be still opened by the process and it stops the user from modifying and even from opening*/
Marshal.ReleaseComObject(xlWorksheet);
Marshal.ReleaseComObject(xlSheets);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);

xlWorksheet = null;
xlSheets = null;
xlWorkBook = null;
xlApp = null;

GC.WaitForPendingFinalizers();
GC.Collect();
}
}
#endregion

#region public static int ExcelColumnNameToNumber(string input)
public static int ExcelColumnNameToNumber(string input)
{
input = input.ToUpperInvariant();
int sum = 0;
int charExcelNumber;
for (int i = 0; i < input.Length; i++)
{
/*
In excel A=1 and Z=26*. In ASCII value for character A iS 65 and Z is 122. ‘A’ and 65 can be used interchangebly.
If the input is ABC then input[0]=A, input[1]=B, input[3]=C.
Charracter respective Excel column number A=1, B=2, C=3.
The formula to convert: ABC=1*26^2 + 2*26^1 + 3*26^0.
*/
charExcelNumber = ((int)input[i] – ‘A’ + 1);
sum = sum + charExcelNumber * (int)Math.Pow(26, (input.Length – 1 – i));
}
return sum;
}
#endregion

#region private static String ExcelColumnNumberToName(String columnNumber)
private static String ExcelColumnNumberToName(String columnNumber)
{
int colNum;
Int32.TryParse(columnNumber, out colNum);
int dividend = colNum;
string columnName = String.Empty;
int modulo;

while (dividend > 0)
{
/*
In excel A=1 and Z=26*. In ASCII value for character A iS 65 and Z is 122. ‘A’ and 65 can be used interchangebly.
If the input is ABC then input[0]=A, input[1]=B, input[3]=C.
Charracter respective Excel column number A=1, B=2, C=3.
The formula to convert: ABC=1*26^2 + 2*26^1 + 3*26^0.
*/
modulo = (dividend) % 26;
columnName = Convert.ToChar(64 + modulo).ToString() + columnName;
dividend = (int)((dividend – modulo) / 26);
Console.WriteLine(modulo.ToString());
Console.WriteLine(dividend.ToString());
}
return columnName;
}
#endregion
}//class
}//namespace

 

SSIS Debug: The SSIS package dynamically created by script

I had a script task which creates a package dynamically and load data. While I was debugging, I wanted to see the dynamicapackage and open and check everything is working as it should be.

In order to see the package, follow the steps:

  1. On the package open the Variables
  2. On the Variables click on the Grid Options
    1. Check Show system variables
  3. Then find the DebugMode and set to True.

SSIS: Use regex in the Foreach File Enumerator

Regex filter for Foreach Loop

Case
The standard Foreach Loop File Enumerator only has a wildcard filter, but in some cases a regular expression filter would be more useful. See/vote this request at Microsoft Connect.Solution
At the moment I’m working on a custom File Enumerator with regular expression support, but for those who don’t want to wait or don’t want to use custom components… You could achieve the same result with a Script Task.

*update: Now also available as Custom Foreach Enumerator. *

1) Foreach Loop
For this case I will use a standard Foreach File Enumerator that fills a variable FilePath. The filter is *.* so all files will be returned.

Standard foreach loop

2) Variables
I will use two extra variables in this example: RegexFilter (string) for storing the regular expression and PassesRegexFilter (boolean) for indicating whether the filename passes the regular expression filter.

Variables

3) Script Task
Add a Script Task in front of the Data Flow Task and give it a suitable name.

Script Task

4) Add variables
Edit the Script Task and add the FilePath and RegexFilter as ReadOnlyVariables and the PassesRegexFilter as ReadWriteVariable.

Variables

5) The Script
Copy the following script to the Script Task.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
// C# Code for filtering filenames with Regex
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Text.RegularExpressions;   // Added
using System.IO;                        // Added
namespace ST_02b6595da2274d7182409fb43af929ae.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion
        public void Main()
        {
            // Get the filename from the complete filepath
            String FileName = Path.GetFileName(Dts.Variables["User::FilePath"].Value.ToString());
            // Create a regex object with the pattern from the SSIS variable
            Regex FileFilterRegex = new Regex(Dts.Variables["User::RegexFilter"].Value.ToString());
            
            // Check if it is match and return that value (boolean) to the SSIS variable
            Dts.Variables["User::PassesRegexFilter"].Value = FileFilterRegex.IsMatch(FileName);
            
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

or with VB.Net

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
' VB.Net Code for filtering filenames with Regex
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Text.RegularExpressions   ' Added
Imports System.IO                        ' Added
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
 Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
 Enum ScriptResults
  Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
  Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
 End Enum
 
    Public Sub Main()
        ' Get the filename from the complete filepath
        Dim FileName As String = Path.GetFileName(Dts.Variables("User::FilePath").Value.ToString())
        ' Create a regex object with the pattern from the SSIS variable
        Dim FileFilterRegex As Regex = New Regex(Dts.Variables("User::RegexFilter").Value.ToString())
        ' Check if it is match and return that value (boolean) to the SSIS variable
        Dts.Variables("User::PassesRegexFilter").Value = FileFilterRegex.IsMatch(FileName)
        Dts.TaskResult = ScriptResults.Success
    End Sub
End Class

6) Precedence Constraints Expression
Connect the Script Task to the Data Flow Task and add an expression that does the real filtering:
@[User::PassesRegexFilter] == true

Precedence Constraint with expression

7) The result
For testing the result, I added an other Script Task below the Data Flow Task that logs all files. My regular expression in this test case: .*[0-9]\.csv$    (all csv files that end with a number in the filename)

The result

Note 1: to gain some performance I should have changed the Foreach Loop File Enumerator wildcard from *.* to *.csv

Note 2: this method could be a bit inefficient if your file collection contains thousands of files and you only need two of them.

SSIS: Transposing rows and columns in SQL Server Integration Services

Transposing rows and columns in SQL Server Integration Services


Okay, this was a rough one. The challenge was that a customer had Excel spreadsheets where we needed to transpose the rows and columns. In other words, the spreadsheet looked like this:

But we needed the records to list Program values by date, like this:

Of course, I did that by simply copying the cells and doing a “Paste | Transpose” in Excel. But when you have several hundred spreadsheets being used by folks, that’s a nontrivial operation.
At first it looked like my only option was going to be writing a script in a Script Transform component – there’s no “transpose” component in SSIS. But every time I asked someone about this, their first response was the Pivot component, which wasn’t quite right. Today I ran across some comments on the Unpivot component, and something about the output called to me:
TechNet Unpivot Resultset
And suddenly I figured it out – I could unpivot the spreadsheet, then pivot it again on the other field. Voila! A transpose.
So let’s walk through this. I’m going to skip many of the SSIS basics – if you need help with SSIS, check out the tutorials here.
First, let’s set up our Excel spreadsheet. This is the one step you really need to do – set a table name on your data. Click and select your data range, including the headers, then type a name into the Name box in the top left:

Hit the “Enter” key then save the spreadsheet. (Note: Save as Excel 95-2003; SSIS doesn’t do Office 2007 yet)
Add a data flow task to a new SSIS project. In the data flow pane, add an Excel source pointing to the ProgramData table in the spreadsheet we just saved. If you preview the data, note the date columns have column names of F2, F3, F4 – the date values of 1/1/2007, etc don’t map as column names. So let’s go to the columns pane in the Excel source editor and give them intelligent names:

Next we’re going to add an Unpivot transformation component and link our Excel Source to it. Double-click on the Unpivot component to open the transformation editor. Then you’ll check each of the boxes to the left of each of the month columns which adds them to the “Input Column” list. For each of them you’ll enter “MeasureValue” as the Destination Column. Finally, enter “MeasureDate” as the Pivot key value column name at the bottom:

 
The output of this transform will look like this:

The next step is a bit of trickery – due to the way the Pivot transform works, we need to sort the output of the Unpivot transform so that the appropriate records will be adjacent, otherwise you’ll get a staggered output. Add a Sort Transformation component and connect the Unpivot output to it:

Configure the Sort Transform to sort by the MeasureDate column.
Now let’s add a Pivot transform and connect the output of the Sort component to it:

Now comes the really tricky part – configuring the Pivot component. Double-click on it to open the Advanced Editor. In the Component Properties tab we don’t have to change anything.
Open the Input Columns tab. Check all three available input columns. That’s all we have to do here.
Open the Input and Output Properties tab. Open up the Privot Default Input and Input Columns folder. Select “MeasureDate.” Note the LineageID number here – we’ll need that later. Set the PivotUsage to “1”. The PivotUsage indicates to SSIS how to use each column:

    • 0: Row Attribute
    • 1: Row ID
    • 2: Column ID
  • 3: Values

So MeasureValue gets a PivotUsage of 3, and note its LineageID. Program gets a 2.
Now let’s create our outputs – open up the Pivot Default Output and click on “Output Columns”
Click on the “Add Column” button at the bottom. Our first column is going to be the MeasureDate – set the name to “MeasureDate” and the SourceColumn to the LineageID of the MeasureDate input column.
Now we’re going to add the four columns for our four programs. Click on the Output Columns again (if you have “MeasureDate” highlighted when you add a column, it will be inserted above MeasureDate. There’s no way to rearrange columns once you create them. It’s not a huge deal, but if you’re aesthetically nitpicky…).
Once you have “Output Columns” selected, click the “Add Column” button. Name the new column “Program1” (no space). We’re going to have to create a column for each value we want to transpose – this is one place that makes this fragile; you’re going to have to be sure to set up your error checking in case someone adds a new Program.
This column is going to get values from any row where the Program column has the text value “Program 1” (with space). So put the LineageID for the MeasureValue input column into the SourceColumn field (this indicates where the value will come from). Then type “Program 1” in the PivotKeyValue field. (Or better yet, copy/paste from the spreadsheet itself).
Add columns for Programs 2, 3, and 4 the same way – make sure to select “Output Columns” before adding a column, and the source column will be the same for all four programs.
Now let’s add an Excel output to view the results – use the Excel Destination Editor to create a new connection manager with the default outputs after you connect the Pivot:

When you run the package, here are the results you’ll get in the Excel spreadsheet you configured as the output:

 
Note that the dates are sorted alphabetically, as the result of our sorting to group the results. If you’re inserting the output into a database, it shouldn’t matter. Alternatively you can use other transforms to translate the date column (which is currently a string) into a proper date and sort the dataset again.
Again, a word of warning that this is a bit fragile; our dates are coded in the original source and unpivot, and the programs are listed in our pivot transform – be sure to use robust error checking and reporting. But it’s definitely easier to work with and maintain than a script component!!!

SSIS – reading blank rows from Excel 2007 which are actually deleted

Folks,

We are facing a strange issue with SSIS and thought I would share the same with you and see if there are any solutions. We are developing a solution where users upload their data in the form of Excel 2007 spreadsheets to a web application interface and then, the file is loaded into an Oracle RDBMS using SSIS 2008 (SSIS called using SSIS Object Model from .NET). Now, there are specific templates provided to the users for their input files and they are using the same. However, it is being observed that when the user deletes certain rows from Excel manually and tries to load it, the file load fails. The reason for the same   is seen when the package is run in debug mode – the excel source is reading a few rows (we are assuming the recently deleted rows) as NULL values in all the columns. Now, some searching revealed at http://www.tech-recipes.com/rx/2189/excel_2007_eliminate_blank_rows/ that the following manual set of operations are resolving the problem –

1. Go to the Ribbon, and select the Home tab.

2. In the Editing section, click the small arrow next to Find & Select .

3. Select Go To Special .

4. Select the Blanks radio button and click OK.

5. All blank columns/rows will be highlighted.

6. Delete

However, this requires a user intervention and obviously, the users are not really loving it J . I just wanted to check that if there are any possible workarounds using which this can be resolved in an automated manner – like a third party component maybe. I do understand the following –

1.     Excel 2007 is not really the best file format for loading data – a tab or comma-delimited file works much better. However, that is not an option here since the users are not comfortable and this decision has been already taken after many rounds of discussion.

2.     Excel being a rich file format would have its own complexities to store the data. However, I am looking for a fix similar to “IMEX=1 ” if it exists for this case

 

Solution

Just select the rows to be deleted and rather than clicking the delete key on ur keybord, goto the rowheader in the selected section -> right click-> delete.

Actually what happens when you just do a simple delete the cell has data which is blank. Same as NULL and no value are different.

When you right click and then delete a row then the row is actually deleted and SSIS will not pick up that row.

Another option is to modify your package to ignore those rows by using ISNULL functions inside a Conditional Split within your Data Flow.  For those rows where one of the columns that you expect an actual value in ISNULL, then you direct that row to a “deleted row” output, which you don’t use.

SSIS: Continue a Foreach loop after an error

Problem

I have a SQL Server Integration Services (SSIS) package with a Foreach Loop container. Inside that container I have a task that sometimes can fail. If it fails, the loop should just continue, skip the current step and go to the next iteration. Is this possible in SSIS?

Solution
  1. setup Event Details – OnError
  2. set the system variable Propagate
  3. set the precedence constraint to completion or success based on your requirement

This tip will describe how we can implement such error handling in a Foreach loop within a SQL Server Integration Services Package. Two solutions will be presented: one using the ForceExecutionResult and MaximumErrorCount properties and one using the Propagate system variable.

SSIS Package Test Set-up

In this tip we’ll use a simple package with the following control flow:

Test Package

The Foreach container loops over a fixed set of numbers, using the Foreach Item Enumerator.

Loop configuration

At each iteration, the current value will be written to an integer variable. In the first Execute SQL Task, that variable will be used as a parameter in the following SQL statement:

DECLARE @denom INT = ?;
SELECT 1 / @denom;
WAITFOR DELAY '00:00:05'; -- wait 5 seconds so looping is better visible

The task will wait 5 seconds in each iteration, so that the looping is more apparent while debugging the package in Visual Studio. As you may have noticed, the third item of the set is the number zero, which will make the SQL statement fail with a divide by zero error. The goal of this tip is to make sure that the loop will do all 6 iterations of the loop.

The last Execute SQL Task is just a dummy task that doesn’t really do anything. It is connected to the first Execute SQL Task with an OnFailure constraint. This is done to study the effects of the solutions were going to implement in this tip.

When the package is executed without any changes, the first task will fail and the second task will be executed:

Default behaviour

Notice that also the Foreach loop container fails (and the package as well), despite all tasks and containers having the propertiesFailPackageOnFailure and FailParentOnFailure are set to False. These properties don’t seem to have any effect at all, so we won’t bother with them in this tip.

ForceExecutionResult and MaximumErrorCount Options in SSIS

Let’s start the first solution by setting the task property ForceExecutionResult to Success.

Setting the property

This property simply tells the task to succeed, no matter what it encounters. When we run the package, we get the following result:

Success?

The task itself didn’t fail, but everything else still fails. The Foreach loop container did not continue the loop as we wanted. To figure out why, we need to take a look at the logging.

Too much errors...

There we can clearly see the container and the package failed because the maximum amount of errors was reached (even though the property FailParentOnFailure is set to false everywhere). This is because errors are propagated to higher levels in the package, which we’ll examine in more detail in the next section.

The default value of the MaximumErrorCount property is 1. If we change this property on the Foreach loop container to 0 – which basically means to ignore all errors – the following result is achieved:

Success! Or is it?

In the logging we can clearly see that all iterations were performed.

6 iterations were done

However, the package still fails because the maximum amount of errors was reached. To avoid failure all together, theMaximumErrorCount on the package should also be changed.

Using the combination of ForceExecutionResult and MaximumErrorCount we can continue the loop when an error occurs. However, this makes the package and the container insensitive to other errors, which is not an ideal scenario. Arguably, you don’t even need the ForceExecutionResult property, you can just set MaximumErrorCount to 0 everywhere, but that’s not a good idea when it comes to decent error handling. Also notice that if you set ForceExecutionResult to Success, the OnFailure path is never called and the second Execute SQL Task is never executed.

The Propagate Variable in Integration Services

The second solution is a far more elegant solution to deal with errors in a loop. The problem with the first solution is that errors “bubble up” from the failing task to the higher levels (containers) right until the package level. When you check out the logging of SSIS packages, it’s possible that you see the same error message for each level in the package. This is because the error propagates through each level and each time a log message is sent. However, the propagation of the error can be stopped at the task level.

To do this, you need to add an error event handler to the first Execute SQL Task. You can do this by selecting the task and by going to the event handlers tab.

The event handlers tab

Click on the link to create the event handler. You can keep it empty. Go to the Variables pane and click on the Grid Options.

Selecting grid options in the variables pane

In the dialog, enable the system variables.

Enabling system variables

Look for the Propagate variable and set its value to False.

Disabling error propagation

This will stop errors from bubbling up to higher levels in the package. As you can see, the container and the package succeed, while the first Execute SQL Task fails and the second task is executed.

Success at last

When we look at the logging, we can verify all iterations were executed.

Success at last - logging

The third iteration still failed and an error is logged, it just didn’t crash the rest of the package.

Note that you could also put the second Execute SQL Task in the event handler, instead of using it in the control flow with the OnFailure constraint.