Blogging source code on WordPress

Possibly save 4 hours of your time: If you are programmer who has started a blog, you may want to display your source code.  Then you may try to find plugins to help support better display of your code. However I find most plugins are hard to use and not intuitive.

WordPress does have original support for displaying code with colors based on language type.

public void DisplayWordpressCodeWithColor()
{
}

For supported language, please see list in the following link:

https://en.support.wordpress.com/code/posting-source-code/

Setup SQL Server Integration Services environment and SQL Agent Job to run deployed SSIS package

Possibly save 8 hours of your time: After you create SSIS package and build the project (bin folder should have .ispac file), the next step is to deploy that to a Microsoft SQL Server instance environment and have a scheduled job to run the package. It is not trivial to setup a proper environment. The steps below are targeting Microsoft SQL Server 2012.

Pre-requisites

  • Please make sure that you have SSISDB catalog under Integration Services Catalogs already created. If not, you can right click and choose Create Catalog.

ssisdb

  • A specific user or system user permission will be needed to run the job to execute the package. From here on we call this user the system user.
  • If the package requires read and/or write access to specific database, provide the following database role permissions as needed to the system user:
    • db_datareader
    • db_datawriter
    • SSIS
  • If the package requires execute permission for specific schema on a specific database, provide the following permissions as needed to the system user:
    • Execute
    • Alter (if using bulk insert tasks in SSIS)
  • For msdb system database, the system user needs to be added to Security > Users. Under Owned Schemas, check SQLAgentUserRole.
  • For SSISDB database, the system user needs to be added to Security > Users.
  • The database administrator who will be running the command script to create the job to run the package will need sysadmin role.
  • If you want to receive SSIS job notification on success or failure, you will need to setup an account under Management > Database Mail. Then right click SQL Server Agent, click Alert System and Enable mail profile and choose the account profile created.

Script Implementation

  • Please see at the bottom for the script. The scripts can be run in command prompt directly or embed in a .cmd script file first. Any word surround by %% are variables that you can set.
  • cmd Script will create
    • folder in SSISDB
    • Deploy packages via .ispac
    • run the following SQL script
  • SQL Script will create
    • delete previous existing job
    • create Credential
    • create Proxy
    • create Alerts
    • create SQL Agent Job via .sql
    • add package(s) to the job
    • add job schedule

Post-requisites

  • Setup environment variables if your packages requires specific configuration for this environment – Integration Services Catalog >> SSISDB >> Project >> Environments
  • Update Project Variable Environment
    • Right click project. Select Configure.
    • Under References, add the environment created previously.
    • Under Parameters, edit the same values as in the environment section to use environment variables.
  • Set project level permissions
    • At the SSISDB folder level, add system identity user account with read, execute objects and read objects permissions
    • At the projects level, add read and execute permission for the system identity user.
  • Setup working folder permissions for system user if using working folder (typically if data files are involved in the package, they need to be somewhere)

Script – create folder in SSISDB

set sqlcmdpath=”C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd.exe”

REM Create folder in SSISDB

echo Creating folder in SSISDB if not exists…
echo %sqlcmdpath% -S %serverName% -d ssisdb -Q “if not exists(select 1 from internal.folders where name = ‘%folder%’) exec catalog.create_folder %folder%”
%sqlcmdpath% -S %serverName% -d ssisdb -Q “if not exists(select 1 from internal.folders where name = ‘%folder%’) exec catalog.create_folder %folder%”

echo *******************************************************

Script – deploy packages via .ispac file

set isdeploymentwizard=”C:\Program Files\Microsoft SQL Server\110\DTS\Binn\isdeploymentwizard.exe”

REM Deploy the ETL
echo ETL Deployment is running…
echo %isdeploymentwizard% /S /ST:File /SP:”yourProjectName.ispac” /DS:%serverName% /DP:/SSISDB/%folder%/%project%
%isdeploymentwizard% /S /ST:File /SP:”yourProjectName.ispac” /DS:%serverName% /DP:/SSISDB/%folder%/%project%
echo ETL Deployment Completed
echo *******************************************************

Note: If you get error 0xC0011008 such as “the package failed to load due to error error loading from xml”, ensure you are targeting the right version of database via project properties.

Script – to run the following SQL script in one file with passed in parameters

set sqlcmdpath=”C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd.exe”

REM Create SQL Agent Job
echo Creating SQL Agent Job…
echo %sqlcmdpath% -S %serverName% -i %jobSQL% -v identity=”%id%” password=”%pass%” operator=”%oper%” environmentId=”%envId%”
%sqlcmdpath% -S %serverName% -i %jobSQL% -v identity=”%id%” password=”%pass%” operator=”%oper%” environmentId=”%envId%”
echo SQL Agent Job has been created

