Odd DTS Export

[Idokyoku2]

I've been trying to export a table with Carriage Return Line Feeds to output in Label Format. I tried adding the numeric equivalent of a CR LF, but won't alter data. It simply exports the file as usual.

Data needs to appear like so to run on our inkjet machines in the production dept:

LW53555
***************CR-RT 02
BOB SKISONOVICH
MY COMPANY
123 MAIN ST
ANYTOWN, CA 91352
|||||||||||||||||||||||||||||

I should mention I know nothing about VB.

David

 

[EdwinGene]

You don't have to know VB. If you put your label into a small SQL Server table (each row of data in the table being one line of your label) and then use the Data Pump task to export the table, the Data Pump task will insert CRLFs for you, automatically.

 

[Idokyoku2]

It's not in label format, that is the problem. It's standard data format.

idnum carrier_route name company ,etc

How do I get it to that format?

 

[EdwinGene]

Create a table with one varchar column. Insert your data into that table, grouping in together by rows. Then, export the single-column table.

 

[Idokyoku2]

That thought actually came to mind after your post and I thought you might say something along those lines. However, I'm just not quite sure how how to do it.

Don't tell me, let me work on it and I'll post in a bit.

Thanks,
David

 

[Idokyoku2]

EdwinGene,
Having some trouble accomplish this, please help.

David

 

[EdwinGene]

Do you know DTS? Do you know Transact SQL? Can you write a stored procedure? I'm not being flippant or condescending, but you have to know these things in order to do this. DTS is very graphics oriented and it shouldn't take too much time to learn how to create a simple package. Books On Line (Query Analyzer Help) can show you how to create a stored procedure. Transact SQL is the language to write the stored procedure in.

Is there someone around there who can help you face-to-face?

Create a stored procedure to populate the export table. Call this stored procedure from the DTS package using the Execute SQL Task. Then use the MS OLEDB connection, the Transform Data Task and the Text File (Destination) connection to export the data to a text file.

 

[Idokyoku2]

Wow! How flattering!
I never had the need to insert multiple columns into one. I did concatenate the strings into a single column in another table. Are you saying my group by clause should stack the data in my one column table ? If so, I definitely could use some help with that part.

D

 

[yksvaan]

Maybe this a (c)old case, but what I would do is to change textfiles connection properties so that Column Delimiter is CRLF. You can't do this with dts dialog UI (which is stupid enough of UI), but with Disconnected Edit you can (right-click package white space). In text file's connection's OLEDB properties there's Row Delimiter and Column Delimiter, you can copy/paste value from Row Delimiter to Column Delimiter (there might be other ways to do this), since you propably have CRFL as Row Delimiter.

Clean sweep, no need to fuzz and buzz with tricky sql programming structures and temp tables in stored procedures or VBScript, though I am not meaning that EdwinGene's advice were no good.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[Idokyoku2]

yksvaan,
I just happened to check this after many days. I've already tried all of what you suggested before my post, but nothing seems to work-I've given up!

I'm just not sure it's actually possible without someone actually having done this.

Thanks for the post.

David