How to schedule a job that runs an SSIS Package in SQL Server

Again I just want to document how I did it. It's hard to remember sometimes the steps taken to make things work. Specially for things that are not done everyday. In this particular case, it is confusing which user accounts or login to specify to be used by the server agent. Anyway, here's a recipe on how to make this work, just follow along...

1. Create a credential.
2. Create a proxy under SSIS Package Execution using the above credential.
3. Create a job in SQL Server Agent, and this is the important part, specify "sa" as the owner of this job.
4. Now add a Step, select SQL Server Integration Services Package type. Again this is the crucial step: Go to the Run as dropdown, then select the proxy specified above.

Now test your package and let me know if it works for you.

How to import SSIS package protected by password to Integration Services

Ok, so that I can remember how to do this again. When importing SSIS package from development machine to server and the package protection level is "Encrypt sensitive data with password", this is for example, when you have a package that queries from an Oracle database, and you wanted to encrypt your credentials. I always forget to specify the package protection level. I don't know if it's because of me or the UI, the Protection level textbox is grayed out so one would normally assume that it's not needed. Then one would think that everything is ok until the package is executed, and you wonder why it errors out. So anyway, so I don't forget and to save me time in finding errors that traces back to not specifying passwords properly, I'm taking these notes on how to specify package protection level...

1. In Stored Packages, right click in MSDB then select Import Package...
2. For Package Location, select File System.
3. Specify Package path and Package name.
4. Then click the ellipsis button next to the Protection level textbox...

5. Now select "Encrypt sensitive data with password", then enter password...

The breakpoint will not currently be hit

This is so annoying. I'm using Visual Studio 2005, and for some reason I cannot set breakpoints to do my debugging. I tried several suggestions I can find from the net but still it doesn't work. Since I don't know the solution yet, but will expand this writeup once I figure out. But for me to debug on my ASP.NET Project right now, I added the following in my code. Important thing for me now is to debug my project.

System.Diagnostics.Debugger.Break(); // DEBUG

Constructor Dependency Injection Design Pattern

Note on Constructor Dependency Injection design pattern. This is how it is in C#.

private IContactManagerRepository _repository;

 

public ContactController()

    : this(new EntityContactManagerRepository())

{ }

 

public ContactController(IContactManagerRepository repository)

{

    _repository = repository;

}

Perl Design Patterns

Lookin' at Design Patterns for Perl. Here's the link.

Configuring Job Property Notifications for Sql Server 2005

I spent so much time figuring out how to configure Notifications in SQL Server 2005 job. One of the properties that can be enabled in a Job is "Notifications", when the job succeeds or fails or completes. I wanted to know when the Job I setup fails and so I set it to my E-mail as well as "When the job fails". But one thing about SQL Server Management Studio is that it doesn't tell you if you're lacking some more configurations. I wished it prompted me that I needed to do some other settings for it to work. Anyways, below I just wanted to outline what I did to make this work.

1. Setup Database Mail. Follow this.
2. Setup a Sql Server Agent Operator. Follow this.
3. Now Restart SQL Server Agent.

Thank you for those who wrote the posts I followed above.

Update table with data from another table in SQL Server

Ok, pretty much self explanatory

UPDATE table1 
    SET table1.col1 = table2.col1, 
        table1.col2 = table2.col2
FROM table1, table2
WHERE table2.col = table1.col

Or using alias:

UPDATE A1 
    SET A1.col1 = A2.col1, 
        A1.col2 = A2.col2
FROM table1 A1, table2 A2
WHERE A2.col = A1.col

Best Practices for Query Optimization

I'd like to take note of few tips from Pinal Dave. Certainly good reminders when creating queries.

Configure Module::Starter::PBP in Windows XP

I decided to use Module::Starter::PBP when creating modules. But I got this error the first time:

C:\dev\perl\modules>module-starter --module=Dir::Walk
The Module::Starter::PBP template: t/C:./Documents isn't in the template directory (C:\Documents and Settings\user\.module-starter\PBP)

