none
Export to flat file using T-SQL and import to another machine

    Question

  •  

    Hi,

     

    I need to export some data from SQL 2005 to a flat file, The data and flat file names will be dynamic and will be be fired programaticaly so I can't use DTS or SSIS.

     

    In SQL2000 I did it using bcp, but that's quite a security hole so I don't want to use external utilities. I'll need to do something similar on another machine to import the data as well.

     

    I find it strange there's no easy way to do this!

     

    Thanks.

    Wednesday, January 09, 2008 3:04 PM

Answers

All replies

  • Hi Davvers,

     

    Michael Entin has a blog post that will help you here: http://blogs.msdn.com/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx

    There are a number of ways of executing SSIS packages, you just need to see which works best for your scenario.

     

    Brian

     

    Wednesday, January 09, 2008 3:18 PM
  •  

    Thanks Brian , I'll take a look. The mention of being able to set vriables or modify the package before execution is of particular interest.

     

    Thanks, Davvers.

    Wednesday, January 09, 2008 3:55 PM
  •  Davvers wrote:

     

    Hi,

     

    I need to export some data from SQL 2005 to a flat file, The data and flat file names will be dynamic and will be be fired programaticaly so I can't use DTS or SSIS.

     

    In SQL2000 I did it using bcp, but that's quite a security hole so I don't want to use external utilities. I'll need to do something similar on another machine to import the data as well.

     

    I find it strange there's no easy way to do this!

     

    Thanks.

     

    I believe the entire premise of this problem is flawed. How is BCP either a security hole, or an external utility? BCP is a core tool that has shipped with SQL Server since v 6.0 or earlier. There is no (and should be no) such functionality in the core database engine - that's not what database engines do. So you need to use an existing tool or build your own "external" utility.

     

    SSIS and BCP both fall into the categories of "easy ways" to import and export data - what exactly is the problem you percieve?

    Wednesday, January 09, 2008 3:55 PM
    Moderator