|
How to migrate Scripting.Filesystem objects
used inside ActiveX script to SSIS equivalent task/code? |
Generally developers use Scripting.FileSystem object in ActiveX script task to perform various file system
related tasks (e.g. Copy file, Delete file etc.). In SSIS you can perform many file system related common tasks using File System task.
You should find out the possibility of replacing ActiveX script using SSIS File System Task. If any task you performing
using Scripting.FileSystem is not possible using File System Task (i.e. Check File Exists) then you can use Script Task
in SSIS and use System.IO namespace to perform File/Folder related tasks not possible using FileSystem Task.
You can perform any of the following operations using File System Task
- Copy directory
- Copy file
- Create directory
- Delete directory
- Delete directory content
- Delete file
- Move directory
- Move file
- Rename file
- Set Attributes (i.e. Set file to Hidden, ReadOnly, Archive or System)
If your ActiveX script is using anything other than above listed operations then you might have to use SSIS Script Task and write code
using System.IO methods. The following code snippets will show how to perform some of most common file/folder related tasks not
possible to implement using File System Task.
| Path related functions |
Imports System.IO
Public Class ScriptMain
Public Sub Main()
Dim sMyVar As String
'//Get only file name from a specified path => Returns mydatafile_001.txt
sMyVar = System.IO.Path.GetFileName("c:\temp\mydatafile_001.txt")
'//Get only directory path from a specified path => Returns c:\temp
sMyVar = System.IO.Path.GetDirectoryName("c:\temp\mydatafile_001.txt")
'//Combine two paths into one path => Returns c:\temp\mydatafile_001.txt
sMyVar = System.IO.Path.Combine("c:\temp", "mydatafile_001.txt")
'//Get filename without extension => Returns mydatafile_001
sMyVar = System.IO.Path.GetFileNameWithoutExtension("c:\temp\mydatafile_001.txt")
'//Get extension of the file => Returns txt
sMyVar = System.IO.Path.GetExtension("c:\temp\mydatafile_001.txt")
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
|
| Check if file/folder exists |
Imports System.IO
Public Class ScriptMain
Public Sub Main()
Dim sMyVar As String
If File.Exists("c:\temp\file_001.txt") = True Then
'//Debug.Print "File Exists"
End If
If Directory.Exists("c:\temp") = True Then
'//Debug.Print "Folder Exists"
End If
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
|
| Read from text file |
Imports System.IO
Public Class ScriptMain
Public Sub Main()
'//Read file content to string variable
Dim sMyfileData As String
Dim sReader As StreamReader = New StreamReader("c:\temp\file_001.txt")
sMyfileData = sReader.ReadToEnd
sReader.Close()
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
|
| Read from text file (line by line) |
Imports System.IO
Public Class ScriptMain
Public Sub Main()
'//Read file line by line
Dim sReader As New StreamReader("c:\autoexec.bat")
' Display all the text lines in the file.
Do Until sReader.Peek = -1
' The ReadLine methods reads whole lines.
Console.WriteLine(sReader.ReadLine)
Loop
' Always close a StreamReader when you've done with it.
sReader.Close()
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
|
| Write to text file |
Imports System.IO
Public Class ScriptMain
Public Sub Main()
'//Open existing file for append. If file doesn't exist then new file will be created
Dim writer As StreamWriter = New StreamWriter("c:\write_test.txt", True)
writer.WriteLine("Hello world - line1")
writer.WriteLine("Hello world - line2")
writer.Close()
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
|
| Get file information |
Imports System.IO
Public Class ScriptMain
Public Sub Main()
'//Get file properties
Dim sInfo As String
Dim FileProps As FileInfo = New FileInfo("c:\windows\notepad.exe")
sInfo = sInfo & " File Name = " & FileProps.FullName
sInfo = sInfo & " Creation Time = " & FileProps.CreationTime
sInfo = sInfo & " Last Access Time = " & FileProps.LastAccessTime
sInfo = sInfo & " Last Write Time = " & FileProps.LastWriteTime
sInfo = sInfo & " Size = " & FileProps.Length
System.Diagnostics.Debug.Write(sInfo)
FileProps = Nothing
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
|
| List files (with wild card search pattern and recursive option) |
Imports System.IO
Public Class ScriptMain
Public Sub Main()
Dim file As String
'//Recursive listing (search for *.txt)
'//Dim files() As String = Directory.GetFiles("c:\windows", "*.txt", SearchOption.AllDirectories)
'//Top level listing (search for *.txt)
Dim files() As String = Directory.GetFiles("c:\windows", "*.txt", SearchOption.TopDirectoryOnly)
For Each file In files
System.Diagnostics.Debug.WriteLine(file & "...found")
Next
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
|
| List sub directories |
Imports System.IO
Public Class ScriptMain
Public Sub Main()
Dim dir As String
'Dim dirlist() As String = Directory.GetDirectories("c:\windows","*",SearchOption.AllDirectories)
Dim dirlist() As String = Directory.GetDirectories("c:\windows")
For Each dir In dirlist
System.Diagnostics.Debug.WriteLine(dir)
Next
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
|
| List disk drives |
Imports System.IO
Public Class ScriptMain
Public Sub Main()
Dim dirInfo As Directory
Dim drive As String
Dim drives() As String = dirInfo.GetLogicalDrives()
For Each drive In drives
System.Diagnostics.Debug.WriteLine(drive)
Next
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
|
| File/Folder delete, copy, move |
Imports System.IO
Public Class ScriptMain
Public Sub Main()
If File.Exists("c:\dest\datafile.txt") Then
File.Delete("c:\dest\datafile.txt")
End If
File.Copy("c:\src\datafile.txt", "c:\dest\datafile.txt")
File.Move("c:\src\datafile.txt", "c:\dest\datafile.txt")
If Directory.Exists("c:\dest") Then
Directory.Delete("c:\dest")
End If
Directory.CreateDirectory("c:\dest")
Directory.Delete("c:\dest")
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
|
|
|
How to migrate ADODB.Connection and
ADODB.Recordset objects used inside ActiveX script to SSIS equivalent task/code? |
|
Using ADO objects inside ActiveX Script task is very common in DTS. People use ADO objects (i.e. ADODB.Connection, ADODB.RecordSet etc)
for many reasons but the most common use are
- Creating connections at runtime
- Executing Adhoc Queries at runtime
- Processing certain data row by row
Check the following example on how to convert ActiveX Script. It performs couple of things
- Establish Connection to SQL Server using ADODB.Connection object
- Execute SQL Query and Load the recordset using ADODB.Recordset object
- Loop through each record to build HTML formatted string and email it.
| Sample ActiveX Script using ADODB.Connection and ADODB.Recordset |
'**********************************************************************
' Visual Basic ActiveX Script
'**********************************************************************
Function Main()
Dim objRs
Dim objConn
Dim strSql
Dim strHtml
Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Provider=SQLOLEDB;Data Source=(local);Trusted_Connection=yes;Initial Catalog=Northwind"
strSql = "Select top 10 b.ProductName,Sum(a.UnitPrice*a.Quantity) as Total from [Order Details] a "
strSql = strSql & "join Products b on a.ProductID =b.ProductID "
strSql = strSql & "group by b.ProductName order by Sum(a.UnitPrice*a.Quantity) desc"
'--- //Method 1 : Using Execute Method of Connection Object
'--- Set objRs=objConn.Execute(strSql)
'--- //
'*** /// OR ///****
'//Method 2 : Using Execute Method of Connection Object
Set objRs = CreateObject("ADODB.Recordset")
objRs.Open strSql, objConn
'//
strHtml = "<h2>Top 10 Products<h2><table>"
strHtml = strHtml & "<tr><td>Product Name</td><td>Total</td></tr>"
Do While Not objRs.EOF
strHtml = strHtml & "<tr><td>" & objRs(0) & "</td><td>" & objRs(1) & "</td></tr>"
objRs.MoveNext
Loop
strHtml = strHtml & "</table>"
'MsgBox strHtml
SendMail "dataservices@mycompany.com", "ceo@mycompany.com", "Top 10 Products", strHtml, True
Main = DTSTaskExecResult_Success
End Function
Sub SendMail(FromAddress, ToAddress, Subject, Body, IsHtml)
'//Write Code to send email
End Sub
|
Above script is DTS way to perform some common operations but when you migrate to SSIS you can take different approach without writing
too much code. The best practice is whenever possible try to avoid hard coded connection string inside your script.
The following table enlists how to migrate various patterns you generally encounter in the DTS world.
| DTS |
SSIS |
| ADO Connection |
1. Replace with Connection Manager
|
| ADO Command (no result set returned ... i.e. INSERT/UPDATE/DELETE) |
1. Add Execute SQL Task. Set Connection, Set SQL Statement
2. Set resultset property to "None"
3. Set Parameter mapping if required
|
| ADO Recordset (which returns resultset) |
1. Add SSIS variable of Object data type to hold Resultset
2. Add Execute SQL Task. Set Connection, Set SQL Statement
3. Set resultset property to "Full Resultset"
4. Set resultset mapping to variable of Object data type to hold Recordset. Make sure to rename Resultset Name="0"
|
| ADO Recordset Looping |
1. Add Foreach Loop Container
2.
2. Use Foreach ADO Enumerator and specify variable which holds resultset (SSIS variable of Object Datatype)
3. In the Variable Mappings add recordset column index to SSIS variable map. Index starts from 0.
|
Lets walk through Step-By-Step how to convert above sample DTS ActiveX script to SSIS equivalent control flow.
Fig-1 (Converted SSIS Package)
Sample Package Installation
Perform the following steps to setup and test the above sample SSIS Package
1. Extract the sample zip file
2. Open FAQTest.sln and analyze or run the Sample Package to test
Step-By-Step
Define Variables : The sample package uses four variables with the following settings.
"Is Expression" column refers to the EvaluateAsExpression property of SSIS variable.
| Variable Name |
Data Type |
Value |
Is Expression |
Expression |
| strHTML |
String |
|
False |
|
| objRs |
Object |
|
False |
|
| varProdName |
Object |
|
False |
|
| varTotal |
Object |
|
False |
|
|
Define Connections : The sample package requires one connection.
- Create an oledb connection ((local).Northwind) to use Northwind database.
|
Execute SQL Task (Get Recordset) : Next step is to create an execute sql task to execute
the following SQL Statement also make sure you select Northwind connection for this task.
| SQL |
Select top 10 b.ProductName,Sum(a.UnitPrice*a.Quantity) as TotalSales
from [Order Details] a
join Products b on a.ProductID =b.ProductID
group by b.ProductName order by Sum(a.UnitPrice*a.Quantity) desc
|
|
| Foreach Loop Container (Loop Recordset) : Place Foreach Loop Container and double
click to open properties dialog box. Apply the following settings displayed in the screenshots. |
|
Fig-2 (Select Enumerator, ADO object Source Variable and Enum mode)
Fig-3 (Select Variable Mappings)
|
Script Task (Update HTML String) : Place Script Task inside the Foreach Loop Container.
- Double click the Script Task
- Specify ReadOnlyVariables as displayed in the following screenshot
- Specify ReadWriteVariables as displayed in the following screenshot.
- Click on the Design Script Button
- Enter the script displayed in the Fig-5 |
|
Fig-4 (ReadOnly and ReadWrite variables)
Fig-5 (Script for Script Task)
| Script |
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim ProdName As Object
Dim Total As Object
Dim HTML As Object
ProdName = Dts.Variables("varProdName").Value
Total = Dts.Variables("varTotal").Value
HTML = Dts.Variables("strHTML").Value
HTML = HTML.ToString & "<tr><td>" & ProdName.ToString & "</td><td>" & Total.ToString & "</td></tr>"
Dts.Variables("strHTML").Value = HTML
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
|
|
Script Task (Send HTML Email) : Last step is to place Script Task to execute SendHTMLEmail
routine.
- Place a Script Task
- Double click the Script Task
- Specify strHTML in the ReadOnlyVariables
- Click on the Design Script Button
- Enter the script displayed in the Fig-6 |
|
Fig-6 (Send HTML Email)
| Script |
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim HTML As String
Dim Header, Footer As String
Header = "<h2>Top 10 Products<h2><table><tr><td>Product Name</td><td>Total</td></tr>"
Footer = "</table>"
HTML = Dts.Variables("strHTML").Value.ToString
HTML = Header & HTML & Footer
SendMail("dataservices@mycompany.com", "ceo@mycompany.com", "Top 10 Products", HTML, True)
Dts.TaskResult = Dts.Results.Success
End Sub
Sub SendMail(ByVal FromAddress As String, _
ByVal ToAddress As String, _
ByVal Subject As String, _
ByVal Body As String, _
ByVal IsHtml As Boolean)
MsgBox(Body)
'//Write Code to send email
End Sub
End Class
|
|
| Connect all tasks and testing : Connect all tasks as shown in the Fig-1 and execute
the package to test. |
| |
|
|
How to send HTML email in SSIS?
|
SSIS Improved Send Mail Task by adding support of SMTP but still it lakes most common email requirement which is send
email in HTML format. Send Mail Task in SSIS only support text format so again programmers have to use script task and write some VB.net
code to send email in HTML format. You can use the following reusable routine to send HTML formatted email.
| Send HTML email |
'// Example :
'// SendMail("someone@mycompany.com, _
'// "support@dtsxchange.com", _
'// "HTML Test Email!!!", _
'// "<B>Hello</B> How are you?", _
'// True)
Private Sub SendMail( _
ByVal SendTo As String, _
ByVal From As String, _
ByVal Subject As String, _
ByVal Body As String, _
Optional ByVal IsBodyHtml As Boolean = True, _
Optional ByVal SMTPServer As String = "localhost", _
Optional ByVal UserName As String = "", _
Optional ByVal Password As String = "", _
Optional ByVal Domain As String = "", _
Optional ByVal Attachments As String = "")
Dim oMessage As System.Net.Mail.MailMessage
Dim mySmtpClient As System.Net.Mail.SmtpClient
oMessage = New System.Net.Mail.MailMessage(From, SendTo, Subject, Body)
oMessage.IsBodyHtml = IsBodyHtml
'//Attachments
If Not String.IsNullOrEmpty(Attachments) Then
Dim sFiles() As String
Dim sFile As String
sFiles = Split(Attachments, ";")
For Each sFile In sFiles
If Not String.IsNullOrEmpty(sFile) Then
oMessage.Attachments.Add(New Net.Mail.Attachment(sFile))
End If
Next
End If
mySmtpClient = New System.Net.Mail.SmtpClient(SMTPServer, 25)
If UserName = "" Then
mySmtpClient.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials
Else
mySmtpClient.Credentials = New System.Net.NetworkCredential(UserName, Password, Domain)
End If
mySmtpClient.Send(oMessage)
End Sub
|
|
|
How to loop through files
in a specified folder, load one by one and move to archive folder using SSIS? |
In DTS if you want to process files in a specified folder then you have to do good amount of work. But If you
want to implement same in SSIS then you don't have to do much work. You can use "Foreach Loop Container" in SSIS to loop through
files in a specified folder.
The following sample SSIS Package shows how to process each file (Nightly_*.txt) in "C:\SSIS\NightlyData". After each file is processed
its moved to Archive folder.
Sample Package Installation
Perform the following steps to setup and test the above sample SSIS Package
1. Extract the sample zip file
2. Create a folder C:\SSIS
3. Create a Folder C:\SSIS\NightlyData
4. Create a Folder C:\SSIS\NightlyData\Archived
5. Copy Provided sample files to C:\SSIS\NightlyData
6. Open FAQTest.sln and analyze or run the Sample Package to test
Step-By-Step
Define Variables : The sample package uses five variables with the following settings.
"Is Expression" column refers to the EvaluateAsExpression property of SSIS variable.
| Variable Name |
Data Type |
Value |
Is Expression |
Expression |
| varSourceFolder |
String |
C:\SSIS\NightlyData |
False |
|
| varArchiveFolder |
String |
|
True |
@[User::varSourceFolder] + "\\Archived" |
| varArchivePath |
String |
|
True |
@[User::varArchiveFolder] + "\\" + @[User::varFileName] |
| varFileName |
String |
|
False |
|
| varFilePath |
String |
|
True |
@[User::varSourceFolder] + "\\" + @[User::varFileName] |
|
Define Connections : The sample package requires two connections.
- Create an oledb connection ((local).tempdb) to use tempdb database.
- Create a Flat file connection (NightlyDataFile) for any available file in the SourceFolder (in this example use C:\SSIS\NightlyData\Nightly_01.txt).
We have to make FlatFile connection Dynamic so we can use one connection to load several files from the source folder. In
order to make FlatFile connection dynamic we have to configure ConnectionString Property as an Expression (See below
screenshot). varFilePath will be updated on each iteration of file in Foreach Loop Container (Check the Foreach Loop Container
description below in this article).
|
|
|
Execute SQL Task (Create or Truncate Table) : Next step is to create an execute sql
task to execute the following SQL Statement also make sure you select tempdb connection for this task.
| SQL |
if object_id('tempdb.dbo.Staging_Customers') is null
begin
create table tempdb.dbo.Staging_Customers
(
FilePath varchar(255)
,CustName varchar(255)
,CustPhone varchar(255)
,CustEmail varchar(255)
)
end
truncate table tempdb.dbo.Staging_Customers
|
|
| Foreach Loop Container (Load Nighly Data Files) : Place Foreach Loop Container and
double click to open properties dialog box. Apply the following settings displayed in the screenshots. |
|
Fig-1 (Select Enumerator, Folder, Files and Retrieve filename)
Fig-2 (Select Variable Mappings)
|
Data flow (Load Data File) : Place Data flow inside the Foreach Loop Container.
- Double click the dataflow
- Place FlatFile Source
- Place OLEDB Destination.
- Connect FlatFile Source and OLEDB Destination
- Double click on the OLEDB Destination to Select tempdb connection. Then click on mapping and configu |
|
Source->Destination
Mappings
|
| File System Task (Move File to Archive) : Last step is to place File System Task to
move processed file to archive folder. Please specify the properties displayed as below. |
|
|
| Connect all tasks and testing : Connect all tasks as shown in the very first screen
and run the package. If everything is configured correctly then files should load into staging table and then moved to Archived
folder. |
| |
|
|
Is it possible to migrate Analysis
Services Task from DTS to SSIS? |
No. In DTS 2000 (SQL Server Enterprise Edition) Microsoft provided OLAP Task to process Cube stored on Analysis
Services 2000. Unfortunately there is no support for Analysis Services 2000 Cube Processing in SSIS, however Microsoft do provide
support for Analysis Services 2005 Cube Processing using "Analysis Services Processing Task".
If you really want to migrate your AS2000 Cube Processing task to SSIS then you can go with embedded DTS package option which can
be executed using Execute DTS 2000 Package Task. DTS xChange will Migrate all your OLAP Task to Execute DTS 2000 Package
Task (Embedded DTS Package). |
|
How to make my SSIS connections
dynamic so I can easily switch to different environment (e.g. Dev, QA, Prod ...)? |
In DTS if you want to make your connection attributes dynamic (i.e. Server, UserName, Password etc.) then you
can use UDL files or Dynamic Properties Task but none of them were effective and they had their own drawbacks.
In SSIS Microsoft came up with a concept called "SSIS Configurations". You can read configuration settings from any of the following source.
Read from XML File
Read from SQL Server Table
Read from Registry Key
Read from Environment Variable
Read from Parent
The most common way of reading configurations is "XML Config File" (see below screenshot).
If you use DTS xChange then configuration files for selected Connection Types will be automatically created during migration. DTS
xChange is smart enough to detect all your common connections based on connection attributes (i.e. server, username, password etc.) and
generate one config file which can be referenced in many packages. You can easily modify Config file and all your packages using that
Config file will start pointing to new server/database.
|
|
How to read/write variable
value from child/parent package in SSIS? |
Changing value of child package variable was easily possible in DTS using "Execute DTS Package Task"
settings called inner/outer variables. But unfortunately this is not directly supported in SSIS if you using "Execute SSIS Package
Task".
However you can use VB.net code inside script task to read/write child package variable. There is another way to read parent package variable
in SSIS is "Configurations". The following two sections illustrate both techniques.
Read parent package variable from child package (Using Parent Variable Configuration)
The Parent Variable Configuration feature of SSIS allows variables from a parent package to be inherited by a child package. This is different
than the SSIS’ predecessor, DTS, where variables were pushed down from the parent package to the child package. In SSIS the child package
requests the variable by name from the parent package, allowing the variable to be inherited from any calling parent package that uses
the Execute Package task to call the child package.
In the following screen value of parent variable varBatchID_Parent is being assigned to child package variable gvarBatchID_Child.
For more info please visit
http://technet.microsoft.com/en-us/library/ms345179.aspx
You can select any available property as configuration target property including variables, connections, task or package property. In
the following example target is value property of variable called gvarBatchID_Child
Read/write child package variable from parent package (Using Script Task)
Many times you have to pass variables or Static Values at run time to child package and due to complexity of logic you might want to use
scripting instead of "Configuration" technique explained in the previous section. The following piece of code displays how you can load
any SSIS package at run time, set package variables and execute the package.
| Load Package, Set Variable and Execute Package at runtime |
Option Strict Off
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim oApp As Microsoft.SqlServer.Dts.Runtime.Application = New _
Microsoft.SqlServer.Dts.Runtime.Application()
Dim oPkg As Microsoft.SqlServer.Dts.Runtime.Package
'//Load Child Package from SQL Server
oPkg = oApp.LoadFromSqlServer("\DW_Packages\Task_ExecSQL", "(local)", "", "", Nothing)
Dim vars As Variables
'//If Child Package Variable Doesn't exist at runtime
'//then create a new variable and pass the value from Parent package variable.
If oPkg.Variables.Contains("gvChild1") Then
oPkg.VariableDispenser.LockOneForWrite("gvChild1", vars)
Try
vars("gvChild1").Value = "Hello!!! Set From Parent..."
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Else
oPkg.Variables.Add("gvChild1", False, "", "Hello!!! Set From Parent...")
End If
oPkg.Execute() '//Execute the Child Package
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
|
|
|
What are the
main differences in SQL Server 2005 SSIS and SQL Server 2008 SSIS? |
Here is the list of couple of major changes in SSIS 2008.
New Script Environment:
Script Task now supports VB.net and C# both languages. In the previous version of SSIS only VB.net support was there.
Enhanced SQL Statements:
Transact-SQL supports the use of a MERGE operation in an SQL statement. The MERGE operation enables you to express multiple INSERT, UPDATE,
and DELETE operations in a single statement against a specified target table.
Change Data Capture (CDC):
Integration Services includes a new technology called change data capture. This new Database Engine feature captures insert, update, and
delete activity that is applied to SQL Server tables. Change data capture also makes the details of these changes available in an easily
consumed relational format.
DataFlow task Improvements:
New DataFlow engine has smarter thread allocation and processing of our execution trees. Long chains of synchronous transforms are optimized,
and most packages will see a performance increase upto 80% on multi-processor machines.
New Data Profiling Task and Data Profile Viewer:
The Data Profiling task is a new task in the Integration Services toolbox. You can use this task inside an Integration Services package
to profile data that is stored in SQL Server. The information provided by the profile helps you identify potential problems with data
quality. The Data Profiling task provides profiles that help identify data quality problems within individual columns and with column
relationships:
Profiles that help identify problems within individual columns
- The distribution of lengths in the column values.
- The percentage of null values.
- The distribution of values in the column.
- Column statistics for numeric columns.
- Regular expressions that match string columns.
Profiles that help identify problems with column relationships
- Candidate key columns.
- Functional dependencies between columns.
- The inclusion of the set of values in one column in the set of values in another column.
Enhanced Performance and Caching for the Lookup Transformation:
Performance enhancements to the Lookup transformation include faster cache loading and more efficient lookup operations. These enhancements
are possible because of the following features:
- The ability to take rows that do not have matching entries in the reference dataset and load those rows into the cache.
- The ability to use separate data flows to load the reference dataset into the cache and to perform lookups on the reference dataset.
New ADO.NET Components:
Integration Services now includes the following ADO.NET components:
- An ADO.net source component that consumes data from a .net Framework provider and makes the data available to the data flow.
- An ADO.net destination component that loads data into a variety of ADO.net-compliant databases that use a database table or view.
New Date and Time Data Types:
The following new date and time data types are available in Integration Services:
- DT_DBTIME2
- DT_DBTIMESTAMP22
- DT_DBTIMESTAMPOFFSET
These new Integration Services data types provide the following benefits:
- Support for a larger scale for fractional seconds.
- Support of user-defined precision.
- Support for a time zone offset.
New Debug Dump Files:
You can create debug dump files (.mdmp and .tmp) that provide information about what happens when a package runs. This information can
help you in troubleshooting issues that occur when you run the package.
For more information visit the following URLs
Source: simple-talk.com - New feature in
SSIS 2008
Source: technet.microsoft.com - What's New (Integration Services 2008)
Source: blogs.msdn.com/mattm - What's new in SQL Server 2008 for SSIS - Part one
Source: blogs.msdn.com/mattm - What's new in SQL Server 2008 for SSIS - Part two
|
|
Why should I migrate DTS packages
to SSIS (advantages of SSIS over DTS)? |
We get this question almost every day so we ended up creating new FAQ for that
"We can run DTS Packages in SQL Server 2005/2008 without any problem so what's the need to upgrade them to SSIS?"
Well there are many good reasons why you should migrate DTS Packages to SSIS Packages very soon.
- Microsoft is going to discontinue support for SQL Server 2000 and DTS sooner or later so better you be prepared for migration
now.
- SSIS has new many built in tasks which can save you significant amount of time compare to manual coding in DTS (e.g.
Looping through files in a folder is inbuilt in SSIS - See Foreach Loop Container)
- Making package dynamic at runtime is easier using expression and configurations
- SSIS DataFlow engine is significant faster than DTS DataPump
- In memory transformations in DataFlow (e.g. JOIN, Split, Sort, Aggrigate, Union)
- DataViewer support to debug DataFlow
- Event Handlers support with separate control flow for each event
- ScriptTask in SSIS uses VB.net language with rich .net framework support. Script Task code is compiled so run much faster
than DTS ActiveX script.
- Expression based control flow along with Success, Failure
and Complete
- Reusable connection architecture
and ... many more....
For more information please download the following white paper
WhatsNewinSSIS.doc
|
|
What are the
advantages/disadvantages of storing SSIS packages to MSDB vs File System? |
This is another common question we face many times
"Should I store my SSIS Packages to File System or on SQL Server?"
There are two places you can store your packages: on the file system or in the msdb database. Each storage option has its
own pros and cons and which option you choose will be based on what is more important to you. We’ll cover these pros and cons in much
depth in this section but to summarize, the following table gives you a high-level idea of which storage option is best based on the what
functionality you’re most interested in. Just because a given storage option is not checked, does not mean it doesn’t have that functionality.
The ones checked just are most optimized for the given functionality.
| Functionality |
Best in File System |
Best in MSDB |
| Security |
|
X |
| Backup and Recovery |
X |
|
| Deployment |
X |
|
| Troubleshooting |
X |
|
| Execution Speed |
X |
X |
| Availability |
X |
|
If security concerns you greatly, you may want to consider placing your packages in the msdb database. To secure your packages on the
file system, you could have multiple layers of security by using the Windows Active Directory security on the folder on the file system
where the packages are at. You could also then place a password on the packages to keep users that may have administrator rights to your
machine from executing the package. This does add extra complexity to your package deployments in some cases. If you store your packages
in the msdb database, you can assign package roles to each package to designate who can see or execute the package. The packages can also
be encrypted in the msdb database, which strengthens your security even more.
Backup and recovery is simpler with storing your packages in the msdb database. If you were to store your packages in the msdb database,
then you must only wrap the msdb database into your regular maintenance plan to backup all the packages. As packages are added, they are
wrapped into the maintenance plan. The problem with this is that you can restore a single package using this mechanism. You’d have to
restore all the packages to a point in time, and that would also restore the jobs and history. The other option is a file system backup,
which would just use your favorite backup software to backup the folders and files. If you did this, you must rely on your Backup Operator
to do this for you, which makes some uneasy. You could though at that point, restore individual packages to a point in time. In reality,
you may just go ahead and redeploy the packages from Source Safe if you couldn’t retrieve a backup file.
File system deployments are much simpler but less sophisticated. To deploy packages onto the file system, you must only copy them into
the directory for the package store. You can create subdirectories under the parent directory to subdivide it easily. You can also copy
a single package over easily as well in case you need to make a package change. To import a package into the package store using the msdb
database, you must use Management Studio (or a command-line tool called dtutil.exe) and import them package by package. To do a
bulk migration, you could use the deployment utility.
Along the same lines as deployment is troubleshooting. If something were to go bump in the night and you wanted to see if the packages
in production were the same release as the packages you thought you had deployed, you must only copy the files down to your machine and
perform a comparison using Source Safe or another similar tool. If the files were stored in the msdb database, you would have to right-click
on each package in Management Studio and select Export. If the same packages were stored in the file system, you must only copy the files
to your machine.
Availability of your packages is always on the top of the list for DBAs. If you were to store the packages in the msdb database and the
database engine were to go down, the packages are unavailable. If they were stored in the file system, then your packages would be available
for execution. Of course, if the database engine is down, then probably one of your key data sources would also be down at the time.
The good news is no matter what storage option you choose the performance will be the same. As you can see there are many pros and cons
to each storage option and neither overwhelmingly wins. The main reason that we choose to use the file system generally is for simplicity
of deployment.
|
|
How can DTS xChange
save time and money? |
DTS xChnage is developed by a team of experienced developers/DBAs who have great experience with DTS
to SSIS migration and they have migrated thousands of packages. So they understand the pain of migration process and they have
already faced all challenges which you going to face or you already facing during DTS migration. We packaged all necessary
functionality for DTS migration in a very easy to use and solid framework... which is DTS xChange.
DTS xChange uses proprietary rule based engine to migrate DTS Packages to SSIS by applying best practices of SSIS. So by
using DTS xChange you not only get higher migration success rate but you also get new features supported by SSIS (e.g. Event Handlers,
Logging, XML Configuration Files etc.)
If you planning for manual migration without using any tool then you must consider the following facts about DTS xChange which
can save you significant amount time and money.
Top 10 features of DTS xChange
- Automatic Variable Migration
- Automatic Connection Migration
- Automatic Task Migration
- Automatic migration of child packages
- Improved support for Flat Files. Mean you will have to spend less time to troubleshoot mapping/datatype issues of flat files.
- Migrates Dynamic Properties Task.
- Migrates UDL connections.
- Supports Event Handler Logging. This feature alone saves you significant amount of time if you planning to add auditing for package/task
execution.
- Consolidates connections so you don't end up with several connections pointing to the same datasource.
- Makes your connection dynamic so you can easily switch packages from to Dev to Prod environment without modifying every package
and connection.
Visit the following URL to see full list of feature matrix.
DTS xChange Features :
http://dtsxchange.com/DTSxChange-vs-MSWizard.asp
|
|
How to migrate
parameterized DTS packages to SSIS (i.e. Parameters passed using command line from SQL Server Job)? |
Many times we get this question
"How do we handle parameterized packages using DTS xChange?"
Parameterized package means package executed from command line with parameters passed to it. Most common parameters are global variable
initial value. Since this is an issue related to command line it has nothing to do with Package migration. When you migrate your SQL Server
2000 Jobs to SQL Server 2005 you might want to review command line changes.
| Parameterized DTS Package - Passing parameters to DTS package from command line |
DTSRun /S "(local)" /N "PkgTest" /A "varFile":"8"="File_001.xls" /E
Note: "8" is DataType ID
|
| Parameterized SSIS Package - Passing parameters to SSIS package from command line |
DTSExec /SQL "\PkgTest" /SERVER "(local)" /SET "\Package.Variables[User::varFile].Value";"File_001.xls"
|
|
|
Which data providers
are supported in SSIS? |
Data-Connectivity Providers and SSIS
Note: The following is not a definitive list of all data providers, and not all data providers on this list have been tested
by Microsoft with SSIS. Only current Beta or shipping products are listed. Information about third-party products was provided by the
product vendors and could not be independently verified.
Soure : Microsoft.com [http://technet.microsoft.com/en-us/library/bb332055.aspx]
Last Updated : 4/1/2008
|
|
Click here to Download sample package