?
Solved

DTS (from SQL Server to Text Files)

Posted on 2001-07-25
8
Medium Priority
?
255 Views
Last Modified: 2009-07-29
i want to extract data from sql server tables to text files.
i create the DTS using the "data tranformation services" node in the console rote of the enterprise manager.
it is quite an easy task except for the following two things that i wasn't able to find a way to:
1. i want my files to fixed width, and at the same time, a space must separate each two fields.
2. i want to right align numbers.

i hope somebody knows the solution for this one.

regards
0
Comment
Question by:samir_tartir
  • 4
  • 3
8 Comments
 
LVL 6

Expert Comment

by:acampoma
ID: 6319375
Create a DTS Package
In the dts package,
create a text file source and under properties set fixed field.
then select a sql server for your source,
select source while hoilding ctrl key and then destination and add a datapump task(black arrow)
then configure the data pump to
use a query to extract the data  from the source and convert number fields to char fields and ltrim them.
0
 

Author Comment

by:samir_tartir
ID: 6319580
thanks for your reply acampoma, but what i need is something that is already there, or something that i need to build only once. in my case, i have to repeat this process for several times within a number of dts's, therefore, working on a one-by-one basis will be time consuming.
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6320031
I would do this via bcp or osql.
If they don't provide the format you require then put the data into a table with a single wide column by formatting within an sp.

right aligning numbers can be done (in almost every language) by

right(space(n)+ convert(varchar(n),fld),n)
where n is the column width.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 

Author Comment

by:samir_tartir
ID: 6320089
nigelrivett, please read my previous comment.
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6325197
I did.
Nothing there that you can't build once to act on a table given as a parameter.
0
 

Author Comment

by:samir_tartir
ID: 6325228
nigelrivett, explain please.
0
 
LVL 18

Accepted Solution

by:
nigelrivett earned 300 total points
ID: 6326510
Depends on the exact requirements.
Something like:

Create a stored proc which accepts the table name as a parameter.

This stored proc looks at the information_schema views and creates select into statement which takes each of the columns in the tables, formats it into fixed  width string (needs to get column datatype and width) and concatenates into a single string.
This select into creates a global temp table.
Run a bcp on that global temp table to output the data to a text file.
Delete the global temp table
0
 

Author Comment

by:samir_tartir
ID: 6326856
thanks, that would help.
0

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

568 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question