CODEWITHSUNDEEP
sql-serverssis
Nadim
Nadim

Reputation: 13

Loading data into .txt file with fixed length ssis

I have a task to create SSIS package to load data into .txt file as below

Have one table ex: employees

Have two SQL statements:

select emp_no,birth_date,first_name from employees
where emp_no in (10001,10002,10003,10004);
Result: (Fixed Lengths as Follows emp_No=5,Birth_date=10,First_Name=10, Total Length: 25)
10001   1953-09-02  abc
10002   1964-06-02  def
10003   1959-12-03  ghi
10004   1954-05-01  jkl

Second Statement:

select emp_no,last_name,gender,hire_date from employees
where emp_no in (10001,10002,10003,10004);
Result: (Fixed Lengths as follows emp_No=5,lastname=9,gender=1,hire_date=10, Total Length: 25)
10001   Abcdef      M   1986-06-26
10002   Bcdefghi    F   1985-11-21
10003   Cdefghijk   M   1986-08-28
10004   Defgh       M   1986-12-01

I have to create a package to get the data as below

100011953-09-02abc
10001Abcdef   M1986-06-26
100021964-06-02def
10002Bcdefghi F1985-11-21
100031959-12-03ghi
10003CdefghijkM1986-08-28
100041954-05-01jkl
10004Defgh    M1986-12-01

Please suggest me how to do in SSIS or SQL server

Thanks

Upvotes: 0

Views: 59

Answers (2)

Laughing Vergil
Laughing Vergil

Reputation: 3756

Trick the system. Generate the nested output of both queries in a single query, like this:

select 
    Cast(emp_no AS Char(5)) + Convert(Char(10), birth_date, 120) + 
    CAST(first_name as CHAR(10)) + CHAR(13) + CHAR(10) +
    Cast(emp_no AS Char(5)) + CAST(last_name as CHAR(9)) + 
    gender + Convert(Char(10), hire_date, 120) 
from employees
where emp_no in (10001,10002,10003,10004);

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31785

Use a UNION ALL query in your data source to get a single-column resultset that looks the way you want it, and then just do a straight dataflow to your flat file destination.

Psuedo-code query:

SELECT {Query #1 Concatenated into one column, with added "SortBy" column = 1}
UNION ALL {Query #2 Concatenated into one column, with added "SortBy" column = 2}
ORDER BY emp_no, SortBy

Obviously don't map the "SortBy" column to your Destination so it won't show up in the flat file.

Upvotes: 0

Related Questions