Friday, November 2, 2007

Web Architecture

I had worked on a web site for one of my client and I am sharing the web architecture diagram for latter use. The web architecture diagram is developed using MS-VISIO tool and the flow for the web architecture is described.

The requirement from the client is to allow the on-line customer to view their account status, contract & payment details, create user, modify profile, send note to admin and subscribe newsletters.

Flow Descriptions

(1) Customer requests for the account status page from the webserver.
(2) ASP.NET renders the welcome page with the login screen and presents the web page to the customer.
(3) Customer types their user id and password and clicks on the [submit] button on the webform.
(4) ASP.NET calls the customer web services to call procedure in database via the sbl db components.
(5) Stored procedure verify the username and password if it is exists in the database table and the result is sent back through the db components, webservice and to ASP.NET.
(6) If the customer is not found within the database , the FEW will send the "Access Denied" screen to the customer.
(7) If the customer is found in the database, FEW invokes a method from the customer web service to call procedure in database via the db components.
(8) Stored procedure execute and retrieve the contract details that is belongs to the customer and the result is sent back through the db components, webservice and to ASP.NET.
(9) ASP.NET renders contract details page and present(s) contract summary & contract details in contract summary tab to the customer.
(10) Customer clicks on either payment summary link, payment due link, send note tab, register tab, modify profile tab and sign up link.
(11) ASP.NET retrieves data from web service that pertains to the selected link or tab via the db components in database and ,
(12) Stored procedure execute (the action or select queries) and retrieve the results and the result is sent back through the db components, webservice and to ASP.NET.
(13) ASP.NET renders the page for the selected link or tab to the customer.

Thursday, November 1, 2007

SQL Server Integration Services Automation

Automation Process

I have created five SSIS package for five source systems. I have scheduled the SSIS package as a job using SQL Server Agent. The jobs have been scheduled to run on every hour on weekdays. You could change the frequency interval at your convenience. The primary (Control Report) job has been enabled by default and rest of jobs is not enabled & rename with suffix as "-- Disabled".

The enabled job would start execute, calculate & set the schedule start date for the subsequent month, rename (suffix as "-- Disabled") & disable the job on success. Rename (remove suffix as "-- Disabled") & enable the next job for execution. The cycle spins for other jobs until the final job for a month.

Let us read further to find out how the "Thought Process" has been executed in SQL Server 2005 using integration services.