Delete previous existing job

USE [msdb]

GO
–Clean up SQL Agent Job

DECLARE @jobId BINARY(16)

SELECT @jobId = job_id FROM msdb.dbo.sysjobs WHERE (name = N’Your Job Name’)

IF (@jobId IS NOT NULL)    EXEC msdb.dbo.sp_delete_job @jobId go

Create Credential

–Create Credential Note: change the credential name

use master

go

IF EXISTS (SELECT 1 FROM sys.credentials WHERE name = N’Your Credential Name’) DROP credential [Your Credential Name]

go

create credential [Your Credential Name] with identity = N’$(identity)’, secret = N’$(password)’go

Create Proxy

–Create Proxy Note: change the proxy name

USE msdb

GO

IF EXISTS (SELECT 1 FROM msdb.dbo.sysproxies WHERE name = N’Your Proxy Name’) EXEC dbo.sp_delete_proxy @proxy_name = N’Your Proxy Name’

GO

EXEC msdb.dbo.sp_add_proxy @proxy_name=N’Your Proxy Name’,@credential_name=N’Your Credential Name’, @enabled=1

GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’Your Proxy Name’, @subsystem_id=11GOEXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N’Your Proxy Name’, @login_name=N’$(identity)’

GO

Create Alerts

–Create Operator

USE [msdb]

GO

IF EXISTS(SELECT 1 FROM msdb.dbo.sysoperators WHERE name = N’Your Operator Name’) EXEC sp_delete_operator @name=N’Your Operator Name’

GO

EXEC msdb.dbo.sp_add_operator @name=N’Your Operator Name’,  @enabled=1,  @weekday_pager_start_time=90000,  @weekday_pager_end_time=180000,  @saturday_pager_start_time=90000,  @saturday_pager_end_time=180000,  @sunday_pager_start_time=90000,  @sunday_pager_end_time=180000,  @pager_days=0,  @email_address=N’$(operator)’,  @category_name=N'[Uncategorized]’

GO

Create SQL Agent Job

 

–Create SQLAgent Job

USE [msdb]

GO
BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N’Your Job Name’,  @enabled=1,  @notify_level_eventlog=0,  @notify_level_email=2,  @notify_level_netsend=0,  @notify_level_page=0,  @delete_level=0,  @description=N’No description available.’,  @category_name=N'[Uncategorized (Local)]’,  @owner_login_name=N’$(identity)’,  @notify_email_operator_name=N’Your Job Name’, @job_id = @jobId OUTPUTIF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

Add package to the job

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Your Step Name’,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’SSIS’,
@command=N’/ISSERVER “\”\SSISDB\Your Folder Name\Your Project Name\Your Package Name.dtsx\”” /SERVER “\”$(serverName)\”” /ENVREFERENCE $(environmentId) /Par “\”$ServerOption::LOGGING_LEVEL(Int16)\””;1 /Par “\”$ServerOption::SYNCHRONIZED(Boolean)\””;True /CALLERINFO SQLAGENT /REPORTING E’,
@database_name=N’master’,
@flags=0,
@proxy_name=N’Your Proxy Name’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

Note: You can repeat this to add more packages as steps in your job.

Add Job Schedule
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’Your Schedule Name’,  @enabled=1,  @freq_type=4,  @freq_interval=1,  @freq_subday_type=8,  @freq_subday_interval=12,  @freq_relative_interval=0,  @freq_recurrence_factor=0,  @active_start_date=20160616,  @active_end_date=99991231,  @active_start_time=0,  @active_end_time=235959,  @schedule_uid=N’d5de52a3-a8e8-49ca-92d0-fbbc3ee10098′

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N’$(serverName)’

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:
GO

 

 

Download SQL Server Data Tools (SSDT) 17.1 for Visual Studio 2015 (SQL Server Integration Services – SSIS – project template)

Possibly save 1 hour of your time: This is a quick reference guide for getting the SSIS project template within Visual Studio 2015.

Download link here https://go.microsoft.com/fwlink/?linkid=849393

Source: https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt

Once you download you can just double click to run the installation. You can check the following features that you need. I need SSIS so I will check SQL Server Integration Services.

install SSDT vs2015

Then you will have to agree to license terms.

If you select all features, it will take about 1 hour to install.

Once install is completed, open Visual Studio 2015, and go to File > New Project, you should be able to select the following Business Intelligence > Integration Services > Integration Services Project template.

ssis template vs2015

 

 

OData syntax

