Post reply

Reverting To BCP for DTS package instead of migrating to 2008?

  • August 1, 2013 at 9:44 am

    #280741

    Would anyone out there convert old DTS packges on SQL2000 to BCP instead of migrating them to 2008R2 SSIS packages? My view is to go with SSIS i have colleagues that want to go to BCP wanted some outside opinions.

  • August 2, 2013 at 1:44 am

    #1638079

    Hi,

    It is always better option to go with SSIS because you can add many functionalities in near future instead of BCP.

    But it also depends on your plan of action.

    Thanks,

  • August 2, 2013 at 6:05 am

    #1638126

    if all the DTS package does is load data from a flat file personally I would rewrite in BCP as it will likely load faster and be simpler, and fits my skill set better (though a load job could be created using the import wizard, even by me)

    If there are complex data transformations involved or your shop is more comfortable with SSIS and has good skills in that area then go to SSIS, there is no black and white answer to this.

    ---------------------------------------------------------------------

  • August 2, 2013 at 7:38 am

    #1638170

    If you are familiar with DTS designer and the new look and feel of Visual Studio 2008 or 2010 then highly recommend using SSIS. Also you can use the import Data Wizard to create some very simple SSIS packages for you.

    Unlike BCP you can easily and quickly manipulate the how you import the data and even adjust for common flat file issues seen in many cross platform files.

    My biggest love for SSIS is that unlike the old DTS tool, SSIS is written in XML format and uses .NET instead of VB6 for the execution language.

    I sure you already know enough about VB6 execution on newer server hardware to understand the advantages of this.

    Final thought. There is even a Bulk Insert task that acts like BCP for SSIS packages to do bulk loading directly to tables. http://msdn.microsoft.com/en-us/library/ms141239.aspx

    You can use the other tools in SSIS to add advanced error handling, alerts, notifications, etc....

  • August 2, 2013 at 8:28 am

    #1638199

    Thank you for you response

  • August 2, 2013 at 8:28 am

    #1638200

    Thanks for taking the time to respond it is appreciated

  • August 2, 2013 at 9:21 am

    #1638226

    rwalker03 (8/2/2013)

    Thanks for taking the time to respond it is appreciated

    You are welcome. As someone that suffered with other peoples DTS and loved BCP and scripts for SQL/shell commands, I actually think SSIS is awesome. It is not Powerflow or Powercenter but it also comes free with SQL server and is not a $$$ dollar product.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply