Export Data to multiple excel sheet from SQL server Table using SSIS

Export Data to multiple excel sheet from SQL server Table using SSIS

Below solution where we need to export SQL data into multiple excel sheets, as we have below data in our SQL Table:

LoanID       State         TYPE        STEP

2                   CA            R6            1

3                   CA            R6           3

4                   CA            P1            4

67                 CA            P1            5

45                 CA           R2            7

23                 CA            R2            8

Now we want excel file (Destination) to have multiple tabs using column 'State' and 'Type'.

Below is how result in excel should be:

LoanID       State         TYPE        STEP

2                   CA            R6            1

3                   CA            R6           3

Second tab should be 'CA-P1'

LoanID       State         TYPE        STEP

4                   CA            P1            4

67                 CA            P1            5

And Third tab will be 'CA-R2'

LoanID       State         TYPE        STEP

45                 CA           R2            7

23                 CA            R2            8

Solution:

First tab should be 'CA-R6'

Sorting Alpha Numeric and Numeric data in SQL Server

Sorting Alpha Numeric and Numeric data

I have a varchar2 column in which data is present like :

1a

1b

1c

1

2

2a

2d 

and so on ... and output should be sorted like :

1,1a,1b,2,2c,2d ........ 11,22,23,24d ...

Below Query where I have used PATINDEX function of SQL Server, which is similar to the like operator

Syntax : The Patindex function takes two arguments:

PATINDEX ('%pattern%', exp)

declare @t table

(

v varchar(20)

)

insert @t

values ('1a'),

('1b'),

('1c'),

('1'),

('2'),

('2a'),

('2d '),

('11c '),

('111d '),

('11 '),

('22 '),

('222a '),

('222 '),

('111 '),

('11 ')

select *

from @t

order by left(v,patindex('%[^0-9]%',v + 'x')-1) * 1,v