Solved

DTS (from SQL Server to Text Files)

Posted on 2001-07-25
8
238 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

691 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