SSIS - Script Component - Variables Access Error

I was developing an SSIS package and I encountered the following problem.
I had a OLE DB source that is providing me data from a SQL table in my data flow task. I had a script component following it. I am trying to concanctenate my one of the string column to a variable and trying to use it in other task.
So I had my code like this in my inputprocessrow method.
--------------------------------------------------------------------------------------
While Row.NextRow
myval = myval + " , " + Row.customer
End While
----------------------------------------------------------------------------------------
And i was trying to set the local variable value to my Pacakge variable collection and I encountered following error.
"The collection of variables locked for read and write access is not available outside of Post Execute", which drove me mad.

After googling for some time got this info.

You need to have a PostExecute method and try assigning it there.

-------------------------------------------------------------------------------------
Public Overrides Sub PostExecute()
Variables.Customers = myval
MsgBox("Variable Values is " + Variables.Customers)
MyBase.PostExecute()
End Sub
-------------------------------------------------------------------------

SQL Injection Tip

Hope everyone is aware of SQL Injection attacks. For people who are new, it is something about intruding in to your SQL Server (DB) by passing some commands through a request.

If the following (0x73656C656374206E616D652066726F6D207379732E6461746162617365733B) value is passed to the SQL Query, it will list all the DBs on the server. Something interesting. So whenever you get a request, check for it.

Service Broker - An Alternate to Messaging MS SQL Server

1. Enable Service Broker in the DB.
Use Master;
Go
ALTER DATABASE AdventureWorks SET ENABLE_BROKER;
go

2. Create Endpoint to send / receive messages from outside of SQL Server Instance.
- Create Endpoint with Port number and authentication level
Use Master;
go
CREATE ENDPOINT ExampleEndPoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 3133)
FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS KERBEROS);
go

Once Service Broker in STARTED state it will send /receive Messages. To stop either deacticate / pause

Use Master;
Go
ALTER ENDPOINT ExampleEndPoint
STATE = STOPPEDl
Go

To start again
Use Master;
Go
ALTER ENDPOINT ExampleEndPoint
STATE = STARTED
Go

3. TO Forward Message from One instance to another you need to have service endpoint started and follow the below syntax to Enable /disable forwarding. The Size mentioned is in MB

Use Master;
go
ALTER ENDPOINT ExampleEndPoint
FOR SERVICE_BROKER (MESSAGE_FORWARDING = ENABLED, MESSAGE_FORWARD_SIZE = 15);
go

Use Master;
go
ALTER ENDPOINT ExampleEndPoint
FOR SERVICE_BROKER (MESSAGE_FORWARDING = DISABLED);
go

4.Message Transport
BABP (Binary Adjacent Broker Protocol) and Dialog Protocol.
BABP - Basic message Transport, Bi-directional and multiplexed. Role to send messages.
Dialog - handles end to end communication and message ordering as a one-time-only in order delivery system. authenitcation and encryption is responsibility.

Steps to Create SErvice Borker:

1. Define Message Type and Validation
2. Define Contract
3. Create 2 queues sender and receiver
4. Create service and bind them to queue
5. Begin the conversation by sending messages.

Interesting SQL Queries

• Query to find Procedures that contains a particular string in MS SQL 2005

select O.name from sysComments C join sysObjects O on O.id = C.id where O.xtype = 'P' and C.text like '%string%'

• Query to find all the user-defined tables in a specified database

USE db;
GO
SELECT *
FROM sys.tables;
GO

• Query to find find all the tables and indexes that are partitioned

USE db;
GO
SELECT SCHEMA_NAME(o.schema_id) AS schema_name,OBJECT_NAME(p.object_id) AS table_name,i.name AS index_name,p.partition_number,rows
FROM sys.partitions AS p
INNER JOIN sys.indexes AS i ON p.object_id = i.object_id AND p.index_id = i.index_id
INNER JOIN sys.partition_schemes ps ON i.data_space_id=ps.data_space_id
INNER JOIN sys.objects AS o ON o.object_id = i.object_id
ORDER BY index_name, partition_number;
GO

• Query to find all the statistics on a specified object

USE db ;
GO
SELECT name AS statistics_name, stats_id,auto_created,user_created,no_recompute
FROM sys.stats
WHERE object_id = OBJECT_ID('');
GO

• Query to find all the statistics and statistics columns on a specified object

USE db;
GO
SELECT s.name AS statistics_name, c.name AS column_name, sc.stats_column_id
FROM sys.stats AS s
INNER JOIN sys.stats_columns AS sc
ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
INNER JOIN sys.columns AS c
ON sc.object_id = c.object_id AND c.column_id = sc.column_id
WHERE s.object_id = OBJECT_ID('');
GO

Reporting Services 2005 Problems

I was creating some SSRS reports (both in Server and local mode) as part of my project work and i need to admit that i had some tough times for some of the issues.

• First of all I had issues in installing SSRS 2005 in my Windows Vista Premium OS and after googling it for some time, I came across a fact that IT IS NOT POSSIBLE TO INSTALL SSRS IN WINDOWS VISTA HOME PREMIUM edition. Refer following URL to install in other versions of vista.
  http://msdn2.microsoft.com/en-us/library/bb630430.aspx
• I had some class object that would be created and needed to populate in reports. For example, I had Employee object that had his project details, which is collection of Project object. To display project details in the report, I should create subreport to display Project details and would embed that in main Employee Report.
• When there is nested hierarchy of objects that needs to be displayed in Report, then there is syntax to be followed to do so. Let's take a scenario of Employee has Address object, Address object has City object that has name attribute. To display CityName in the Report, we need to follow syntax like "=Fields!Employee.Value.Address.City.Name". I lost some time in learning it.
• Another is to display the particular field in the Report Header that would repeat in all pages. For example, there is Employee object that has Employee Id that needs to be printed in all the pages of Employee Report. It is not possible to refer the field in the Header directly. There are some roundabouts to do that. The way I chose is to Create a report parameter and set the value at run time to have it populated.
• Very good resource for SSRS Tips.
  http://msdn2.microsoft.com/en-us/library/bb395166.aspx

Message Maintanance Architecture.

Here is my article I wrote some timc back about Message Maintanance Architecture for Web Application. Have a glance and let me know about any short falls or enhancements. I would like to have some comments on this.

http://www.codeproject.com/KB/cs/messagehandling.aspx

Microsoft - Tech Mela - Jan 2008

Visit following link to have a look at latest Microsoft Tech Mela Presentations and Videos.

http://msdn2.microsoft.com/hi-in/bb735929.aspx

Some of the videos are below par addressing very low level issues. For example, Peroframance and security video on web applications. Expected more than the content. Anyways, good refresher course.