DTS - Fixed Length File

Question

DTS - Fixed Length File

billyburns

Ten Centuries

Points: 1108
More actions
June 2, 2003 at 10:09 am

#80995

Can anyone tell me the best way to get DTS to output a query to a fixed length file?
I have a query that concatenates all the individual column values into one long column (query runs fine in Query Analyzer)and I keep getting an 'Invalid Column Size' error when trying to output to a text file with DTS. The rows returned from the query are < 8k bytes
TIA!!!

Viewing 9 posts - 1 through 8 (of 8 total)

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

eljeffo Ten Centuries Points: 1040 More actions · Answer 1

William, it'd help to know a little more about the structure of what you're trying to export. I have several files that I have to create from data into flat FL files for export to my billing companies, so I may have some help for you. In my case, I have created views that return the line formatted up the way I want it, and I run the DTS packages using the views as the data sources. Give me a little more to go on if you would, because this sounds a lot like the battles we faced until we figured out to use pre-formatted views. Carpe carp (sieze the fish)

billyburns Ten Centuries Points: 1108 More actions · Answer 2

Thanks for the reply.

The script that I have written requires creating temp table to be able to get the final rowset...so I can't use a view (to my knowledge).

Here is an example of the script I use. If you put it into DTS Export Data from Northwind you'll see the error I get.

USE NORTHWIND



SELECT * INTO #tmp_customers

FROM Customers



SELECT CAST(CustomerID AS CHAR(10)) 

+

CAST(CompanyName as CHAR(50))

+

CAST(SPACE(50) AS CHAR(50))

+

CAST(City AS CHAR(5))

AS FixedLengthRow

FROM #tmp_customers

eljeffo Ten Centuries Points: 1040 More actions · Answer 3

I guess I didn't understand the question then... if you're having the DTS package create the flat file, how are you getting the number of rows as part of the last row (or am I completely mistreading your posts?)?

billyburns Ten Centuries Points: 1108 More actions · Answer 4

I don't think I did a good job on the post.

Here's another way to tell me what I want to know.

Take the above script and export the results to a file using DTS (or some other automatable means).

TIA!

eljeffo Ten Centuries Points: 1040 More actions · Answer 5

Okay, I think I got it now: you're running twice the work. You don't need to load the temp table first. Just create a view like this:

CREATE VIEW FormattedString

AS

SELECT

CAST(CustomerID AS CHAR(10)) +

CAST(CompanyName as CHAR(50)) +

SPACE(50) +

CAST(City AS CHAR(5))

FROM Customers

Then run your DTS package from the view.

That should solve your problem.

billyburns Ten Centuries Points: 1108 More actions · Answer 6

In my production script (which is considerably longer) I do HAVE to build a temp table to get the result set it need.

billyburns Ten Centuries Points: 1108 More actions · Answer 7

I figured it out.

I created a stored procedure out of the script and have DTS executing the SP. Also I stopped concatenating the results together and it works brilliantly.

The reason I originally recieved errors is because I didn't set SET NOCOUNT OFF. Silly me.

eljeffo Ten Centuries Points: 1040 More actions · Answer 8

eljeffo

Ten Centuries

Points: 1040

June 3, 2003 at 2:24 pm

#459605

I hope I helped. Congrats on fixing it!