Archive

Archive for the ‘SQL SSIS’ Category

DTS Package migration steps from SQL 2000 to SQL2008

March 31, 2015 Leave a comment

  1. Run time support installation for DTS packages.
  2. Design time support installation for DTS packages.
  3. Configure SSMS for DTS packages.
  4. Migrate the DTS packages by using SSMS.These are all high level steps which includes below step by step activities.
  • Check the backward compatibilities and integration services are installed on target server or not.Check the integration service is there or not.
  • Run services.msc
  • If the above services are not installed, go to binaries location and run the Setup on SQL2008 instance on target server and go to the feature selection page.
  • Select both features in the installation wizard and proceed with installation.
  • Download the design time components from the below URL and install the same.
  • http://www.microsoft.com/en-us/download/details.aspx?id=11988
  • Copy the files, SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL, from the %ProgramFiles(x86)%\Microsoft SQL Server\80\Tools\Binn folder to the %ProgramFiles(x86)%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE folder.
  • Download and install SQL upgrade advisor to analyze the existing packages for migration issues.
  • Start the migration package wizard from SSMS to do the DTS package migration.                 Upgrade Advisor: http://www.microsoft.com/en-in/download/details.aspx?id=11455
  • Design time Components: http://www.microsoft.com/en-us/download/details.aspx?id=11988
  • Required Software Download location:
Advertisements
Categories: SQL SSIS

DTS Package migration steps from SQL 2000 to SQL2008

March 31, 2015 Leave a comment

  1. Run time support installation for DTS packages.
  2. Design time support installation for DTS packages.
  3. Configure SSMS for DTS packages.
  4. Migrate the DTS packages by using SSMS.These are all high level steps which includes below step by step activities.
  • Check the backward compatibilities and integration services are installed on target server or not.Check the integration service is there or not.
  • Run services.msc
  • If the above services are not installed, go to binaries location and run the Setup on SQL2008 instance on target server and go to the feature selection page.
  • Select both features in the installation wizard and proceed with installation.
  • Download the design time components from the below URL and install the same.
  • http://www.microsoft.com/en-us/download/details.aspx?id=11988
  • Copy the files, SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL, from the %ProgramFiles(x86)%\Microsoft SQL Server\80\Tools\Binn folder to the %ProgramFiles(x86)%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE folder.
  • Download and install SQL upgrade advisor to analyze the existing packages for migration issues.
  • Start the migration package wizard from SSMS to do the DTS package migration.                 Upgrade Advisor: http://www.microsoft.com/en-in/download/details.aspx?id=11455
  • Design time Components: http://www.microsoft.com/en-us/download/details.aspx?id=11988
  • Required Software Download location:
Categories: SQL SSIS

Rollback of SSIS

December 31, 2013 Leave a comment

Issue: I have a SSIS that insert a lot of comments that we received, because our pdn server is offsite, when I am loading the comments to pdn server, my concern is if the internet dropped, I need to roll back the transaction, is there a way to do this? or if there is a better way?

Workaround:

We can put the steps in the package inside of a transaction to roll back the entire transaction if any problem occurred …here is a video on how to do it….

http://www.jumpstarttv.com/Media.aspx?vid=39

To watch the video just need to register on the site and after confirmation you can watch it.

Note about Transaction:

Transactions in SSIS allow you to protect your data and envelope any data change that occurs in the package in that protection. Transactions can be either on the entire package, a container, or an individual task. They can be nested just like in TSQL. What is important to note about transactions is that only data-related tasks are protected. If your package were to archive a file, for example, that file would not be un-archived upon a package failure (unless you explicitly created some kind of compensating action to perform such duties).

Letus take the previous package you created and wrap the package in a transaction. First, lets disable the checkpoint from the previous example by changing the Checkpoint Usage package property to Never and the Save Checkpoints property to False. Next, delete all the records from the RestartabilityExample table.

With the example now reset back to its original state, you’re ready to enable transactions on the package. In the Properties window for the package, change the Transaction Option at the bottom of the window to Required. By changing this from Supported to Required, you have created a transaction that envelopes the package. Any task or container that has this same property set to Supported will join the transaction. By default, each task is set to join the transaction with the Transaction Option of Supported set.

For you to enable this type of transaction, though, you need the Microsoft Distributed Transaction Coordinator (MSDTC) started on each server that you want to participate in the transaction. So, if you have a server that changes data on two servers and runs on a third server, you would need MSDTC running on all three servers. Transactions can then protect data nearly from any database like SQL Server, Oracle, and UDB, since you have externalized the transaction-handling to another service. The caveat with this, though, is the database must be running on a Windows machine, since MSDTC is a Microsoft service.

Before executing a package that has transactions enabled, you must ensure that MSDTC is started. You can start the MSDTC service in the Services applet, or you can type the following command from a command prompt: NET START MSDTC. If you did not have the service started, you would receive the following error when executing the package:

Error: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B “The Transaction Manager is not available.”. The DTC transaction failed to start. This could occur because the MSDTC Service is not running. After having the service started, lets break the package yet again. Select the last task, which is named Insert Record 2, and change the ForceExecutionResult package property to Failure. Execute the package again and you should see the results as shown in Figure 7-11. This time, if you were to view the records in the Adventure Works database, you should see no new rows. This is because the purging of the table occurred, then the first record was inserted, and when the final task failed, both it and the first two operations (within the same transaction) were rolled back.

Categories: SQL SSIS

Migrating DTS to SQL Server Integration Services

Categories: SQL SSIS