Some quirky stuffs in Windows when using this module. The initial configuration as outlined in cpan doesn't work. You need to tweak your config a little bit to accomodate this. It's not really the module's fault, but more on the way Windows handles directories, you know, spaces and backslashes, which is really a constant annoyance when you are programming perl in Windows. Anyway, here's what I did to make it work.

1. Move C:\Documents and Settings\user\.module-starter\PBP to another directory, in my case I moved it to C:\dev\perl\modules\templates\PBP.
(It's the spaces in 'Documents and Settings' that's causing the issue).

2. Modify C:\Documents and Settings\user\.module-starter\config as follows: (Note that I changed the backslashes to forwardslashes).

author: Sam Dela Cruz
email: myemail@address.com
builder: ExtUtils::MakeMaker Module::Build
plugins: Module::Starter::PBP
template_dir: C:/dev/perl/modules/templates/PBP

Plugin to Bookmark a Page in Your PDF files

Books in PDF formats are very popular nowadays. But annoying thing is that the Adobe Reader which is the standard PDF reader at work doesn't come with a bookmark functionality. I just need a simple bookmark like those good old paper bookmarks. Well I found one that does the job, a plugin from this post. Thank you very much.

How to make a quick copy of SQL Server database table

Here's how to create a quick copy of a database table including the data in SQL Server.

select * into [dbo].[TableName_Bak] from [dbo].[TableName]

My very first perl6 program

I know, I know, I promised to do some perl6 hacking over the holidays, but as it turned out, I didn't have the time to do it. Having 2 kids in the family and tons of activities lined up over the holidays didn't really gave me much room to doing other things like hacking with my computer. Anyways, another thing I did over the holidays was... tada I got a G1. And I really, really love it! Absolutely amazing piece of work. The downside is, it's going to hurt on our monthly utility bills. So my wife and I were thinking cutting down our cost. We are thinking of giving up our landline, since we both have wireless anyway. But of course wireless minutes aren't cheap either, so we gotta figure out who are actually calling us and who do we call from our landline.

Well, that can be done very easily. Since we are subscribed to comcast, incoming and outgoing phone calls are easily downloadable from their website. And so, this is a good opportunity for me to use the perfect tool for the job. Perl. But this time, I want to use Perl6. And here's what I came up with.

#!/usr/bin/perl6

my %number_of;

my $file = "CallRecords.txt";
if (my $fh = open $file, :r) {
    for =$fh -> $line {
        next if $line !~~ /Answered/ or $line ~~ /Private/;
        my ($name, $phone) = $line.split("\t")<2, 3>;
        %number_of{$name.uc} = $phone;
    }
} else {
    say "Could not open $file";
}

for %number_of.sort.kv -> $name, $phone {
    say $name ~ ' ' ~ $phone;
}

Here's the output:

0 ABXXON ROLLY  (408) 386-XXXX
1 AGUXXAR R & J (408) 251-XXXX
2 AGUXXAR RONNIE        (408) 768-XXXX
3 ALMXXEN FAMILY        (408) 997-XXXX
4 XXER FRATERNAL        (408) 984-XXXX
5 AQXXNO GREG   (408) 297-XXXX
6 AQXXNO GREG R (408) 297-XXXX
7 ARGONCXXLO S  (408) 937-XXXX
8 ARGONCXXLO SUSA       (408) 937-XXXX
9 ASXXAS DAISY  (831) 240-XXXX
10 BALIXXIT VENTUR      (408) 251-XXXX
11 XXNK OF AMERICA      (866) 512-XXXX
12 BEXXER LIVING I      (408) 561-XXXX
13 BOUXXER      CO      (303) 786-XXXX
14 CALIFOXXIA EAR       (650) 494-XXXX
15 CAMXXHO GEMMA        (408) 849-XXXX
...

