Using Beyond Compare to compare 2 SSIS packages

Pretend you have two SSIS packages that you want to compare. Let's assume you don't care about cosmetics like position and width of squares, and that you are concerned about connections, queries, and everything else except GUIDs of objects.

When you save a SSIS package you get a .dtsx file. It is an escaped XML file for the most part. This means it is also text based. Beyond Compare does a great job of comparing text file. However, all the things we said we didn't want are also in this file.

Beyond Compare is powerful enough to allow us to tell it what things need to be ignored in the file. This is done by doing the following.

Open Beyond Compare | Tools | Pick Rules | New Rules...

NOTE: Later you can get back to it by choosing it in the menu first. Then after that you just have to go to the Beyond Compare | Tools | Edit Current Rules...

General Tab:

Name: SSIS Package

Associated with: *.dtsx

Whitespace Includes:

Tabs and spaces (8 character positions)

Line endings: checked

Importance Tab:

Unimportant Text checkboxes: check them all

Here is the important part.

Click the New button.

Category: Regular Expression

Regular Expression: (width|height|top|left|x|y)="-?\d{1,10}"

Click the New button.

Category: Regular Expression

Regular Expression: \{.{36}\}

Click the New button.

Category: Regular Expression

Regular Expression: ddsxmlobjectstreaminitwrapper binary=".{1,24}"

All other tabs and values are default values.

Now when you compare .dstx files these rules will automatically be applied, and the unwanted stuff will not be used as criteria when comparing the files.

Cost of Query in Oracle

One way to tell what is happening when a query is executed on Oracle is to open up SQL Plus and type the following before your SQL statement you want to analyze. Turning Autotrace on will give you performance cost in the way of an Execution Plan.

SET AUTOTRACE ON

When you are done, turn it off with

SET AUTOTRACE OFF

Timing SQL Plus query

To time a query in Oracle SQL Plus do the following.

SET TIMING ON

select * from mytable

.... Results here....

Elapsed: 00:00:00.15

Get definition for view in Oracle

set LONG 10000 select TEXT from ALL_VIEWS where view_name = 'MY_VIEW';

Line one must be at least the size of the statement used to create the view. Either pick a really big number if you think the view is large or use the following to determine how long it is first. If it isn't, the text will be truncated

select text_length from all_views where view_name = 'MY_VIEW'

NOTE: name of the view is case sensitive as are most every thing in Oracle.

If you want to capture output to something other than a SQL Plus window such as a file, be sure to include do the following before you execute the select statement.

spool c:\v_person.sql

To close the file when you are done writing output to it, call the following:

spool off

To summarize:

select text_length from all_views where view_name = 'MY_VIEW'

10000

-- whatever value you get back, use on the next statement.

set LONG 10000

spool c:\v_person.sql

select TEXT from ALL_VIEWS where view_name = 'MY_VIEW';

spool off

Fixing Derby Driver error in NetBeans 6.0

I just installed NetBeans IDE 6.0. I also have SUN Java Studio, and NetBeans 5.5.1 already installed. They also seem to have a Derby database for them. When in NetBeans 6.0 I try to connect to any of the sample databases or even one that I create, I get message similar to "....unable to connect. cannot establish connection to using org.apache.derby.jdbc.ClientDriver (Unable to find a suitable driver).

To fix the issue I simply expand the NetBeans IDE | Services tab | Databases | Drivers | Java DB (4 of them) and add the path to the correct Derby database driver location. I just right clicked each of the drivers and chose Customize menu item. Then added the following path (customize it to your system) C:\Documents and Settings\MyProfileHere\.netbeans\6.0\jdbc-drivers\derbyclient.jar.

Custom Paging Helper Class

