Different ways to migrate DTS to SSIS 2005

SQL SERVER INTEGRATION SERVICES (SSIS)
Today, I will state various ways to migrate DTS packages (.dts files) to SSIS 2005 packages (.dtsx files)

1) Using Package migration wizard exe "DTSMigrationWizard.exe" located in the following path:
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\

A snapshot of the wizard is as follows:


2) Another way to migrate DTS packages is from the SSIS solution.
In the SSIS solution, right click Packages and select "Migrate package" option

3) If you don't want to migrate the DTS packages but want to use them in SSIS 2005, you can execute them from the "Execute DTS package task"

4) Also you can modify/create/execute DTS packages using SQL Server Mgmt Studio.
In SSMS, after connecting to Database engine, select Management->Legacy, right click on "Data Transformation Services" and select the appropriate option.
You must have the DTS Designer component installed to work on the DTS packages.
This can be downloaded from Microsoft site.

Methods 1),2) and 4) would be using Migration wizard to migrate DTS to SSIS 2005 packages.



All achievements, all earned riches, have their beginning in an idea.

Four steps to achievement:
Plan purposefully.
Prepare prayerfully.
Proceed positively.
Pursue persistently.

New features in SQL Server 2008

Microsoft has released SQL Server 2008.. Let's see what's new in it compared to previous version.
http://www.mssqltips.com/tip.asp?tip=1313





All achievements, all earned riches, have their beginning in an idea.

Four steps to achievement:
Plan purposefully.
Prepare prayerfully.
Proceed positively.
Pursue persistently.

All the information you need

Here, on my blog, I will try to post the technical aspects covering Microsoft BI Technologies. Feel free to post your comments and raise your questions.

Script in SSIS script task to download file via HTTP

Public Sub Main()
Dim myWebClient As WebClient
Dim remoteUri As String
Dim localFileName As String
Dim fireAgain As Boolean

Try
myWebClient = New WebClient()

' get the context from variables
'remoteUri = CStr(Dts.Variables("RemoteUri").Value)
'localFileName = CStr(Dts.Variables("LocalFileName").Value)
remoteUri = "http://www.msbicentral.com"
localFileName = "C:\\Multi-ValuedParameters.rdl"
' tell the user what we're downloading where
Dts.Events.FireInformation(0, String.Empty, String.Format("Downloading '{0}' from '{1}'", localFileName, remoteUri), String.Empty, 0, fireAgain)

' do the actual download
myWebClient.DownloadFile(remoteUri, localFileName)

Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
' post the error message we got back.
Dts.Events.FireError(0, String.Empty, ex.Message, String.Empty, 0)
Dts.TaskResult = Dts.Results.Failure
End Try
End Sub

More details can be found on this page:
http://www.eggheadcafe.com/community/aspnet/13/10053833/using-ssis.aspx

Difference between BULK INSERT and SQL Server Destination in SSIS

The Bulk Insert task uses the Transact-SQL BULK INSERT statement for speed when loading large amounts of data. The SQL Server destination uses logic similar to but even more optimized than the logic used by the SQL Server built-in BULK INSERT command. Because the SQL Server destination uses shared memory for maximum loading speed, the package containing the SQL Server destination must execute on the same server as the database engine. Both the Bulk Insert task and the SQL Server destination can specify options that would be specified in the Transact-SQL BULK INSERT statement. Using the SQL Server destination is faster than either the Bulk Insert task or using the OLE DB destination with one of the fast load options. The fast load option of the OLE DB destination works by using optional parameters of SQL Server's BCP (bulk copy) utility; these optional parameters include:

· Keep identity values from the imported data file or use unique values assigned by SQL Server.

· Retain a null value during the bulk load operation.

· Check constraints on the target table or view during the bulk import operation.

· Acquire a table-level lock for the duration of the bulk load operation.

· Specify the number of rows in the batch and the commit size. The number of rows in a batch defaults to 100.