Create Text File from SELECT

Question

Create Text File from SELECT

Viewing 4 posts - 16 through 18 (of 18 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Orlando Colamatteo (1/27/2016)
What you have here is a multi-record format where the header line has a different column-format than the data lines. T-SQL solutions traditionally have trouble with these as hou have found when tying to use UNION. In SSIS this is not a problem. You can do this by writing to the file two separate times: once to write the header and once to write the data lines.
Create two Connection Managers, one for tbe header and one for tbe data lines. In the one for data lines configure it to append data to the file instead of overwriting the file. Implement teo Data Flows, get the header and write it to the file and a second for the data lines. Post back if you have issues.

Exactly. It either takes two separate writes (the second being an append of detail in TSQL or in SSIS as you say) or you have to homogenize the data to fit a UNION ALL, the former being the much easier of the two for both SSIS and T-SQL. Doing such a thing in either TSQL or SSIS is pretty simple if you know how.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

Jeff Moden (1/28/2016)
Orlando Colamatteo (1/27/2016)
What you have here is a multi-record format where the header line has a different column-format than the data lines. T-SQL solutions traditionally have trouble with these as hou have found when tying to use UNION. In SSIS this is not a problem. You can do this by writing to the file two separate times: once to write the header and once to write the data lines.
Create two Connection Managers, one for tbe header and one for tbe data lines. In the one for data lines configure it to append data to the file instead of overwriting the file. Implement teo Data Flows, get the header and write it to the file and a second for the data lines. Post back if you have issues.
Exactly. It either takes two separate writes (the second being an append of detail in TSQL or in SSIS as you say) or you have to homogenize the data to fit a UNION ALL, the former being the much easier of the two for both SSIS and T-SQL. Doing such a thing in either TSQL or SSIS is pretty simple if you know how.

I have explained one way to do this with SSIS (there are numerous ways). I know how I would approach this using T-SQL, xp_cmdshell and various other tools to meet this requirement. I recently asked you on another similar thread dealing with multi record formats where you also said a TSQL solution was readily available but you did not post back...how would you propose the OP solve this with a TSQL based solution?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Orlando Colamatteo (1/28/2016)
Jeff Moden (1/28/2016)
Orlando Colamatteo (1/27/2016)
What you have here is a multi-record format where the header line has a different column-format than the data lines. T-SQL solutions traditionally have trouble with these as hou have found when tying to use UNION. In SSIS this is not a problem. You can do this by writing to the file two separate times: once to write the header and once to write the data lines.
Create two Connection Managers, one for tbe header and one for tbe data lines. In the one for data lines configure it to append data to the file instead of overwriting the file. Implement teo Data Flows, get the header and write it to the file and a second for the data lines. Post back if you have issues.
Exactly. It either takes two separate writes (the second being an append of detail in TSQL or in SSIS as you say) or you have to homogenize the data to fit a UNION ALL, the former being the much easier of the two for both SSIS and T-SQL. Doing such a thing in either TSQL or SSIS is pretty simple if you know how.
I have explained one way to do this with SSIS (there are numerous ways). I know how I would approach this using T-SQL, xp_cmdshell and various other tools to meet this requirement. I recently asked you on another similar thread dealing with multi record formats where you also said a TSQL solution was readily available but you did not post back...how would you propose the OP solve this with a TSQL based solution?

Method 1

BCP Out the Header file using the table columns from the table meta data.

BCP Out the Detail file using the table columns in the same order.

Use the DOS COPY command to merge the files into one.

Use the DOS DELETE command to delete the Header and Detail files.

...OR...

Method 2

Use dynamic SQL to create a blob for the header and each row of the detail into a new table.

BCP out the file.

Note that if it's a "fixed field format" requirement (usually not), the dynamic creation of a BCP Format file would make things relatively easy for Method 1 and would complicate Method 2 a bit in that you would either need to hard code something into the dynamic code or use a "mapping instruction" table to drive the process.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

Thanks for sharing Jeff. Method 1 is what I had in mind for a T-SQL+command-line solution. Method 2 sounds quite interesting.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato