SSIS Packages Saved to SQL Server

When creating an SSIS package through a wizard, one of the options when saving it is to save it to SQL Server. However, when you do this the package may appear to disappear and cannot be found. The answer to finding these packages again is in SQL Management Studio, choose connect and choose Integration Services from the list.

Then, in object explorer, open the Integration Services instance for the server and drill down to find the saved SSIS packages.

Creating ClickOnce Projects

It is quite easy to create ClickOnce projects. These are normal .Net projects that are built using the Publish option on the Build menu.

All the settings related to publishing a ClickOnce project are found on the Publish tab of the project properties screen.

The screen shot below shows the settings on the main screen.



The most important things to bear in mind are:
1) The publish location must be reachable from the development machine. This is the equivalent of the release folder in a normal build.
2) The install folder must be reachable from the client machines. This can be a web server or a network share (can also be a CD). You will copy the files to this location once they are published.
3) Click theUpdate button to bring up the update options screen. This is important because by default the update folder is the same as the publish folder. In reality it should be a location reachable by client machines.

The reason that the update and install locations are important, even though we will be copying the installation files to a specific location is because they are included in the application manifest and the deployment manifest.

It is possible to specify .Net Framework and certain other packages as prerequisites. However, not everything is supported here, so it may be necessary to install certain other things manually, or even edit the Publish.htm file to include links for them too.

On visiting the client site I encountered an error message "The application requires that assembly *** be installed in the Global Assembly Cache". This message related to SQL SMO.

As it happens, all the files required for SMO were already installed on these machines.

The answer is:go into the VB project properties page, publish tab, click application files.

Then for the files that will be installed separately and not updated as part of the clickonce package, change their setting from Prerequisite(Auto) to Include(Auto).

ClickOnce Deployment

This looks very promising, especially where an application does not need to deploy any files to the GAC (Global Assembly Cache). An example of .Net applications that deploy to the GAC would be those that use Microsoft Office PIAs (Primary Interop Asseblies)

However, there may be work arounds for this, such as installing PIA's separately.

This blog provides information about installing PIAs on client PCs.

This page gives a good walk through of ClickOnce deployment.

www.15seconds.com/issue/041229.htm

In visual studio, Build\Publish takes you to the simple publishing wizard.

When you publish, a set of folders and files is created that inclused Publish.htm.

This is the starting point for installing the ClickOnce application on the user's PC.

When choosing the local web server screen shot one shows the resulting folders\files that are created.

Opening Publish.htm gives this screen:

Click Install and this dialog follows:

As in my test scenario the application was not signed, this dialog appeared.

The application is not actually stored on the client machine. (At least not in the usual location, it is not is program files sub directory)

When an update is posted to the update location, and a user clicks on the application shortcut they will see this message. Note, the update has it's own version number system, and does not depend on the application version number.

Deploying ClickOnce Solutions On Client Sites

In the project properties\publish tab there are options that can be set that make it possible to have install and update locations that are different from the publish location.

On the main page there is an installation URL as well as a publish URL. Make the installation URL the location of the files on the client site. On the Update page, set the Update Location to the place on the client network where updates will be located. This can be the same as the publish URL.

VB.Net Tracing

Use Trace is very much like Debug, but it allows you to  tracing on the client machine without visual studio.

I have created a class trace which can be used in any project, and writes basic information about the application and the user away to a config file.

This should be include in a project, and a start statement should be called during the application load.

The trace flag should be set to on in the project options.

Then anywhere in the application where I want to check a value, put Trace.WrineLine("")

SQL Server Debugging

I have tried this out today and to be able to step through a stored procedure after it has been called by a .net application is fantastic. This can save so much time now I know what to do.

The key to this is the server explorer in Visual Studio - I should be in the habit of always setting up a connection to SQL server when working on a database project.

Four steps:

1) Attempt to step into a stored procedure and receive warning that certain opptions have to be set on the machine. OK this and it will do it automatically. (I did not get the exact messagage)

2) In Project properties, debug tab,  Check "Enable SQL Server Debugging"

3) In server explorer, right click and open a stored procedure. Set a break point

4) In connection strings, add Polling=false; to string

The the application branches into the stored procedure you can step through it like any other piece of code, even inspecting values of TSQL variables within the procedure.

This could save a lot of time if I get used to these steps.

I need to watch that I have a means of setting connection polling only for testing, and having a different setting for the released versions of the code.

One possibility is to check processes to see if visual studio is running. Then only have pooling set to true if it is not, otherwise explicitly set it to false.

This works at Scotts but not on my home PC, I need to troubleshoot this because this functionality is so useful. You can even step through stored procedures on their own.

Exporting to CSV, Opening In Excel, Special Character Problem

When creating a csv file from VB.net, then opening this file in Excel using the file association method (ie process.start(file.csv)) special characters such as Nestlé are not recognised by MS Excel. Instead it is shown as Nestlé 

When opening the same CSV file in notepad the foreign characters are shown correctly.

However, if the Import feature is used in Excel, and the encoding is specified as UTF-8 then the data is displayed correctly.

By default, the vb.net streamwriter will use UTF-8 encoding. 

In UTF-8 the two-byte sequence 0xC3 0xA9 = é

In Windows-1252 0xC3 0xA9 = Ã©

To solve this problem I could write the data as an Excel file using COM. However, this ties the application to a particular version of Office and could break if the company upgrades from Office 2003 to 2007.

Instead I have changed the encoding within vb.net.

stwriter = New StreamWriter(File.Create(FilePath), System.Text.Encoding.UTF32)

Using UTF32 did cause Excel to handle foreign characters correctly, although at the cost of automatically splitting the data into columns. 

The TextToColumns feature I added into a macro that automatically selected the first column, split the data into columns and autofit the columns to the contents.

Crystal Reports XI Cross Tab Reports - Column Order

In Crystal Reports, with a cross tab report, the column order is important.
However, the label used to describe the column may not be good for sorting on. for example Month Name, we would not want months sorted alphabetically.
The report I am working on now can have dynamic colum names.
This is the answer.

In the query for the report, I return one column for the sort order, and another for the display value.
When setting up the cross tab, select the one for the sort order (e.g. Period) as the column group.
Then on the design/preview page, right click the column header, choose column options\group options.
The following dialog appears:



Choose Options tab on the dialog, this allows to to choose the field that will be used for the header.