Extending SSIS with Custom Tasks
Platinum
Gold
Silver
www.sqlbits.com
Group BY:
[Food and Drink at Reading Bowl, see you there!]
Feedback Forms:
[Voucher for £30 book on return of Form]
Lunch Time Sessions:
[Idera in Everest, Quest in Memphis, Grok in Chic 1 and 2]
Learn & Enjoy
[Put your phone on Vibrate!]
Ask The Experts
[Sessions need to finish on time, take questions to the ATE area]
Extending SSIS with Custom Tasks
Extending SSIS with Custom Tasks
Darren Green
Konesans Ltd
Agenda
•
Extending SSIS
–
Types
–
Six Steps
–
Tasks
–
Debugging
–
Installation
–
When and Why?
Types
•
Task
•
Pipeline Component
–
Source
–
Destination
–
Transform
•
Log Provider
•
For Each Enumerator
•
C
o
n
n
e
c
t
i
o
n
M
a
n
a
g
e
r
Six Steps
•
Create project
•
Add references
•
Add signing key
•
Write code
•
Compile
•
Install
References
•
Microsoft.SQLServer.ManagedDTS
–
Microsoft.SqlServer.Dts.Runtime
•
Microsoft.SQLServer.DTSRuntimeWrap
–
Microsoft.SqlServer.Dts.Runtime.Wrapper
•
Microsoft.SqlServer.PipelineHost
–
Microsoft.SqlServer.Dts.Pipeline
•
Microsoft.SqlServer.DTSPipelineWrap
–
Microsoft.SqlServer.Dts.Pipeline.Wrapper
•
Microsoft.SqlServer.Dts.Design
–
Microsoft.SqlServer.Dts.Runtime.Design
[ObjectAttribute(
Information
)]
public class MyCustomObject : BaseClass
{public override void BaseMethod()
{// Custom code
}
}
Custom Objects - Template
Classes & Attributes
Demo
Simple Task
Tasks
•
Methods
–
InitializeTask
–
Validate
–
Execute
•
Properties
–
Use attributes, editors and type converters
–
Use connection GUID
–
Write only for security when required
–
Custom persistence for sensitive data or complex
types
More Task Features
•
Custom Events
•
Custom Log Entries
•
Breakpoints (IDTSBreakpointSite)
Create a Task User Interface
•
Create UI class (Implement IDtsTaskUI)
•
Create UI form
•
Set task’s UITypeName attribute
Demo
Simple Task User Interface
Advanced Task User Interfaces
•
Microsoft.DataTransformationServices.Controls
Microsoft.DataTransformationServices.Controls.dll
•
DTSBaseTaskUI Base Form Class
–
DTSTaskUIHost Control, paged control
–
Class per page, implement IDTSTaskUIView
UI Classes & Attributes
Debugging
•
Designer – Design time
–
Attach to devenv.exe
•
Designer – Runtime
–
Attach to DTSDebugHost.exe
•
Component Project – Runtime
–
Start external program
C:\Program...\DTExec.exe
/F "C:\...TestPackage.dtsx”
Debugging Tips
•
Ensure class and strong name is fixed at
start
•
Must restart BIDS between builds, slow!
•
Use DTExec for all runtime
•
Use Post Build Event for install
–
copy "$(TargetPath)“ C:\Program...
–
gacutil.exe" /if "$(TargetPath)“
•
Ensure persistence complete
Installation Locations
•
Designer Enumeration Folder
–
Designer location only (x86 Only)
C:\Program Files\Microsoft SQL Server\90\DTS\<Object>
\Connections
\LogProviders
\ForEachEnumerators
\PipelineComponents
\Tasks
•
Global Assembly Cache
–
Runtime loading by execution host
Install Platform Target 32-bit (x86)
•
Install in Program Files for Designer
•
Install in GAC for run-time
•
Support for x86 specific targeted assemblies
•
Support for Any CPU / MSIL
Install Platform Target 64-bit (x64)
•
Support for x86 only targeted assemblies
•
Support for x64 only targeted assemblies
•
Support for Any CPU / MSIL
•
Designer is x86/MSIL only
•
Can side by side target assemblies
Install Platform Target Itanium (IA64)
•
Support for IA64
•
Support for Any CPU / MSIL
•
No designer support
64-bit Tools Only
•
No x86 file requirement
•
No x86 DTSPath registry key
•
MSI registry search is 32-bit
•
No key, no tools, no designer, no file required!
Install Tips
•
Use MSI builder of choice, e.g. VS, WiX
•
Registry Search for DTS folder location
HKLM\SOFTWARE\Microsoft\MSDTS\Setup\DTSPath
C:\Program Files\Microsoft SQL Server\90\DTS\
•
Allow tools only installs, don’t require 32-bit key
•
User Interfaces & Support Assemblies?
–
GAC Only
•
Support Files?
–
Use special folders, CommonApplicationData
•
Minimal UI, no options required
When to extend
•
Reuse in multiple packages
–
Increased Development
vs
Lower Maintenance
•
Access legacy resources (COM)
•
Complex business logic
•
More advanced code requirements
•
Powerful IDE
•
Language choice
Resources - Samples
•
Microsoft Download Center
http://www.microsoft.com/downloads/
Search for “
SQL Server SSIS Sample Component
”
•
P
r
o
f
e
s
s
i
o
n
a
l
S
Q
L
S
e
r
v
e
r
2
0
0
5
I
n
t
e
g
r
a
t
i
o
n
S
e
r
v
i
c
e
s
–
W
R
O
X
(
C
h
1
4
–
1
5
–
S
a
m
p
l
e
s
)
http://www.wrox.com/WileyCDA/WroxTitle/productCd
-0764584359.html
•
Microsoft SQL Server 2005 Integration
Services – SAMS (Ch 24 – 25 - Samples)
http://www.samspublishing.com/bookstore/product.a
sp?isbn=0672327813
Thank You!
Darren Green
Konesans Ltd
darren.green@konesans.com
http://www.konesans.com
http://www.sqldts.com
http://www.sqlis.com
Platinum
Gold
Silver
www.sqlbits.com
www.SQLBits.com
[Conference Web site]
www.SQLBlogCasts.com
[Becoming the premier Blogging site for SQL professionals]
www.SQLServerFAQ.com
[UK SQL Server Community Website]
UK SQL Bloggers
cwebbbi.spaces.live.com
sqlblogcasts.com/blogs/simons
sqlblogcasts.com/blogs/tonyrogerson
Feedback Forms!!
Questions – can keep badges? Raffle tickets?
Health and Safety – Fire Alarm; Toilets; Exists; Where you can smoke; Location Memphis and Everest and how to get there.
Feedback Forms : each speaker will give out a colour code that you need to write on your feedback form in order to get the voucher worth £30 for a book from Wiley.
Sponsors – QUEST have a prize draw for an xbox and other goodies, IDERA have a prize draw for 5 ipod nanos - visit their stands for more information.
Sponsor thank you – thank you to sponsors Microsoft, IDERA, Quest, Solid Quality Mentoring, Redgate and Wiley.
Dive into the world of custom tasks in SQL Server Integration Services (SSIS) at the SQLBits event. Learn about different types, debugging methods, installation processes, and more. Explore references, custom objects, and practical demos to enhance your SSIS skills and make the most out of this powerful tool.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
SQLBits Logo http://www.sqlbits.com/images/mslogo_black.jpg Platinum http://www.sqlbits.com/images/idera_logo_tag%20600%20150.jpg Learn & Enjoy [Put your phone on Vibrate!] www.sqlbits.com Group BY: [Food and Drink at Reading Bowl, see you there!] http://www.sqlbits.com/images/quest_logo.gif Gold http://www.sqlbits.com/images/SQM%20Signature.jpg Feedback Forms: [Voucher for 30 book on return of Form] http://www.sqlbits.com/images/redgate_black_lrg.gif Silver Lunch Time Sessions: [Idera in Everest, Quest in Memphis, Grok in Chic 1 and 2] Ask The Experts [Sessions need to finish on time, take questions to the ATE area] Extending SSIS with Custom Tasks
Extending SSIS with Custom Tasks Darren Green Konesans Ltd
Agenda Extending SSIS Types Six Steps Tasks Debugging Installation When and Why?
Types Task Pipeline Component Source Destination Transform Log Provider For Each Enumerator Connection Manager Connection Manager
Six Steps Create project Add references Add signing key Write code Compile Install
References Microsoft.SQLServer.ManagedDTS Microsoft.SqlServer.Dts.Runtime Microsoft.SQLServer.DTSRuntimeWrap Microsoft.SqlServer.Dts.Runtime.Wrapper Microsoft.SqlServer.PipelineHost Microsoft.SqlServer.Dts.Pipeline Microsoft.SqlServer.DTSPipelineWrap Microsoft.SqlServer.Dts.Pipeline.Wrapper Microsoft.SqlServer.Dts.Design Microsoft.SqlServer.Dts.Runtime.Design
Custom Objects - Template [ObjectAttribute(Information)] public class MyCustomObject : BaseClass { public override void BaseMethod() { // Custom code } }
Classes & Attributes Base Class Base Class Attribute Attribute ConnectionManagerBase DtsConnectionAttribute PipelineComponent DtsPipelineComponentAttribute ForEachEnumerator DtsForEachEnumeratorAttribute LogProviderBase DtsLogProviderAttribute Task DtsTaskAttribute
Demo Simple Task
Tasks Methods InitializeTask Validate Execute Properties Use attributes, editors and type converters Use connection GUID Write only for security when required Custom persistence for sensitive data or complex types
More Task Features Custom Events Custom Log Entries Breakpoints (IDTSBreakpointSite)
Create a Task User Interface Create UI class (Implement IDtsTaskUI) Create UI form Set task s UITypeName attribute
Demo Simple Task User Interface
Advanced Task User Interfaces Microsoft.DataTransformationServices.Controls Microsoft.DataTransformationServices.Controls.dll DTSBaseTaskUI Base Form Class DTSTaskUIHost Control, paged control Class per page, implement IDTSTaskUIView
UI Classes & Attributes Interface / Class Interface / Class Notes Notes IDtsConnectionManagerUI IDtsConnectionManagerUI Use Win Form Use Win Form CManagedComponentWrapper IDtsComponentUI ForEachEnumeratorUI UserControl IDtsLogProviderUI Not Supported Use Win Form IDtsTaskUI
Debugging Designer Design time Attach to devenv.exe Designer Runtime Attach to DTSDebugHost.exe Component Project Runtime Start external program C:\Program...\DTExec.exe /F "C:\...TestPackage.dtsx
Debugging Tips Ensure class and strong name is fixed at start Must restart BIDS between builds, slow! Use DTExec for all runtime Use Post Build Event for install copy "$(TargetPath) C:\Program... gacutil.exe" /if "$(TargetPath) Ensure persistence complete
Installation Locations Designer Enumeration Folder Designer location only (x86 Only) C:\Program Files\Microsoft SQL Server\90\DTS\<Object> \Connections \ForEachEnumerators \Tasks \LogProviders \PipelineComponents Global Assembly Cache Runtime loading by execution host
Install Platform Target 32-bit (x86) Location Location x86 x86 x64 x64 IA64 IA64 MSIL MSIL Program Files Design Design GAC Run Run Install in Program Files for Designer Install in GAC for run-time Support for x86 specific targeted assemblies Support for Any CPU / MSIL
Install Platform Target 64-bit (x64) Location Location x86 x86 x64 x64 IA64 IA64 MSIL MSIL Program Files (x86) Design Design GAC Run Run Run Support for x86 only targeted assemblies Support for x64 only targeted assemblies Support for Any CPU / MSIL Designer is x86/MSIL only Can side by side target assemblies
Install Platform Target Itanium (IA64) Location Location x86 x86 x64 x64 IA64 IA64 MSIL MSIL GAC Run Run Support for IA64 Support for Any CPU / MSIL No designer support
64-bit Tools Only Location Location x86 x86 x64 x64 IA64 IA64 MSIL MSIL Program Files (x86) GAC Run Run No x86 file requirement No x86 DTSPath registry key MSI registry search is 32-bit No key, no tools, no designer, no file required!
Install Tips Use MSI builder of choice, e.g. VS, WiX Registry Search for DTS folder location HKLM\SOFTWARE\Microsoft\MSDTS\Setup\DTSPath C:\Program Files\Microsoft SQL Server\90\DTS\ Allow tools only installs, don t require 32-bit key User Interfaces & Support Assemblies? GAC Only Support Files? Use special folders, CommonApplicationData Minimal UI, no options required
When to extend Reuse in multiple packages Increased Development vs Lower Maintenance Access legacy resources (COM) Complex business logic More advanced code requirements Powerful IDE Language choice
Resources - Samples Microsoft Download Center http://www.microsoft.com/downloads/ Search for SQL Server SSIS Sample Component Professional SQL Server 2005 Integration Professional SQL Server 2005 Integration Services Services WROX (Ch 14 WROX (Ch 14 15 http://www.wrox.com/WileyCDA/WroxTitle/productCd -0764584359.html Microsoft SQL Server 2005 Integration Services SAMS (Ch 24 25 - Samples) http://www.samspublishing.com/bookstore/product.a sp?isbn=0672327813 15 Samples) Samples)
Thank You! Darren Green Konesans Ltd darren.green@konesans.com http://www.konesans.com http://www.sqldts.com http://www.sqlis.com
SQLBits Logo http://www.sqlbits.com/images/mslogo_black.jpg Platinum http://www.sqlbits.com/images/idera_logo_tag%20600%20150.jpg www.SQLBits.com [Conference Web site] www.sqlbits.com www.SQLBlogCasts.com [Becoming the premier Blogging site for SQL professionals] http://www.sqlbits.com/images/quest_logo.gif Gold http://www.sqlbits.com/images/SQM%20Signature.jpg www.SQLServerFAQ.com [UK SQL Server Community Website] http://www.sqlbits.com/images/redgate_black_lrg.gif Silver UK SQL Bloggers cwebbbi.spaces.live.com sqlblogcasts.com/blogs/simons sqlblogcasts.com/blogs/tonyrogerson Feedback Forms!!
