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
Variable Mapping for SSIS data source
May 25, 2007 at 10:24 am | Posted 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
Calling C# Assembly from SSIS Script Task
May 24, 2007 at 4:09 pm | Posted in SQL Development, SSIS | 2 CommentsTo call a C# assembly from an SSIS Task you need to do the following:
1- Build the C# with strong naming ( SDK Command :>sn /K c:\key.snk, add the Key.snk to the project and use it to sign it)
2- Add the Assembly to the CAG (VS Command:>gacutil /i AssemblyName.dll)
3- Copy the assembly to the “C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727″ folder for the Develpment or Production server
4- Form the SSIS Script designer, add the reference as it should appear in the list.
5- Import the library and instentiate an object to be used for the task
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports SQLReportingServices.SQlReportingServices
Public Class ScriptMain
Public Sub Main()
Dim SqlServer As String
Dim FileName As String
Dim FolderName As String Dim WhenUploadedName As String
Dim MiMe As String
SqlServer = http://ServerName/reportserver/reportservice2005.asmx
FileName = “c:\ExcelSheet.xls”
FolderName = “/Reports”
WhenUploadedName = “Data Sheet.xls”
MiMe = “Excel”
Dim SqlReportingService As SQLReportingServices.SQlReportingServicesSqlReportingService = New SQLReportingServices.SQlReportingServicesSqlReportingService.CreateResource(SqlServer, FileName, FolderName, WhenUploadedName, MiMe)
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Maximum Request Length Exceeded – SQL Reporting Service
May 24, 2007 at 3:59 pm | Posted in Reporting, SQL Development | Leave a commentWhen using CreateResource( ) method or directly try to upload a file into the Reporting Server if the file size is greater than 4MB, an exception will be raised: “Maximum Request Length Exceeded”
To fix the problem, the “maxRequestLength” in the web.config should be added and given the desired max file
<httpRuntime
executionTimeout = “9000“
maxRequestLength=“10000“/>
Blog at WordPress.com. | Theme: Pool by Borja Fernandez.
Entries and comments feeds.
