Solved

DTS (from SQL Server to Text Files)

Posted on 2001-07-25
8
192 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
nigelrivett, please read my previous comment.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 18

Expert Comment

by:nigelrivett
Comment Utility
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
Comment Utility
nigelrivett, explain please.
0
 
LVL 18

Accepted Solution

by:
nigelrivett earned 100 total points
Comment Utility
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
Comment Utility
thanks, that would help.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

771 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

10 Experts available now in Live!

Get 1:1 Help Now