******* ‘ Declare variables
******* Dim TextStream As Byte()*********** ‘ To hold Text Stream
******* Dim TextStreamAsString As String*** ‘ To Hold Text Stream converted to String
******* Dim StringArray() As String******** ‘ To contain split Text Stream

******* ‘ Load Text Stream into variable
******* TextStream = Row.TextStreamColumn.GetBlobData(0, CInt(Row.Column0.Length))

******* ‘ Convert Text Stream to string
******* TextStreamAsString = System.Text.Encoding.ASCII.GetString(TextStream)

******* ‘ Split string into array and output
******* StringArray = TextStreamAsString.Split(“#”) *******

******* Row.Column1 = StringArray(1).ToString
******* Row.Column2 = StringArray(2).ToString
******* Row.Column3 = StringArray(3).ToString**

using System;
usingMicrosoft.SqlServer.Server;
usingMicrosoft.SqlServer.Dts.Runtime;

namespace Package_Modifier
{
*** class Program
*** {
******* static void Main(string[] args)
******* {
*********** // Initialize an Application and Package object
*********** Application app = new Application();
*********** Package package = null;

*********** // Set a package path
*********** StringpkgPath = “C:\\BI Monkey\\SamplePackage.dtsx”;

*********** // Load the package in package object
*********** package = app.LoadPackage(pkgPath, null);

*********** // Add the new variable
*********** package.Variables.Add(“NewVar”, false, “User”, 0);

*********** // Save the package
*********** app.SaveToXml(pkgPath, package, null);
********* }
*** }
}

• The query can be maintained independently of SSRS, allowing tuning the query without accessing or modifying the reports
• The execution plans can be cached if you use an Stored Procedure, but not if you use SSRS
• Stored procedures allow the use of certain objects that cannot be used in embedded T-SQL in the report such as temp tables and indexes specific to those temp tables as well as table variables
• Stored Procedures provide a layer of abstraction between the report and the business logic
• Stored Procedures allow re-use of similar logic