SQL Query: Writing update statement in a new way – Matthew Jeffries

UPDATE [ED] SET [ED].[NNumber] = [P].[NNumber]

FROM #El AS [ED]

OUTER APPLY

(

SELECT TOP 1 [N].[NNumber]

FROM [In].[temp].[NumberMAP] AS [SK]

INNER JOIN [In].[temp].[NumberMap2] AS [N]

ON [SK].GUID_ID=[N].GUID_ID

WHERE [SK].[PID] = [ED].[PID]

) AS [P];

Advertisements

Visual Studio: C# Debugging is not working

 

 

Understanding symbol files and Visual Studio’s symbol settings

Symbols are a fundamental requirement for debugging and other diagnostic tools. Fortunately in most cases when you are building and launching your application in Visual Studio you don’t have to think about symbols for your code. However the odds are that at some point in time you’ll need to change how symbols load, where the debugger looks for them, or will need to load symbols for a 3rd party component (e.g. Windows or .NET libraries). Additionally because symbols are so fundamental to debugging, we continue to make tweaks to the experience so understanding the ins and outs of how Visual Studio behaves can save you hours of frustration.

In this blog post I’ll walk you through what symbols are and how to configure Visual Studio’s symbol settings (which are used by other diagnostic tools beyond the debugger such as the performance tools and IntelliTrace), the various knobs available when debugging, and how to trouble shoot issues when Visual Studio isn’t able to find the symbol files that you need.

Symbol basics

Before we delve into the details of symbol files it’s important to briefly review what symbols are and why they are important:

  • What is a symbol file? For the Microsoft compilers, these are the .pdb files that are produced as part of your build.
  • What is in a symbol (.pdb) file? The exact contents of symbol files will vary from language to language and based on your compiler settings, but at a very high level they are the record of how the compiler turned your source code into machine code that the processor executes.
  • Why do I need symbols? Without symbols, tools are unable to correlate the instructions executing in the application to the original source code.
    • When debugging, without a symbol file you are unable to set breakpoints on a specific line of code. If symbols are not loaded you will see a hollow circle with a warning symbol while in debug mode, and if you hover the mouse over it a tooltip will tell you that the breakpoint will not be hit because no symbols have been loaded.
    • Depending on what you are debugging, symbols may be required to show you a complete call stack and to inspect objects using the Watch windows, or DataTips (e.g. this is true for C++).
    • Note: If you are debugging a dump file that does not contain the heap, the debugger will need access to the original binary file so it can determine the correct symbol file to load. Said another way, if you are debugging a dump with no heap information, you need both the corresponding binary and symbol file on the symbol path.

clip_image001

Visual Studio’s default behavior

Before we look at any of Visual Studio’s advanced settings it’s important that I stop and review the default behavior (meaning if you never touch a setting how will it behave):

  • Visual Studio will try to load symbols for all binaries (referred to as “modules”) in the process when the module is loaded (and for all modules already loaded when attaching to a process).
    • The exception to this is when you are debugging managed (.NET) applications, the debugger will not load symbols for any binaries considered “not your code” when “Just My Code” is enabled.
  • No symbol locations are set, so it will not find symbols for any Microsoft runtime binaries
    • If you right click on a module in the Call Stack or Modules windows and choose to load symbols it will automatically try to get them from the Microsoft public symbol servers assuming it can’t be found on your local machine.
  • Visual Studio will always find symbols when:
    • The symbol file is located in the same folder as its corresponding module. The default build output settings for Visual Studio projects will output the symbols next to the binaries. This means that Visual Studio will always be able to find the symbols for your projects.
    • The symbol file is located in the same directory is was placed during compilation. The full path of the .pdb is placed into the binary at build time.

How can I tell if a symbol is loaded and if not why?

The screenshot above (with the hollow breakpoint) shows a situation where symbols didn’t load for a source file you are trying to set a breakpoint in. The other ways to determine if symbols are loaded:

  • A message will appear in the call stack window saying that symbols are not loaded

clip_image003

  • The Modules window will tell you (Debug -> Windows -> Modules):
    • The status of the symbol file (loaded, skipped, or couldn’t be opened or found)
    • Path the binary is loaded from
    • [if loaded] where the symbol file was loaded from
    • The module version
    • The module’s time stamp

clip_image005

Additionally the debugger can tell you why it didn’t load symbols and where it searched for them. To see this information, open the Modules window, right click on a module and choose “Symbol Load Information…”

clip_image007

This will display a box that shows you all the paths the debugger searched for the symbol file.

clip_image009

Some common reasons symbols aren’t loaded include:

  • Symbol paths don’t point to the correct location
  • The symbol file is from a different version of the module than the one loaded in the process
    • Visual Studio requires that the symbol file come from the exact same build as the module. It cannot load symbols that come from a different build even if the source code was identical
  • [Managed only] Just My Code settings prevent the debugger from loading the symbol file

Configuring Visual Studio’s settings

Now that you understand what symbols are, and how to determine if they are loaded let’s look at how you configure Visual Studio’s symbol settings. To access symbols settings, go to the “Debug” menu and choose “Options…” (“Options and Settings…” in previous versions of Visual Studio), and then select the “Symbols” sub page

clip_image011

You’ll notice the following settings on the page:

    1. Symbol file (.pdb) locations
    2. Symbol cache settings
    3. “Load all symbols” button

Autom

  1. Automatic symbol loading settings

Symbol file locations

If you are building and debugging your application from Visual Studio this option likely won’t apply to the symbols for your modules, but remote symbol locations (or symbol servers) are used to load symbols in situations where you need a 3rd party symbol file (e.g. one from Microsoft), or you are working in an environment where you may not have the symbols on your local machine (e.g. your application is built using a build server.  If you are using TFS read about how to add symbol and source archiving support).

The symbol file location box tells the debugger where to look for symbol files, these can be http symbol servers (e.g. the prepopulated “Microsoft Symbol Severs” entry), network shares, or folders on your local machine

  • You can add as many paths as you need.
  • There is a pre-populated entry for Microsoft’s public symbol servers. If you want to load symbols for modules from Microsoft (e.g. for the runtime or operating system) check this box.
  • Visual Studio will search local paths before querying network paths regardless of the order provided.
  • For performance reasons, beginning in Visual Studio 2012 Update 1, Visual Studio will only search each symbol server once for a symbol file in a given Visual Studio session (until you restart Visual Studio) when automatic symbol loading is enabled. This means you don’t pay the cost of a network call every time you start debugging when the server doesn’t contain the file.
  • Environment Variable: _NT_SYMBOL_PATH: If you see this in your symbol file locations it means that the environment variable _NT_SYMBOL_PATH is set. Visual Studio uses a library from Windows to load symbols, and the library will always search any locations in this environment variable for symbols; which is why you cannot uncheck the option in Visual Studio. You will need to unset the environment variable if you want Visual Studio to ignore the variable.
    If you need the environment variable for other purposes, the easy way to unset the variable locally is to open a command prompt, enter “set _NT_SYMBOL_PATH=” and then launch Visual Studio from the command prompt. You system’s environment settings will remain unaffected.

Symbol cache

The symbol cache is the location on your local machine that Visual Studio places a copy of the symbols it finds on remote locations for future use. Assuming you provide a path for the symbol cache, Visual Studio will search the cache before trying to find symbols in any symbol file locations you specified above. For performance reasons we recommend specifying a symbol cache if you need symbols stored in a remote location.

Load All Symbols

This button is only enabled while Visual Studio is in debug mode, and clicking it will tell the debugger to try to load symbols for all modules in the process.

Automatic Symbol Loading

Visual Studio offers two modes of automatic symbol loading:

  • Automatically load symbols for all modules unless excluded: As the title indicates, unless you add a module to the exclude list by clicking “Specify excluded modules”, Visual Studio will try to load symbols for all modules in the process. You will typically want this setting if you want symbols loaded for almost everything in the process, or if there are only a handful of very large ones you don’t want loaded for memory or debug startup performance reasons.
  • Only specified modules: This setting by default will load symbols that are next to the binary on the disk, but will not try to load symbols for any other modules unless you add them to the include list by clicking “Specify modules”.
    • beginning with Visual Studio 2013 Update 2, the “Specify modules” dialogs accept * for module names. So if for example you wanted to use manual loading but always load symbols for anything with “Microsoft” in the name, you could enter “*Microsoft*”

clip_image013

  • Symbols can be manually loaded from the Call Stack window as needed. To do this, you can select an individual frame (or select all with ctrl+a), right click and choose “Load symbols”. This will load symbols for all of the modules that were in the call stack window at that time. If loading symbols improves the call stack and additional modules are found you will need to repeat this as it won’t automatically try to load symbols for modules that appear due to the previous load.

clip_image015

  • The other option to load symbols manually when you are debugging is to locate the module in the Modules window (Debug -> Windows -> Modules), right click and choose “Load Symbols”.

clip_image016

Deep dive on manual symbol loading

It is worth calling out that “Only specified modules” is my and many of the Visual Studio team’s preferred setting when debugging. The reason for this is:

  • When debugging very large applications you can load symbols on demand as you need them. This helps with:
    • The performance of your debug session—you don’t have to wait for symbols to load for everything in the process you are debugging.
    • Visual Studio’s memory—if you are debugging a very large application you may need to selectively load symbols for only the modules you are interested in. Since Visual Studio is a 32bit process, it can at most grow to 4 GB in Virtual Memory. For very large applications you can have more in symbol files than this.
  • You can leave your symbol servers enabled without encountering unexpected performance hits when debugging new applications or during your first debug session in a new Visual Studio instance.

If you have a very large solution that you build entirely on your machine you will need to uncheck the “Always load symbols located next to modules” checkbox to see the benefits I mentioned above. Then you will either need load the symbols on demand while debugging, or set the ones you need to automatically load.

  • If you need to hit breakpoints in those modules you will want to set them to load automatically.
  • If you aren’t sure you will need the symbols ahead of time you will want to wait and load them only if you need them to inspect an object or complete the call stack.