Below is the source I wrote to encapsulate methods needed to implement custom paging. This is useful for GridView or any other time you need to page through things. The class has all functionality accessible via static methods if you prefer. All the functionality that makes sense can also be used by an instance of the class. The advantage of creating an instance of the class is you just pass your parameters once, then all the parameters that you would need to specify in the static version of the methods are taken automatically from the instance of the class. I find the later to be less error prone due to mixing up parameters. Below the class is code that I typically use to implement custom paging for a GridView using System; using System.Collections.Generic; /// <summary> /// Encapsulates the algorithm for paging a list. All indexes are 1-based. /// </summary> public class PagingHelper { int totalNonPagedRows = 0; int pageSize = 0; int totalPageCount; int currentPageIdx = 0; public PagingHelper(int currentPageIdx, int totalNonPagedRows, int pageSize) { this.totalNonPagedRows = totalNonPagedRows; this.pageSize = pageSize; this.currentPageIdx = currentPageIdx; // do some quick basic calculations based on parameters above that we will need later this.totalPageCount = GetTotalPageCount(); } public static int GetTotalPageCount(int totalNonPagedRows, int pageSize) { int totalPageCount = (int)Math.Ceiling((double)totalNonPagedRows / pageSize); return totalPageCount; } public int GetTotalPageCount() { return PagingHelper.GetTotalPageCount(totalNonPagedRows, pageSize); } public static List<int> PagesIndexes(int totalPageCount) { List<int> pageIndexes = new List<int>(); for (int i = 1; i <= totalPageCount; i++) { pageIndexes.Add(i); } return pageIndexes; } public List<int> PagesIndexes() { return PagingHelper.PagesIndexes(totalPageCount); } /// <summary> /// Returns true if there is a page before the current page. Index is 1-based /// </summary> /// <param name="currentPageIdx">1-based index</param> /// <returns></returns> public static bool HasPreviousPage(int currentPageIdx) { if (currentPageIdx == 1) return false; else return true; } /// <summary> /// Returns true if there is a page before the current page. Index is 1-based /// </summary> /// <param name="currentPageIdx">1-based index</param> /// <returns></returns> public bool HasPreviousPage() { return PagingHelper.HasPreviousPage(currentPageIdx); } /// <summary> /// Returns true if there is a page after the current page. Index is 1-based /// </summary> /// <param name="currentPageIdx">1-based index</param> /// <returns></returns> public static bool HasNextPage(int currentPageIdx, int totalPageCount) { if (currentPageIdx == totalPageCount) return false; else return true; } /// <summary> /// Returns true if there is a page after the current page. Index is 1-based /// </summary> /// <param name="currentPageIdx">1-based index</param> /// <returns></returns> public bool HasNextPage() { return PagingHelper.HasNextPage(currentPageIdx, totalPageCount); } /// <summary> /// Returns the index of the page before the current page. /// If no page exists, the current page index is returned. Index is 1-based. /// </summary> /// <param name="currentPageIdx">1-based index</param> /// <returns></returns> public static int GetPreviousPageIndex(int currentPageIdx) { if (HasPreviousPage(currentPageIdx)) { return currentPageIdx - 1; } else { return currentPageIdx; } } /// <summary> /// Returns the index of the page before the current page. /// If no page exists, the current page index is returned. Index is 1-based. /// </summary> /// <param name="currentPageIdx">1-based index</param> /// <returns></returns> public int GetPreviousPageIndex() { return PagingHelper.GetPreviousPageIndex(currentPageIdx); } /// <summary> /// Returns the index of the page after the current page. /// If no page exists, the current page index is returned. Index is 1-based. /// </summary> /// <param name="currentPageIdx">1-based index</param> /// <returns></returns> public static int GetNextPageIndex(int currentPageIdx) { return currentPageIdx + 1; } /// <summary> /// Returns the index of the page after the current page. /// If no page exists, the current page index is returned. Index is 1-based. /// </summary> /// <param name="currentPageIdx">1-based index</param> /// <returns></returns> public int GetNextPageIndex() { return PagingHelper.GetNextPageIndex(currentPageIdx); } public static bool IsValidPageIdx(int pageIdx, int totalPageCount) { if (pageIdx >= 1 && pageIdx < totalPageCount) return true; else return false; } public bool IsValidPageIdx() { return PagingHelper.IsValidPageIdx(currentPageIdx, totalPageCount); } /// <summary> /// Returns the row index (1-based) for the first row on the specifiec page /// </summary> /// <param name="pageIndex">the index of the page to get the first row index</param> /// <returns>1-based row index</returns> public static int GetStartRowIndex(int pageIndex, int pageSize) { int startRowIndex = (pageIndex - 1) * pageSize + 1; return startRowIndex; } /// <summary> /// Returns the row index (1-based) for the first row on the specifiec page /// </summary> /// <param name="pageIndex">the index of the page to get the first row index</param> /// <returns>1-based row index</returns> public int GetStartRowIndex() { return PagingHelper.GetStartRowIndex(currentPageIdx, pageSize); } public static int GetCurrentPageIndexFromUrl(string queryParameterName) { string idx = System.Web.HttpContext.Current.Request[queryParameterName]; int currentPageIdx = 1; if (!string.IsNullOrEmpty(idx)) { currentPageIdx = Convert.ToInt32(idx); } return currentPageIdx; } public static int GetCurrentPageIndexFromForm(string formParameterName) { string idx = System.Web.HttpContext.Current.Request.Form[formParameterName]; int currentPageIdx = 1; if (!string.IsNullOrEmpty(idx)) { currentPageIdx = Convert.ToInt32(idx); } return currentPageIdx; } } Here is the code I used to populate a previous, next buttons and other labels that show information about the current page, etc. I am using a ObjectDataSource and using my Custom Objects and DAL that I have referenced in some of my other blog entries. protected void ObjectDataSource1_Selecting(object sender, ObjectDataSourceSelectingEventArgs e) { int currentPageIdx = PagingHelper.GetCurrentPageIndexFromUrl("PageIdx"); lblCurrentPage.Text = Convert.ToString(currentPageIdx); int startRowIndex = PagingHelper.GetStartRowIndex(currentPageIdx, PAGE_SIZE); e.InputParameters.Clear(); e.InputParameters.Add("startRowIndex", startRowIndex); e.InputParameters.Add("maximumRows", PAGE_SIZE); e.InputParameters.Add("orderBy", e.Arguments.SortExpression); e.InputParameters.Add("totalNonPagedRowCount", e.Arguments.TotalRowCount); } protected void ObjectDataSource1_Selected(object sender, ObjectDataSourceStatusEventArgs e) { // Get Values from form and url lblTotalUnpagedRow.Text = Convert.ToString(e.OutputParameters["totalNonPagedRowCount"]); int totalUnpagedRowCount = Convert.ToInt32(lblTotalUnpagedRow.Text); int currentPageIdx = PagingHelper.GetCurrentPageIndexFromUrl("PageIdx"); // do calculations int totalPageCount = PagingHelper.GetTotalPageCount(totalUnpagedRowCount, PAGE_SIZE); // configure previous and next links linkPrevious.Enabled = PagingHelper.HasPreviousPage(currentPageIdx); linkPrevious.NavigateUrl = string.Format("GridViewPage.aspx?PageIdx={0}", PagingHelper.GetPreviousPageIndex(currentPageIdx)); linkNext.Enabled = PagingHelper.HasNextPage(currentPageIdx, totalPageCount); linkNext.NavigateUrl = string.Format("GridViewPage.aspx?PageIdx={0}", PagingHelper.GetNextPageIndex(currentPageIdx, totalPageCount)); } Here is another example, but using buttons instead of links for paging. The advantage of this is that sorting can be done without doing redirects. The above code doesn't really support sorting of custom paging without redirecting the url due to the fact that the current page index is taken from the url and to change the url we need to redirect. This can make page hit stats a little misleading. The good news is that it is Google friendly. Since it uses url instead of javascript to do the navigation (like LinkButtons or Buttons) Google can follow them. An alternative to this is use javascript and have a page that you tell Google to explicitly go to. This is a page that doesn't have to be an end user page, it just has to have your site map on it. The choice is yours. Here is the code that supports custom paging and sorting. protected void GridView1_Sorting(object sender, GridViewSortEventArgs e) { PageIndex = 1; } protected void ObjectDataSource1_Selecting(object sender, ObjectDataSourceSelectingEventArgs e) { int startRowIndex = PagingHelper.GetStartRowIndex(PageIndex, PAGE_SIZE); e.InputParameters.Clear(); e.InputParameters.Add("startRowIndex", startRowIndex); e.InputParameters.Add("maximumRows", PAGE_SIZE); e.InputParameters.Add("orderBy", e.Arguments.SortExpression); e.InputParameters.Add("totalNonPagedRowCount", e.Arguments.TotalRowCount); } protected void ObjectDataSource1_Selected(object sender, ObjectDataSourceStatusEventArgs e) { int totalNonPagedRows = Convert.ToInt32(e.OutputParameters["totalNonPagedRowCount"]); PagingHelper paging = new PagingHelper(PageIndex, totalNonPagedRows, PAGE_SIZE); // populate labels that tell paging information lblCurrentPage.Text = Convert.ToString(PageIndex); lblTotalUnpagedRow.Text = Convert.ToString(totalNonPagedRows); // enable / disable navigation buttons btnNext.Enabled = paging.HasNextPage(); btnPrevious.Enabled = paging.HasPreviousPage(); // populate drop down list of pages ddlPages.DataSource = paging.PagesIndexes(); ddlPages.DataBind(); ddlPages.SelectedValue = Convert.ToString(PageIndex); } protected void ddlPages_SelectedIndexChanged(object sender, EventArgs e) { PageIndex = Convert.ToInt32(ddlPages.SelectedValue); GridView1.DataBind(); } public int PageIndex { get { return ViewState["PageIndex"] == null ? 1 : (int)ViewState["PageIndex"]; } set { ViewState["PageIndex"] = value; } } protected void btnNext_Click(object sender, EventArgs e) { PageIndex = PagingHelper.GetNextPageIndex(PageIndex); GridView1.DataBind(); } protected void btnPrevious_Click(object sender, EventArgs e) { PageIndex = PagingHelper.GetPreviousPageIndex(PageIndex); GridView1.DataBind(); } And finally, below is how you would use similar code (I added a dropdown list for the user to select the page to go to) but doesn't use the ObjectDataSource. I find this code easier to maintain and read since it is pretty linear; unlike the ObjectDataSource that forces you to figure out the correct events to put your code it. Well, that is IF all you are doing is displaying data. If you want the other CRUD operations, or if you want to have the columns automatically created for you then I would use the ObjectDataSource. It is the new way in ASP.Net 2.0 anyway. Might as well go with the flow. :) Again, either one works, it is up to you. public partial class GridViewPage2 : System.Web.UI.Page { int PAGE_SIZE = 3; PagingHelper paging; protected void Page_Load(object sender, EventArgs e) { GridView1.EnableViewState = false; BindUI(); } protected void BindUI() { PersonDAL dal = new PersonDAL(); int startRowIndex = PagingHelper.GetStartRowIndex(PageIndex, PAGE_SIZE); int totalNonPagedRowCount; List<PersonEntity> people = dal.FetchPersonGetAllPaged(startRowIndex, PAGE_SIZE, GridView1.SortExpression, out totalNonPagedRowCount); GridView1.DataSource = people; GridView1.DataBind(); PagingHelper paging = new PagingHelper(PageIndex, totalNonPagedRowCount, PAGE_SIZE); // populate labels that tell paging information lblCurrentPage.Text = Convert.ToString(PageIndex); lblTotalUnpagedRow.Text = Convert.ToString(totalNonPagedRowCount); // enable / disable navigation buttons btnNext.Enabled = paging.HasNextPage(); btnPrevious.Enabled = paging.HasPreviousPage(); // populate drop down list of pages ddlPages.DataSource = paging.PagesIndexes(); ddlPages.DataBind(); ddlPages.SelectedValue = Convert.ToString(PageIndex); } protected void GridView1_Sorting(object sender, GridViewSortEventArgs e) { PageIndex = 1; } protected void ddlPages_SelectedIndexChanged(object sender, EventArgs e) { PageIndex = Convert.ToInt32(ddlPages.SelectedValue); GridView1.DataBind(); } public int PageIndex { get { return ViewState["PageIndex"] == null ? 1 : (int)ViewState["PageIndex"]; } set { ViewState["PageIndex"] = value; } } protected void btnNext_Click(object sender, EventArgs e) { PageIndex = PagingHelper.GetNextPageIndex(PageIndex); BindUI(); } protected void btnPrevious_Click(object sender, EventArgs e) { PageIndex = PagingHelper.GetPreviousPageIndex(PageIndex); BindUI(); } } Tips: Set GridView.AllowSorting = true. If you get "The data source 'ObjectDataSource1' does not support sorting with IEnumerable data. Automatic sorting is only supported with DataView, DataTable, and DataSet.", then you need to specify the ObjectDataSource.SortParameterName to the name of your Select parameter in your DAL.

