SSIS Text file export

• b_boy

  SSCertifiable

  Points: 5145

  More actions

  September 9, 2008 at 8:29 am

  #126309

  Hello

  Does anyone know how I can export a result set from SQL Server 2005 as a text file and dump the file in a netwrok folder, and have the file named after the day it was dumped (eg 20080909.txt)

• Mukti

  SSCrazy

  Points: 2826

  More actions

  September 9, 2008 at 12:19 pm

  #869232

  Step 1 - Create a flat file conneciton on the network. Use expressions to set the connection string. Use expression to generate file name as required:

  "FileName" + (DT_WSTR, 10) DATEPART( "yyyy", GETDATE()) + RIGHT("00" + (DT_WSTR, 10) DATEPART( "mm", GETDATE()) ,2) + RIGHT("00" + (DT_WSTR, 10) DATEPART( "dd", GETDATE()) ,2) + ".txt"

  You can set this flat file anyway you like - comma seperated, tab seperated or fixed widht etc.

  Step 2 - use Data flow task and use ole db source (to generate data from your table) and the above file as destination.