Conclusion

Keeping track of symbols and configuring your settings correctly and for optimal performance can be quite complicated. Hopefully the above content helps you understand the settings available to you. However if you run into issues I did not cover, or have any other feedback you’d like to share, please let me know below, through Visual Studio’s Send a Smile feature, or in our MSDN forum.

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.

 

SQL Query: Schedule multiple jobs to run over night

I had about 40 sql agent jobs. I had to schedule them to run one time only over night. It was a bit of hectic to open every job, change the ownership, modify the schedule time, then change the ownership back to the service account. In order to minimize the work effort, I have written a script based on CURSOR. Where I will have a control table, it will define the job name, schedule name, schedule start date, schedule start time. I will read this from the control table and the change the schedule accordingly.

 

USE msdb;USE msdb;go
/*create the control table and insert some dummy data.In order to make this script work, there has to be job exist defined in the control table.*/
CREATE TABLE [ETL].[SqlJobControl]( [SqlJobControlID] [int] IDENTITY(1,1) NOT NULL, [job_id] [int] NULL, [job_name] [nvarchar](200) NULL, [schedule_name] [nvarchar](50) NULL, [job_description] [nvarchar](100) NULL, [active_start_date] [int] NULL, [active_start_time] [int] NULL, [owner_login_name_user] [nvarchar](50) NULL, [owner_login_name_service_account] [nvarchar](50) NULL, [enabled] [bit] NULL, CONSTRAINT [PK__SqlJobCo__D32BB6AA03DE9823] PRIMARY KEY CLUSTERED ( [SqlJobControlID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
GOSET IDENTITY_INSERT [ETL].[SqlJobControl] ON
INSERT [ETL].[SqlJobControl] ([SqlJobControlID], [job_id], [job_name], [schedule_name], [job_description], [active_start_date], [active_start_time], [owner_login_name_user], [owner_login_name_service_account], [enabled]) VALUES (1, NULL, N’Developer_Test_Job1′, N’OneOff’, N’Dummy job’, 20170613, 175500, N’blue\ahmedma’, N’ELC\srvc.DWHSQLAgent’, 1)INSERT [ETL].[SqlJobControl] ([SqlJobControlID], [job_id], [job_name], [schedule_name], [job_description], [active_start_date], [active_start_time], [owner_login_name_user], [owner_login_name_service_account], [enabled]) VALUES (2, NULL, N’Developer_Test_Job2′, N’OneOff’, N’Dummy job’, 20170614, 180000, N’blue\ahmedma’, N’ELC\srvc.DWHSQLAgent’, 1)SET IDENTITY_INSERT [ETL].[SqlJobControl] OFF;
DECLARE  @job_id int, @job_name nvarchar(200), @schedule_name nvarchar(50), @job_description nvarchar(100), @active_start_date int, @active_start_time int, @owner_login_name_user nvarchar(50), @owner_login_name_service_account nvarchar(50), @enabled bit
;
DECLARE @oneoff_sqljob_cursor AS CURSOR;
SET @oneoff_sqljob_cursor = CURSOR FAST_FORWARD FOR SELECT  [job_id] ,[job_name] ,[schedule_name] ,[job_description] ,[active_start_date] ,[active_start_time] ,[owner_login_name_user] ,[owner_login_name_service_account] ,[enabled] FROM [UNIFY2].[ETL].[SqlJobControl] WHERE [enabled]=1;
OPEN @oneoff_sqljob_cursor;FETCH NEXT FROM @oneoff_sqljob_cursor INTO  @job_id ,@job_name ,@schedule_name ,@job_description ,@active_start_date ,@active_start_time ,@owner_login_name_user ,@owner_login_name_service_account ,@enabled ;
WHILE @@FETCH_STATUS = 0BEGIN EXEC [dbo].[sp_update_job_for_developers]   @job_name=@job_name,  @owner_login_name=@owner_login_name_user; EXEC msdb.dbo.sp_detach_schedule  @job_name=@job_name,  @schedule_name=@schedule_name, @delete_unused_schedule=1 ;
EXEC msdb.dbo.sp_add_jobschedule  @job_name=@job_name,  @name=@schedule_name,  @enabled=1,  @freq_type=1,  @freq_interval=1,  @freq_subday_type=0,  @freq_subday_interval=0,  @freq_relative_interval=0,  @freq_recurrence_factor=1,  @active_start_date=@active_start_date,  @active_start_time=@active_start_time ; EXEC [dbo].[sp_update_job_for_developers]   @job_name=@job_name,  @owner_login_name=@owner_login_name_service_account; FETCH NEXT FROM @oneoff_sqljob_cursor INTO  @job_id ,@job_name ,@schedule_name ,@job_description ,@active_start_date ,@active_start_time ,@owner_login_name_user ,@owner_login_name_service_account ,@enabled ;END

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.