Saturday, October 29, 2011

SSIS: How to pull Stock Quotes from Google Finance

This post shows how you can use Google Finance to pull the stock quotes in SSIS without using any web services.

Knowing the stock prices of your portfolio is one import thing we do every day. To know the stock prices there are many websites available, which provide free services to create your portfolio and track their prices.

When it comes to pull the stock prices in SSIS there are many task and components which are helpful. One of the favourite components is Web Services Task, Script Task or Script Component. In order to use Web Services Task we need to call a web service which is providing the Stock Quotes free. We can call such web services in Script Task and Script Component as well.

The internet giant Google Finance is also available to achieve the same. In case you want to know the stock price of a company in XML format you can try the following URL from Google Finance.

http://www.google.com/ig/api?stock=RIL

The above URL will show the stock price of India most valuable company “Reliance Industries”. The stock prices are shown in XML format.




You can use the above Google Finance service in SSIS to pull the stock quotes.

For the demonstration purpose I have create a table which has following values and columns.

The table has four columns. StockName, StockSymbol, LastPrice and LastUpdateTime.

The StockSymbol field contains the value which we need to pass as querystring value to Google Finance.

http://www.google.com/ig/api?stock=RIL


To pull the stock quotes of above mention shares in table I created one OLE DB connection in my SSIS project which points to the Database which has the above table. I created following task under the Data Flow tab.


The Read Stocks simply read all the values from table.

The Read From Google Finance is a script component which connect with the Google Finance services and fetch the stock price values.

The Update Stock Prices is a OLE DB Command task which updates the stock prices values back in the table.

I created a package level variable to store the Google Finance URL.

The script component Read From Google Finance uses the System.XML namespace to read the XML values return by the Google Finance. I have setup the ReadOnlyVariable values to User::googleURL. I have following codes in the script component.


As you can see in the code we are pulling the stock price Last values and updating it to LastPrices column. You can read values such as high, low, volume, market capitalization etc. from the XML values return by the Google finance.

The OLE DB Command Update Stock Prices has following SQL Update statement.


After setting up the Data Flow Task components I connected with the internet and run the package. It runs successfully and pulls the stock prices values and updated them in the table.



This post shows one of the ways we can use to pull the stock prices of our favourite shares. There are many ways to accomplish the same in SSIS.

I hope you enjoy reading the post. Thanks for the reading.

If you want to explore more with the Source code of this post; please visit Download Zone.



Related Article:
Labels: , ,

Tuesday, October 25, 2011

How to Map your Google blog with your own Domain?

This post shows how to setup your domain with your Google Blogspot blog if you have bought the domain from www.net4.in

Recently I bought my domain www.singhvikash.in from net4.in. My blog at Google blogger site is www.singhvikash.blogspot.com. I wanted to map my domain with my blogspot site.

What it takes to map your own domain with Google blogspot?

In order to map your domain with Google blogspot you need to have two things ready

1. Your own domain should be ready with you
2. Your Google blogspot address should be available with you

There are many vendors from which you can buy a domain such as godaddy.com, names.com, net4.in etc.

I bought my domain from net4.in. The only thing I consider was price and availability. This was my mistake which I realized later. After buying the domain I was happy that I will map this domain with my blog site.

How to setup your own domain with Google blogspot?

The primary thing we have to do is to change the DNS settings for the property called CNAME. CNAME stand for Canonical name. This property in the Domain Name System tells that the domain is an alias of another site. What it means is if I setup the CNAME value of my domain to Google blog site; then all visitors visiting my own domain name will be actually visiting Google blog site. My own domain name will be just an alias of my Google blog site.

In case you want to map your domain with Google blogspot you need to set this value to ghs.google.com

Setting the CNAME property value depends on the domain registrar. This is pretty easy with godaddy or other domain name registrar but the same is not very clear with net4.in.

Setting the CNAME property with net4.in was the challenge. When I first login and check the user control panel, I do not find anything related with CNAME. I did some research on internet and I was shocked and sad that people who have attempted this before with net4.in have very negative feedback. I felt sorry that I bought it from net4.in and moreover I was cursing myself that I did not do a through research. I send an email to net4.in and they replied back in less then 4 hours. The instruction was quite simple. This was there reply:

This is in reference to your query; we would like to inform you that kindly follow the below mentioned steps for changing the Cname:

Logon to net4.in with the help of CRNID & password > My Dashboard > Click on the Domain> Go to DNS management>Go to Sub domain Create/Manage >Click on the Add new button > In the open window, there are two options, A record & CNAME entry > In the case of CNAME entry, select the CNAME option button and mention the alias name is first box like (mail, docs, sites & calendar as per Google) and mention the CNAME entry in the second box kindly put destination of the Cname [like google.com or ghs.google.com without dot[.] Click on Create now. For any further assistance do contact us, on 39891414 (from Landline), from Mobile phone dial city code as prefix.


So basically I needed to create a subdomain and set its CNAME property value to ghs.google.com as shown in below picture.


After setting the CNAME property; we need to set the A-Record values. The A-Record is a property in Domain Name System which point to IP address. I did some research which point me that the values should be setup as following IPs.


After setting the A-Record property I was all set that I am done with the setting part from Domain side. Well this is the first part of the story.

The second part is you need to tell your blogspot about this setting. For this you need to login into blogspot site and navigate to Dashboard || Settings || Publishing || Switch To Custom Domain || Write your own domain name --> Save Settings.

After completing the second part you probably need to wait for 24 to 48 hours to reflect the same. Once this is done anyone visiting your site will be redirected to your blog but his address bar in the browser will reflect your domain name and same will happen if any one visiting your blogspot site will see your domain in the address bar.

That is what I did to setup my domain I bought from net4.in to map with my google blogspot.

Thanks for reading this post.
Labels: , , , ,

Saturday, October 22, 2011

SQL Server 2008: Encrypt/Decrypt data through SQL Pass-Through Query

All of us coming from SQL Sever background knows that grand father (SQL Server 6.5, 7.0 or 2000) of current SQL Server 2008R2 had no built-in mechanism for encryption/decryption of data/files/login. Built-In mechanisms for encryption/decryption are available from SQL Server 2005 onwards.

To encrypt the data/data files in SQL Server 2000 environment we had to use the 3rd party tools such as Encryptionizer from NetLib which provides database encryption and column-level encryption.

The second option is to go for certification based authentication and SSL encryption which are provided by Certification Authority such as VeriSign etc and at SQL Server client end we need to enable the Force Protocol encryption.
The third option is to developing manual ENCRYPT LOGIC for source data and DECRYPT LOGIC for target data. First run the encryption logic on the source data to encrypt the data in tables. Later to decrypt the data run the decryption logic.

Recently, I had a challenge to encrypt or decrypt the data in some specific tables stored in SQL Server 2008 and Ms-Access 2003 databases. The challenge was that Ms-Access database had link tables and these link tables were pointing to a tables and database which was in SQL Server 2008.

The requirement was to encrypt the data in some specific columns at SQL Server 2008 end but the same data was needed in Ms-Access in decrypted format. Essentially the data at SQL Server end will always remain decrypted.

This is achievable through many mechanisms. One of the solutions is SQL Pass-Through Queries in Ms-Access

What is Pass-Through Query?


A Pass-through query is jus like any other query in ms-access with the exception that it executes SQL statements against the tables in an external database (ex:- SQL Sever, Oracle etc.). It uses ODBC connection to access the data in the external database (ex:- SQL Sever, Oracle etc.). So if you have some UDF at SQL Server end you can use it with Pass-through query because the SQL statements will get executed at database level and not a ms-access level.

Encrypt data in SQL Server 2008 and Decrypt in Ms-Access using Pass-Through Query

In order to demonstrate the solution using Pass-Through query I created a database “EncryptTest” at sql server level. I created a table “Employee”. This table has two columns EmpID and EmpName.



I inserted some employee data into Employee table.


I encrypted the column EmpName using ENCRYPTBYPASSPHRASE function which is available with SQL Server 2008. After encryption the column data looks like following.


I created a Pass-through SQL Query in Ms-Access with the name SQLPassQuery. This query was to decrypt the encrypted Colum (Empname) and returns the decrypted value. To create a Pass-Through Query we need to setup the ODBC connection string in the query properties.


The next thing I did was to create a Make Table query “CreateLocalTable”. This query creates a local table “Decrypted_Local_Table”.


After running the make table query it created a local ms-access table named “Decrypted_Local_Table”. The table had EmpName column values in decrypted format.


While the SQL-Pass query returns the decrypted values at the same time sql server table had encrypted values.


I hope you enjoy the reading the post. Thanks for reading!!!

Related Article:
Labels: , , , ,

Sunday, October 16, 2011

SSIS: Checkpoints implementation

This post is an example of SSIS Checkpoints implementation.

To see the Checkpoints mechanism in action I created a very basic package.



The package has two tasks.

Task A: This task is an Execute SQL Task and reads the current Date time of SQL Server and assigns it to a local variable (User::varDate). The SQL Statement is very basic

SELECT getDate() as 'TodayDate'

Task B: This task is an Execute SQL Task and calls a stored procedure. The stored procedure inserts the records in a table called [Emp1]. Stored procedure code logic is pretty basic.

CREATE PROCEDURE [dbo].[vksAddEmp](
@EmpName varchar(50),
@Age int,
@DOJ Datetime) AS
BEGIN
INSERT INTO HumanResource.dbo.Emp1 values(@EmpName,@Age,@DOJ)
END

To implement the Checkpoints at package level; the #1 thing I did was to ENABLE THE CHECKPOINT AT PACKAGE LEVEL Checkpoints feature at package level by setting up the following properties:



#2 I have CONFIGURE TASK(s) FOR CHECKPOINT AT CONTROL FLOW TAB by setting following properties for Task A and Task B.



After setting up the #1 and #2 pre-requisite, I run the package which got executed successfully. Since package was executed successfully no checkpoint files were created.

Now to generate an error at Task B, I removed the stored procedure (vksAddEmp) which the Task B is calling. This is done intentionally to generate errors. After that I executed the package and got the error in Task B. The package fails.


Since the package has failed it generated a Checkpoint file named myCheckpoint. I checked the file location and it exist.



When I open the checkpoint the content of the Checkpoint were visible as well.


The Checkpoints mechanism has generated the Checkpoints file.

Now I re-created the stored procedure which has been used in Task B and re-run the package. The Package runs and started with Task B which was the point of failure.




So in the above example we have seen the Checkpoints mechanism in action. It was a very basic example but the example shows that if applied correctly; Checkpoints are useful tool.

Thanks for reading the post!
Labels: , ,

SSIS: What is CheckPoints?

When everything runs smooth in life…it feels great…right?

The same apply to SSIS.

When the SSIS package you have deployed in production is running smoothly; it feels great. But an SSIS package can fail due to many reasons. The reason can be attributed to various factors but not limited to the list below:

• Source, Target or Staging servers are down
• FTP connectivity or network connectivity has been lost.
• Bad data in the source file/table.
• User permission has been denied at sql server end.
• SMTP Server is not available when sending email via SSIS

… and many more

So what if your package was in the middle of execution and some adverse reasons have failed it? What will be your next step?

…The choice is to debug and find the exact reason for failure and resolve it. After that re-run your package…correct?

You can do that, but SSIS has provided a mechanism to restart your package from the point of failure. Yes that is right you can run your SSIS package from the task (onwards) which have failed. This mechanism is called Checkpoint.

For example if you have five task in your package with the below status i.e. Task A and Task B has been completed successfully and Task C has been failed.

Checkpoints provides the system by which you can re-run your package from Task C onwards. That’s right. With Checkpoint mechanism you do not need to run your Task A and Task B again as you have already run them successfully in the first run.


What exactly is Checkpoints?

Checkpoints are mechanism in SSIS which stores the information in an XML file when the package is executing. This XML File is called Checkpoint file. This file stores following information:

- Task which have been completed successfully.
- Value of Package variables at the time of failure.

Facts about Checkpoints:

- By default Checkpoints are disabled. To enable the Checkpoints you need to set the following properties at package level.


- When a package is successfully executed, Checkpoint files are removed. So for each successful package completion there is no Checkpoint file.
- When a package fails, the Checkpoint files remains in disk. This file information is used when the package is re-executed. The package status is restored and variable values are re-assigned. In other words Checkpoint feature is there to help you when the package fails. If you package do not fails there is no checkpoint file available for you.
- Checkpoint file do not store the value of Object variables.

This is important to know that Checkpoint mechanism track the success or failure of package at Task level i.e. at Control Flow level. SSIS Checkpoint feature do not keep track of success or failure at Component level i.e. at Data Flow Task level. What essentially it means is with Checkpoint you can re-start your package at task level but not at the component level.

Using Checkpoints mechanism is two step tasks.


#1. Enable the Checkpoints at Package Level
#2. Configure Task(s) for Checkpoints at Control flow tab.

#1. ENABLE THE CHECKPOINT AT PACKAGE LEVEL

At the package level you can set the following properties to ENABLE THE CHECKPOINTS.

CheckpointFileName:

This property is use to define the filename and file location that you want to use for Checkpoint file. In case of package failure you can navigate to that file location and open the file to verify any information you might want to. You can use either a hard code file name or use an expression. By default this property is not set; it remains blank.

CheckpointUsage:

This property has three possible values you can choose from:

Never: This is the default value which indicates you are not using Checkpoints.
ifExists: This value tells that SSIS package should use the Checkpoint file if it exists to restart the package from point of failure by restoring the variable values. If there is no file available start the package execution from very first task.
Always: This value tells that SSIS package should always use Checkpoint files while executing. If the checkpoint file do not exist; it will throw an error.

SaveCheckPoints:

This property has two values – True or False. To implement the Checkpoints mechanism in your package you should set the value True.

Ok..now by setting the three above properties you have enabled the Checkpoint mechanism at the Package Level. #1 step to implement the checkpoints are complete.



#2. CONFIGURE TASK(s) FOR CHECKPOINT AT CONTROL FLOW TAB.

The second step is performed for each task that you want to include in your Checkpoints strategy. For each task you have to setup the following properties:


FailPackageonFailure:

This property has two values – True or False. By setting the value to True, we are telling to SSIS that if this task fails, SSIS package must fail. In a nutshell, this task becomes the point of failure. If this task fails the package will fail. Next time when the SSIS package will be re-executed (after resolving the failure reasons), the SSIS package will start the execution from this task onwards.

FailParentonFailure:

This property has two values – True or False. This property is set when the task is child control inside a Sequence container. By setting the value to True, we are telling to SSIS that if this task fails, the parent task must fails. In a nutshell, this task will force the parent to fails and parent must fail the Package. To achieve this we need to setup FailPackageOnFailure property to true at the parent level (ex:- Sequence containers) and at the child level we need to set FailParentonFailure value to true.

After completing the #1 and #2, you are all set to use CheckPoint at Package level. We will have a demo post on this soon.

Thanks for reading this Post.
Labels: , , , , ,

Wednesday, October 5, 2011

PM: How accurate is your Project Cost Estimates?

As a project manager estimating cost is the first step you take towards creating a Cost Management Plan. How accurate your estimate should be, must be plan. There has never a project under the sun which matches with the exact cost estimated. I mean to say there has never been a case where you estimated that project will cost $12,067 and after closing the project the project expenditure exactly matches with the $12,067.

So how accurate the estimates should be?

Estimates must be in a range. A range which has a lower and upper limit. Example:- A project during the planning phase might have an estimates range of +-50% of the estimated amount.

When a project is in planning phase there might be many requirements which are not very much visible. So estimating during this phase will have a wide range.

When you do the estimates in later phases this gap will be narrowed as you know more details about the project.

Estimates should be based on a WBS (Work Break Down Structure) if at all possible. Estimates if done by the resource who has to work on the activities is more realistic.

Estimates done during the project phases are following:

ROM Estimates (Rough Order of Magnitude)

This type of estimates is done during the project initiating phase. The estimates could be in the range of +-50%. This wide gap is attributed to the reason that at this phase project baselines are not ready; project clarity might be vague etc.

Budget Estimates

This type of estimates is done during the project planning phase. The estimates could be in the range of -10 to +25%. At this stage project clarity has been sharpen well. The baselines (Scope, time) are very much known.

Definitive Estimates

This type of estimates is done during the later phases. The estimates could be in the range of -10 to +10%. Project has very much clarity. The project team knows much more details about the project.

How Estimates can be reduced?

An experienced project manager knows how to reduce the estimates. Remember in my post Do you know your Project Cost, we outlined that what do we estimates about?

…So we can cut on those areas to cut the estimates…

We can reduce the risk to decrease the cost. We can opt for a cheaper resource or may be cut on qualities; it all depends on the nature of projects.

So know we can be assured that Estimates are never a fixed cost. They come with a range.

We will cover more on Cost Management in our future posts…till then please feel free to add your comments:

Labels: , , ,

Tuesday, October 4, 2011

SSIS: Event Handlers

This post is an introduction of Event Handlers in SSIS.

Event handlers in SSIS are used to handle events. The events at SSIS level could be an error, a warning, information, a pre/post execution of a particular task, a variable value changes etc. SSIS provides flexibility to trap the events raised by executables at run time and do the processing as required by the user.

Event handlers tab is the place were the event handlers are defined.




To see the Events related to a specific control flow task, you can switch to Event Handler tab and select the particular Control flow task.



To demonstrate an example I selected the Data Flow Task, the events related with DFT were shown in the Event handler combo box.


We will see an example where the Data Flow Task Start Time and End Time can be captured at the Event Handler tab.

We can place a script task onpreExecute and onPostExecute events to show the Start and End Time of the DFT package.


MessageBox.Show("Package Start Time:" + DateTime.Now.ToString());

Inside the Script Task we are showing the date and time only:


MessageBox.Show("Package End Time:" + DateTime.Now.ToString());

Once we run the package we will get the message box which shows the start time and end time of DFT (Data flow task)


I hope you like the post.

For more information on Event Handlers you can visit this URL: http://msdn.microsoft.com/en-us/library/ms140223.aspx
Labels: , , ,

PM: Do you know your Project Cost?

This blog post is about Cost Management concepts.

What is cost?
Cost is one of the three most import factors in the Project Management. The other two are Scope and Time. Pictures speak more then words, so the three important factors of project management can be shown in below picture. Quality is the center theme of this triangle.




What is Cost Management Process?

Cost Management Process involves three Cs.
EC = Estimate cost
DC = Determine cost
CC = Control Cost

When do you estimate cost, determine cost and control cost?

Estimate cost occurs in the project planning phase. Determine cost occurs in the project planning phase. Control cost occurs during the monitoring and controlling phase.

What is Cost Management Plan?

Cost Management Plan is developed as part of Project Management Plan.
Cost Management Plan primarily includes:

• What currency (dollar, Euro, INR etc) will be used in the estimates?
• What level of accuracy is required for the estimate?
• What will be the reporting format?
• What will be the cost types in the project?
• What rules will measure the cost?
• What will be the control thresolds?

…etc.


What are Cost types?

Cost can be divided into following types:

Direct Cost and Indirect Cost:

Direct costs are related to the project work. Example: - team member’s salary, travel, cost of materials or equipments used in the project etc.

Indirect costs are overhead costs for the benefit of more then one projects. Example:- Taxes.

Variable Cost and Fixed Cost

Variable costs are those which changes with the amount of supplies or production. Example: - Cost of materials, supplies, labor cost etc.

Fixed Cost is those which do not change with the amount of production or supplies.
Example:- Project set-up cost, rental costs etc.

So how does a Project Manager Estimate Cost?

To estimate the cost it is must to know WHAT IS ESTIMATED. If you know what you have the estimates things becomes easy. Following list will help to understand what is estimated.

• Quality efforts cost
• Risk efforts cost
• Project Manager Time cost
• Project Management activities cost
• Direct Costs (team member’s salary, training, paper, pencil, materials etc.)
• Indirect Costs (Management general office expense, project set-up, rental etc)

So how estimates are done?

To do the estimates there are many methods which can be used. Following list contains the methods which a project manager can used to do the estimation:

• One-Point estimates
• Analogous estimate
• Parametric estimate
• Three-Pint estimate
• Bottom-up estimate

…etc


So what can be used for to the estimates?

From the many things a Project manager need scope baseline, project schedule, risk register, human resource plan (labor rates etc) to do the estimates. He/She needs to know the company policies on estimating. He should have access to the lesson learn; historical information’s to do the estimates.

…More on the project cost soon…till then please feel free to add comments about the post.
Labels: , , , ,
Subscribe to: Posts (Atom)

Popular Posts

Real Time Web Analytics