Getting the output parameter after executing your stored procedure.

If you have a stored procedure that has an OUTPUT parameter it, it requires different syntax to execute and check the output parameter when you are using Query Analyzer or SQL Management Studio. Let's assume you have a very simple stored procedure that select 5 rows and just returns the number of rows in a table as an output parameter. create PROCEDURE [dbo].GetRows @RowCount int OUTPUT AS select top 5 * from Person select @RowCount = count(1) from Person GO Here is the syntax to test it from Query Analyzer or SQL Management Studio. Declare @TotalCount int exec GetRows @TotalCount OUTPUT select @TotalCount If you want to do this from ADO.Net, here is the syntax. Once again, there is a trick. You must close the reader before checking the value of the output parameter. In this example we are using a SQL Helper class similar to one of the early MS DAAB and custom objects, but this is not the important piece of code. The important thing here is that when using a reader, you must close the reader before accessing the output parameter. If you use a DataAdapter to load results into a heavy like DataTable or DataSet. public List<PersonEntity> FetchRows(out int rowCount) { SqlDataReader reader = null; List<PersonEntity> entities = new List<PersonEntity>();

try { _sqlServer = new SqlServer(_sqlConnStr);

SqlParameter[] parameters = new SqlParameter[1]; parameters[0] = _sqlServer.MakeOutParam("@RowCount", SqlDbType.Int, -1);

reader = _sqlServer.ExecuteReader("GetRows", parameters);

while (reader.Read()) { entities.Add(FillEntityFromReader(reader)); }

// we must close the reader before we can read an output parameter reader.Close(); rowCount= (int)parameters[3].Value;

} catch (Exception ex) { throw ex; } finally { // a little overkill, but just for clarity, close everything explicitly if (reader != null && !reader.IsClosed) { reader.Close(); }

if (_sqlServer != null) { _sqlServer.Close(); _sqlServer.Dispose(); } }

return entities; } To call this method from C#, you would do something like: int count; FetchRows(out count); // use count here