Solved

DTS (from SQL Server to Text Files)

Posted on 2001-07-25
8
203 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
 

Author Comment

by:samir_tartir
ID: 6320089
nigelrivett, please read my previous comment.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 100 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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.

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now