[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
SolvedPrivate

Data from SQL table to text file

Posted on 2014-02-10
13
Medium Priority
?
50 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
[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
  • 6
  • 3
  • 2
  • +1
13 Comments
 
LVL 66

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
Independent Software Vendors: 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!

 

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 66

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 66

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

649 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