How to export data from a table into a text file in SSIS?

• ruchi-761283

  SSC-Addicted

  Points: 434

  More actions

  June 2, 2008 at 1:03 am

  #154402

  Hi,

  I am trying to export data from a table in a text file:

  using these controls:

  1)Data flow task

  2)FTP Task

  Inside Data flow task, it does not allow me to define column mappings in lookup.

  Please help

  Thanks in advance.

• Gogula G. Aryalingam

  SSCrazy

  Points: 2688

  More actions

  June 2, 2008 at 1:14 am

  #822786

  Hi,

  Is it possible to be a little more specific regarding the Lookup part? Are you looking up a table? And what sort of problem are you getting?

  Regards,

  Gogula

• ruchi-761283

  SSC-Addicted

  Points: 434

  More actions

  June 2, 2008 at 1:23 am

  #822788

  Hi,

  Data flow task I have successfully added, but now getting error on FTP task.

  While executing package it's giving error: Flat file connection manager is not a file."

  In FileTransfer :Flat file connection manager is given as al ocal path.Do I need to provide something in Remote path also?

• pduplessis-723389

  SSChampion

  Points: 10599

  More actions

  June 2, 2008 at 3:06 am

  #822807

  I am assuming that you want to upload to a FTP destination from a local path.

  So you would require the remote FTP path in your FTP task, as well as the local file.

  Trust this helps

  ~PD

• Sara_DBA-629440

  Right there with Babe

  Points: 721

  More actions

  June 2, 2008 at 3:28 am

  #822817

  Hi, you can do the following to export data from table to file

  DECLARE @SQL VARCHAR(2000)

  DECLARE @DAY VARCHAR(20)

  DECLARE @FILE VARCHAR(100)

  DECLARE @DIR VARCHAR(100)

  DECLARE @TABLE VARCHAR(100)

  SET @DIR = 'G:\DRIVE\'

  SET @TABLE = '"SELECT * FROM DATABASE.DBO.TABLE"'

  SET @DAY = CONVERT(VARCHAR(20), GETDATE(), 102)

  SET @DAY = REPLACE(@DAY, '.', '_')

  SET @FILE = FILENAME_' + @DAY + '.txt'

  SET @SQL = 'BCP ' + @TABLE + ' QUERYOUT ' + @DIR + @FILE + ' -c -t, '

  EXECUTE master..xp_CmdShell @SQL, NO_RETURN

  HOPE THIS HELP

  [font="Comic Sans MS"]Sara[/font]

  A ship in the harbour is safe . . . but that's not what ships were made for. 😀