SolvedPrivate

Data from SQL table to text file

Posted on 2014-02-10
13
45 Views
Last Modified: 2016-02-10
I need to output data from  SQL table to a text file with permanent header.
 sample output below.

header--stays the same always (in tableA)

sample  header output

ABCDEFPASS          2345uuNA          ABCDEFG        ABCGFRT     F          ABC       TESTING      
                                                 
Sample details  line - (in table B)

Business 1        MANNTR    4567777              789999                80000
Business 2        FANNTR     6387999               789653               70000
Business 3        Bauctyr       4569877              369887               60000

The header and details do not match.  The header does not change.  The details changes
every day.


Inputs appreciated
0
Comment
Question by:Nana Hemaa
  • 6
  • 3
  • 2
  • +1
13 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39847700
>header--stays the same always (in tableA)
In SSIS, go to the Connection Manager, double-click on whichever connection represents this textfile, and eyeballeth thy 'Header Rows to skip' listbox.  You can add a 1 here for SSIS to skip the header row, and process all other rows.

>The details changes every day.
If you mean the data changes, but the file format is the same, no problem.

If you mean the columns will change, this is going to be a show-stopper, as SSIS requires a 'contract' with a data source in order to consume it, meaning that the file spec has to be well defined and does not change.

Normally the only alternative is to import the entire row as a single column, then write T-SQL to figure out how to parse each column based on 'The
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39847708
you can use the BCP command to accomplish this task.

bcp "
select 'ABCDEFPASS','2345uuNA','ABCDEFG','ABCGFRT','F','ABC','TESTING'
UNION ALL
select * from <your Table>" queryout '<dir path>' -c -T

Open in new window

0
 

Author Comment

by:Nana Hemaa
ID: 39847728
The data changes but the file format is the same--thx

>The details changes every day.
If you mean the data changes, but the file format is the same, no problem.
0
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 

Author Comment

by:Nana Hemaa
ID: 39848258
can you pls. ellaborate the bcp-- at command prompt or via SQL management?
0
 

Author Comment

by:Nana Hemaa
ID: 39848337
header and details do not have same number of columns for the UNION
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39848359
Abonsu - Let us know if you have an exact question in mind.  

'Inputs appreciated' is going to solicit every angle under the sun, whether you are looking for it or not.
0
 

Author Comment

by:Nana Hemaa
ID: 39848371
need to know  how to get this accomplished via SSIS
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39848394
>need to know  how to get this accomplished via SSIS
In simple terms (can't speak for the BCP solution, as I don't have those skills)

(1)  Open Visual Studio, and create a new SSIS Solution
(2)  Create a connection to your text file, making sure you enter a 1 in the  'Header Rows to skip' listbox so it knows to skip the header row.  Then set all of the column data types.
(3)  Create another connection to wherever you want to pump this data.
(4)  Create a data flow task control, then double-click on it.
(5)  Create a source task to your flat file connection.
(6)  Create a destination task to wherever this file is going.
(7)  Save, then execute.

Or, you can always go into SSMS, then click on the database, then right-click:Tasks: Import Data..., and see if the wizard will complete the above for you.  If yes, make sure you save the resulting SSIS package.
0
 

Author Comment

by:Nana Hemaa
ID: 39848637
Thanks Jim..I am confused at step 2.--Are you implying my destination flat file here

 I  save the header information from tableA(header table) to a text file called  header.txt since it is does not change.  


Header.txt
ABCDEFPASS          2345uuNA          ABCDEFG        ABCGFRT     F          ABC       TESTING    

I am not sure how to combine the header (header.txt) with the details in Table B to get the desired output in Text file   ---finaloutput.txt from above instructions

ABCDEFPASS          2345uuNA          ABCDEFG        ABCGFRT     F          ABC       TESTING      
Business 1        MANNTR    4567777              789999                80000
Business 2        FANNTR     6387999               789653               70000
Business 3        Bauctyr       4569877              369887               60000

I created a header flat file connection in ssis  pointing to Header.txt
I created an OLE DB Source to table B
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 39856099
The Flat File destination has a property called Overwrite. You can set that to False, that way you can use your header.txt as template.

Here's what the Control Flow would look like:

First use the File System Task to copy your template to a new location (or name).

Then use this new location/name in the Flat File destination and write your records to it.

Please note that the header line in the file should already end with a CRLF.
0
 

Author Comment

by:Nana Hemaa
ID: 39857007
Thanks.  The output looks better but when I change the overwrite property to True...It appends the detail records and does not overwrite it each time the package runs.
0
 
LVL 37

Assisted Solution

by:ValentinoV
ValentinoV earned 500 total points
ID: 39858300
but when I change the overwrite property to True

I think you meant False instead of True :)

It appends the detail records and does not overwrite it each time the package runs.

That's what the File System Task is for: first make a fresh copy of your template containing the header, then append records to that copy.  Through this method the Data Flow transform always starts with an "empty" file.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
GeoClustering  and AOG 25 41
SQL Pivot table 2 42
Find unused columns in a table 12 67
Many to one in one row 2 35
Lessons learned during ten years of interviewing for SQL Server Integration Services (SSIS) and other Extract-Transform-Load (ETL) contract roles and two years of staff manager interviewing contractors.
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…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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

685 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