(I replaced some letters and numbers with X's in this post for security purposes.)

Oh my goodness! What an elegant piece of code that is. And it didn't take me a while to figure it out. I simply read tutorials from Gabor and Jonathan. I tell you, I can't believe the expressiveness of the latest and greatest perl. I have barely scratched the surface, it's got tons of other amazing stuffs! Try it for yourself.

Safari Login

My little secret Safari Login. Just for me to see if I can access Safari from my T-Mobile G1 with Google Phone, Black (T-Mobile).

Yey! It's working, now I can read books from my G1.

Project setup using GitHub

Just taking notes on how to setup project in GitHub. These notes are from GitCasts and GitHub Guides.

1. Setup, Initialization and Cloning

Global setup, these apply to all projects on the system:

git config --global user.name 'User Name'
git config --global user.email username@gmail.com
git config --global color.status auto
git config --global color.branch auto
git config --global core.autocrlf true

To save project in git repository:
1. First, create the project.
2. cd to the project, then do the following:

git init
git add .
git ls-files --cache
git commit -m "Initial Commit"
git status
git log

To clone existing code in the git repo:

git clone git://github.com/sammydc/publictalkscheduler.git

If it's your own repo, use this instead:

git clone git@github.com:sammydc/publictalkscheduler.git 

2. Normal Workflow

Create .gitignore file, this is to specify which files we don't want to add to git, like as follows:

log/*.log
tmp/**/*

Editing files and commiting to repo:

git status
git add [filename]
git commit
OR:
git commit -a -m 'My Next Commit' #automatically add and commit all changed files
git ls-files --stage
git rm public/robots.txt #remove a file, git not to track the file anymore
git status
git commit -m 'robots file removed'

Now push to remote repo:

git remote add origin git@github.com:sammydc/publictalkscheduler.git
git push origin master

Take note that if you get the error message:

fatal: protocol error: expected sha/ref

You need to make sure that .git/config [remote "origin"] url entry is in the form of

git@github.com:sammydc/publictalkscheduler.git

When working on another machine, to get latest changes:

git pull

VIM syntax highlighting for Perl6

Just want to take note on what I did to enable syntax highlighting in VIM for Perl6. I followed this excellent procedure. Thanks Ovid.

Then if you are using NERD_commenter.vim plugin like I do. This is the best commenter for me by the way. You need to add another file type for perl6, as follows:

elseif a:filetype == "perl6"
    call s:MapDelimiters('#', '')

Want to try Perl6? Here's how to get started in Windows.

02/19/2009: UPDATED for Parrot-0.9.1

I have my eyes on the next version of Perl. Perl6. I always wanted to somehow try it but never get the chance. Now that I'm preparing for a long holiday due to company shutdown, I want to play with it. I choose Rakudo Perl, which is an implementation of Perl6 targetting the Parrot virtual machine. As you already know, Perl6 is a set of specifications on this language. One of it's implementations is Rakudo.

Anyway, here's a procedure on how to install Perl6 on Windows XP. Rakudo Perl (Perl6 on Parrot), that is...
1. Download latest Windows build of Parrot and parrot-rakudo addon from here. Total of 2 executables.
2. Install and take all defaults.
3. Now do a smoke testing of the install:

C:\parrot-0.9.1>perl smoke_languages.pl

If returns ok, then congratulations, your rakudo install is successful. If not, then proceed with the rest of this procedure.

4. Go to C:\parrot-0.9.1\bin, create a file and call it perl6.cmd. Have it contain the following:

@echo off
rem adjust the path below to suit your environment
set PARROT_ROOTDIR=c:\parrot-0.9.1
%PARROT_ROOTDIR%\bin\parrot.exe %PARROT_ROOTDIR%\lib\parrot\languages\rakudo\perl6.pbc %*

5. Rename C:\parrot-0.9.1\bin\perl6.exe to perl6.ex_. Just to prevent .exe from running.

Update 02/19/2009, I'm still using the workaround above because when I attempted to use the perl6.exe that came with the rakudo addon, I got this error message:

C:\dev\perl6>perl6 hello.pl
"load_bytecode" couldn't find file 'PCT.pbc'
current instr.: '' pc 405 (src\classes\Object.pir:20)
called from Sub 'parrot;Perl6;Compiler;main' pc -1 ((unknown file):-1)

Since this is a work in progress, I assume that these will all be fixed in the next release.

Now the moment of truth...

C:\dev\perl6>perl6 hello.pl
Hello World!

Hurray! Success. Now I can play with perl6. I plan on looking and writing some test that I can probably contribute back. Thank you so much to you all perl6 team.

By the way, just so you know, this is just a workaround. In the procedure that I found, perl6.exe can be built using a utility called pbc_to_exe, but it didn't work for me. So instead I followed this.

In the future, my wish is to have a one-click-installer for Windows of everything Rakudo. Meaning, an installer that will install Parrot, then perl6.exe or create perl6.cmd and set the environment variable. Which also can handle upgrading to latest build. That would make it easier for people to install and use it. Thanks.

Update 02/19/2009, in parrot-0.9.1, path environment variable is automatically set during install. Then about the issue above, I logged a new ticket in parrot regarding this.

Update 02/20/2009, speedy answer from the parrot team, ticket resolved. So for V.0.9.1, you don't need the workaround outlined above. Thanks a lot parrot guys!

Free book: Higher Order Perl

This is one book I always wanted to have. Now, the author, Mark-Jason Dominus is giving it for free. Fantastic. Here's where you can download: http://hop.perl.plover.com/book/

Thank you very much, Mark-Jason.

Install sqlite3-ruby for Rails on Windows XP

A little bit of trouble when installing sqlite3-ruby on Windows. I get this error message:

C:\ruby\bin>gem install sqlite3-ruby
Building native extensions.  This could take a while...
ERROR:  Error installing sqlite3-ruby:
        ERROR: Failed to build gem native extension.

C:/ruby/bin/ruby.exe extconf.rb install sqlite3-ruby
checking for fdatasync() in rt.lib... no
checking for sqlite3.h... no

nmake

Microsoft (R) Program Maintenance Utility   Version 1.50
Copyright (c) Microsoft Corp 1988-94. All rights reserved.

NMAKE : fatal error U1073: don't know how to make 'ruby.h'
Stop.


Gem files will remain installed in C:/ruby/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.2.4 for inspection.

Results logged to C:/ruby/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.2.4/ext/sqlite3_api/gem_make.out

Here are the steps I followed to install it:
1. Download sqlite-3 and sqlitedll-3 from sqlite download.
2. Unzip the files to c:\ruby\bin. Total of 3 files, sqlite3.exe, sqlite3.dll, sqlite3.def.
3. In command line: gem install --version 1.2.3 sqlite3-ruby

C:\ruby\bin>gem install --version 1.2.3 sqlite3-ruby
Successfully installed sqlite3-ruby-1.2.3-x86-mswin32
1 gem installed
Installing ri documentation for sqlite3-ruby-1.2.3-x86-mswin32...
Installing RDoc documentation for sqlite3-ruby-1.2.3-x86-mswin32...

Enjoy...

Nice Introduction to REST

I'd like to take note of this link to a video by Joe Gregorio. It's a nice introduction to REST architectural style.

jQuery notes

Looking into jQuery. Some of my notes as I go...

It is interesting to note that for the $() function, it removes the need to do a for loop to access a group of elements since whatever put inside the parentheses will be looped through automatically and stored as a jQuery object.

Common examples on using the $() factory function:

Element	Sample jQuery	Meaning
tag name	$('p')	gets all paragraphs in the document.
id	$('#some-id')	gets the single element in the document that has the corresponding some-id ID.
class	$('.some-class')	gets all elements in the document that have a class of some-class.