• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

DTS (from SQL Server to Text Files)

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
samir_tartir
Asked:
samir_tartir
  • 4
  • 3
1 Solution
 
acampomaCommented:
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
 
samir_tartirAuthor Commented:
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
 
nigelrivettCommented:
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
samir_tartirAuthor Commented:
nigelrivett, please read my previous comment.
0
 
nigelrivettCommented:
I did.
Nothing there that you can't build once to act on a table given as a parameter.
0
 
samir_tartirAuthor Commented:
nigelrivett, explain please.
0
 
nigelrivettCommented:
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
 
samir_tartirAuthor Commented:
thanks, that would help.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now