SolvedPrivate

Data from SQL table to text file

Posted on 2014-02-10
13
42 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
 

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

 

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

From time to time, for debugging and troubleshooting your flow at run time you’ll need to check if the variable has the correct value or not, there’re several ways to check for the value of the variables inside the flow, You can add Break Points, a …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

707 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

12 Experts available now in Live!

Get 1:1 Help Now