SSIS Deployment Utility
May 19, 2009 at 1:16 pm | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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
Variable Mapping for SSIS data source
May 25, 2007 at 10:24 am | In SQL Development, SSIS | Leave a CommentTo map a TSQl variable with an SSIS variable, “?” is used as the variable in the SQl command or ifora parameter when calling a procedure.
Then, on the data source select parameters (each “?” will show up as parameter0, parameter1,..), and simply map it to any variable created in SSIS
Blog at WordPress.com. | Theme: Pool by Borja Fernandez.
Entries and comments feeds.
