Post reply

DTS - Fixed Length File

  • 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!!!

  • June 2, 2003 at 1:57 pm

    #459414

    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. <br> <br>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.<br><br>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.<br><br>Carpe carp (sieze the fish)

  • June 2, 2003 at 2:29 pm

    #459422

    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

  • June 2, 2003 at 3:11 pm

    #459432

    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?)?

  • June 3, 2003 at 7:38 am

    #459535

    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!

  • June 3, 2003 at 10:41 am

    #459585

    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.

  • June 3, 2003 at 10:48 am

    #459587

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

  • June 3, 2003 at 2:22 pm

    #459604

    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.

  • June 3, 2003 at 2:24 pm

    #459605

    I hope I helped. Congrats on fixing it!

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

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