Possibly save 1 hour of your time: This is a quick reference guide for querying OData service. You can query as follows with the code shown below respectively.

Top 5 records

Filter based on some elementName with value ‘some Text’. Notice it uses eq as equal and %27 as single quote and space as +.

  • $top=5
  • $filter=elementName+eq+%27some+Text%27

 

User Interface testing

Possibly save many hours of your time: As a developer you may end up spending a lot of time testing your web application from the user interface.

There is a Firefox browser plugin tool named Selenium IDE that you can follow the instructions below to download. Once you download, you can create test suite and within test suite you can add test case. For each test case, you can click on the red circle (Record button) to record your actions. So you will have two Firefox browser windows, one where you will browse to your website and start performing actions and the other window (Selenium IDE) will record your actions and you can playback later to repeat the UI test. This will save you a lot of time in the future for repetitive tasks of testing. You can use all kinds of verify commands to test your labels, values on your display.

se_1

se_2

se_3

se_4

se_5

It will then ask you to restart Firefox.

se_6

Once restart, you can open the tool by going to menu icon and click on Developer, then choose Selenium IDE.

se_7

se_8

Then it will open the Selenium IDE window.

se_9

Some function tips:

If some elements take time to load, use waitForText rather than verifyText. You can also use pause function.

You can use store command to store variable.

se_10

se_11

Then you can use it in other functions with $ and curly brackets notation.

 

LINQ and Entity Framework Errors

Possibly save 4 hours of your time: When working with LINQ and Entity Framework 6, you may encounter the following errors.

  1. Only parameterless constructors and initializers are supported in LINQ to Entities.
  2. The ‘Distinct’ operation cannot be applied to the collection ResultType of the specified argument. Parameter name: argument
    This happens on Union operation of two entities with different schemas
  3. An error occurred while preparing the command definition. See the inner exception for details.
    This happens on Union operation of Concat operation on two entities with different schemas
  4. System.InvalidOperationException : The specified cast from a materialized ‘System.Int32’ type to the ‘System.String’ type is not valid.
    This happens on Union operation of Concat operatoin on two entities with different schemas

First one is obvious, you must have parameterless constructor when selecting new object transformation.

from i in dbcontext.TableName

select new EntityObjectName() { ID = i.ID }

Second and Third one is similar. When you select new object with properties that are in array form, you may get these errors. One idea would be to simply change this to IEnumerable or IList.

Fourth, this one took me awhile to figure out. It basically happens when the property being transformed came from two different tables and have different data types on the database backend. A simple fix is to check for null and turn into empty string on both the union/concat select. This seems to fix the issue.

from i in dbcontext.TableName

select new EntityObjectName() { Value = i.Value ?? “” }

UNION or CONCAT

from j in dbcontext.TableName2

select new EntityObjectName() { Value = j.Value2 ?? “” }

Make methods accessible to unit tests

Possibly save 1 hour of your time: If you would like to create unit tests for your library, you can create the unit tests in a different project to separate the tests and the code.

1. Typically, a good convention is use the same project name and append dot Tests. i.e. MyProjectName.Tests

2. Then you will need to add reference to your project.

3. To access the methods in your project from the tests project, you will need to mark them internal access unless it is already marked as public access.

4. In the AssemblyInfo.cs of your project, you need to add the following the make your project visible to your tests project.

   [assembly: InternalsVisibleTo(“YourProjectName.Tests”)]

There are other methods of testing your protected or private methods, but I find this solution the easiest to configure and maintain.

Publish a Nuget package to your own server

Possibly save 1 hour of your time:

1. Download Nuget and set environment variables path where the nuget.exe is found

2. Open command prompt and cd to the .csproj folder.

Run the following command to create a nuget specification template.

nuget spec

3. Open command prompt. Then change directory to go to the folder where the YourProjectName.csproj is. Then enter the following to package your csproj.

    nuget pack YourProjectName.csproj

Next, you will push this to your server location

    nuget push YourProjectName.1.0.0.0.nupkg -Source \\serverLocation

The version number is based on your assembly info file.

Resource Link:

https://docs.microsoft.com/en-us/nuget/quickstart/create-and-publish-a-package

 

 

Execute PowerShell Script

Possibly save 1 hour of your time:

  1. Use ./ (dot and slash and PowerShellScriptName) to run the command in the current path folder. You don’t need to enter .ps1

Often times you will type cd to change directory to the folder where the PowerShell script exists. Then you type the script name and it gives you error that the command is not recognized.

Incorrect
PS C:\Scripts> HelloWorld

Correct
PS C:\Scripts> ./HelloWorld