Osama Assaf

Parsing XML with SSIS transformation script

October 14, 2010 at 9:33 am | Posted in SSIS | Leave a comment

The ssis query component runs a query on the database and return the XML data in text

The transformation script will read the input row, parse the blob ito string and then parse the XML data embeded within the text

Dim xml String As String= System.Text.Encoding.ASCII.GetString(Row.MESSAGE.GetBlobData(0, CInt(Row.MESSAGE.Length)))

Dim sr As New System.IO.StringReader(xmlString)

Dim doc As New Xml.XmlDocument doc.Load(sr)

Dim reader As New Xml.XmlNodeReader(doc)

While reader.Read() Output0Buffer.AddRow()

If reader.Name = “TagName” Then

Try

Output0Buffer.PRECORDID = reader.GetAttribute(1)

….

SSIS Deployment Utility

May 19, 2009 at 1:16 pm | Posted in SSIS | Leave a comment

To create an SSIS package deployment, use the properties on the SSIS project

Once theCreateDeploymentUtility option is selected (True) and the project is rebuild, a wizard file is created in the \bin folder which can be excuted and will proceed.

Managing transactions

May 19, 2009 at 12:53 pm | Posted in IT | Leave a comment

If you have a container within the Control Flow and you want the transaction to rollback if one of the tasks withing the container fails, then you need to do the following:

1- On the package level, make sure that transaction option is Supported

2- On the container, make sure that the transaction option is Required

3- On the sub tasks, make sure that transaction option is Supported

Writing to an SSIS variable within a dataflow script component

January 31, 2008 at 9:32 am | Posted in SQL Development, SSIS | Leave a comment

Use PostExecute() method to write to an SSIS variable within a script component.

Public Overrides Sub PostExecute()

Dim dtsVariables As IDTSVariables90

VariableDispenser.LockOneForWrite( “Error”, dtsVariables)‘ set error’s state

dtsVariables.Item(0).Value = errorsEnd Sub

Create a linked server for MySQL

January 30, 2008 at 1:21 pm | Posted in SQL Administration, SQL Development | Leave a comment

To create a linked server for MySQL on SQL 2005 you need to do the following:

1- Install MySQL ODBC Driver 3.51 or later

2- Create a System DSN using Windows ODBC Data Source Administrator for the MySQL server providing the hostname, username, password and the databasename

3- On SQL 2005, create a linked server for a Microsoft OLE DB Provider for ODBC Drivers

4- Provide any name for the Product Name, for the Data Source, use the System DSN created, on the security tab, map an SQL 2005 account to the MySQL remote user (Probably same used when configuring the Sustem DSN), on the Server Options, enable RPC and RPC Out

5- On SQL 2005 Linked Servers, Providers folder, select the properties for MSDAQL and enable Nested Queries, Allow Inprogress, Level Zero Only, and Support Like operator

6- Using SQL 2005 Surface Area Configuration tool, Services, Enable Both Local and Remote for the Remote Connections

7- Restart the SQL instance

Sample Query:

SELECT * FROM <MySQLLinkedServerName>…<Table/ViewName>

Using expressions for task precedence constraint

July 10, 2007 at 11:11 am | Posted in IT, SQL Development, SSIS | Leave a comment

When using a variable in evaluating a precedence constraint, the variable should start with @, e.g. variable User::Count should be refered to as @Count

Symmetric keys in SQl 2005

June 25, 2007 at 9:24 am | Posted in IT, Programming, SQL Development | Leave a comment

Use Symmetric Keys to encrypt the content of your data before inserting into the database.

Create Symmetric Key:

CREATE SYMMETRIC KEY MyKey WITH ALGORITHM = DESENCRYPTION BY PASSWORD = ‘MyPassword’

Open Symmetric Key for use:

OPEN SYMMETRIC KEY MyKey DECRYPTION BY PASSWORD = ‘MyPassword’ Encrypt data using Symmetric Key:

SELECT ENCRYPTBYKEY(KEY_GUID (‘MyKey’),‘Hello World’)

Decrypt data using Symmetric Key:

SELECT CONVERT(VARCHAR,DECRYPTBYKEY(ENCRYPTBYKEY(KEY_GUID (‘MyKey’),‘Hello World’)))

Explicit transactions using SSIS

June 21, 2007 at 10:35 pm | Posted in SQL Development, SSIS | Leave a comment

To use explicit transactions in SSIS the database connection option RetainSameConnection should be set to True. This allows all transactions to run in the same connection

Adding a .NET library reference to SQL 200X Reports

June 15, 2007 at 11:40 am | Posted in Reporting, SQL Development | Leave a comment

To reference a .NET library from an SQL report, the library need to be copied to the right location.

Different locations are used for Developer tools and Production servers

SQL 2005 Reporting

Deveoper Machine: C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies

Production Machine: C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\bin

SQL 2000 Reporting

Deveoper Machine: C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer

Production Machine: C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\bin

Changing SSIS Variable value while executing a package

May 25, 2007 at 10:55 am | Posted in SQL Development, SSIS | Leave a comment

To change the value for an SSIS Package while executing use the “Set Values”.

Add a property path for the variable (You could also use it for other objects, e.g. loop counter).

Property Path : Package.Variables[VariableName].Value

Blog at WordPress.com. | Theme: Pool by Borja Fernandez.
Entries and comments feeds.