Format settings from flat file connection manager

Question

Post reply

Format settings from flat file connection manager

Jobs90312

Mr or Mrs. 500

Points: 533
More actions
October 19, 2022 at 7:13 am

#4101299
Hello everyone,
I have a SQL table in which there are some columns like Name, Age, City etc.
And in rows there is some data.
Now what I am trying to do is print this data to a text file in this format:
```
<name>
   <abc>
<age>
   <44>
<city>
   <Tokyo>
<phone>
   <11324242>
<email>
   <hj@gmail.com>
```
I tried various settings in flat file connection manger in SSIS package but so far no success.
Is it achievable from flat file manger or is there another way?
Check image for flat file connection manager settings.
Regards
- This topic was modified 2 years, 2 months ago by Jobs90312.
Attachments:
You must be logged in to view attached files.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

I don't think that there is a 'no code' way of achieving this.

But if you know how to code and use Script Components, this is possible.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Phil Parkin SSC Guru Points: 246985 More actions · Answer 2

Having said that, if you can transform your source query so that the results look like this, it would be straightforward.

ExcelData

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jobs90312 Mr or Mrs. 500 Points: 533 More actions · Answer 3

Phil Parkin wrote:

Having said that, if you can transform your source query so that the results look like this, it would be straightforward.

Can you please tell me what keywords can I use in my source query to make data look like this?

Phil Parkin SSC Guru Points: 246985 More actions · Answer 4

Keywords indeed! OK, here is one way, which (for ordering purposes) relies on every person having a unique Id column.

DROP TABLE IF EXISTS #SomeData;

CREATE TABLE #SomeData
(
    PersonId INT NOT NULL PRIMARY KEY CLUSTERED
   ,Name VARCHAR(50) NOT NULL
   ,Age INT NOT NULL
);

INSERT #SomeData
(
    PersonId
   ,Name
   ,Age
)
VALUES
(1, 'abc', 44)
,(2, 'def', 55);

SELECT c1.PersonId
      ,c1.Ctr
      ,c1.Item
FROM #SomeData sd
    CROSS APPLY
(
    SELECT x.PersonId
          ,x.Ctr
          ,x.Item
    FROM
    (
        VALUES
            (sd.PersonId, 1, '<name>')
           ,(sd.PersonId, 2, CONCAT('<', sd.Name, '>'))
           ,(sd.PersonId, 3, '<age>')
           ,(sd.PersonId, 4, CONCAT('<', sd.Age, '>'))
    ) x(PersonId, Ctr, Item)
) c1
ORDER BY c1.PersonId
        ,c1.Ctr;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Format settings from flat file connection manager

Attachments: