Parsing XML with SSIS transformation script
October 14, 2010 at 9:33 am | Posted in SSIS | Leave a commentDim 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)
SSIS Deployment Utility
May 19, 2009 at 1:16 pm | Posted in SSIS | Leave a commentTo 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 commentIf 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 commentUse 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 commentTo 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 commentWhen 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 commentUse 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 commentTo 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 commentTo 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 commentTo 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.
