locked
Splitting data into multiple excel spreadsheets RRS feed

  • Question

  • I want to load similar data of col1 and col2 from table t1 into their respective excel spreadsheet. For example:
    Rows 1 thru 4 from table t1 should be loaded into one excel spreadsheet and name it as
    a1.xls (a is from col1, 1 is from col2)
    As soon as combination of col1 and col2 changes, then that data should be in another spreadsheet
    i.e rows from 5 thru 7 should go into second spreadsheet and name it as b2.xls
    (b is from col1 and 2 is from col2)
    As soon as combination of col1 and col2 changes, then that should be in another spreadsheet
    i.e rows from 8 thru 10 should go into third spreadsheet and name it as c3.xls etc.

    Trying to figure out how we can do this using SSIS. Thanks.

    Below is the sample code:
     
    create table t1
    (col1 varchar(100),
    col2 varchar(100),
    col3 varchar(100))

    Insert into t1
    select 'a', 1, 'abc'
    union all
    select 'a', 1,'123'
    union all
    select 'a', 1,'AAA'
    union all
    select 'a', 1, 'ccc'
    union all
    select 'b', 2, 'aa1'
    union all
    select 'b', 2, 'bbb1'
    union all
    select 'b', 2, 'vvv1'
    union all
    select 'c', 3, 'aaa1'
    union all
    select 'c',3,'bbc1'
    union all
    select 'c',3,'zzza'

    select * from t1 order by col1 asc

     


    sqldev
    Friday, July 23, 2010 12:42 AM

Answers

    • Create a udf function , for example dbo.GetExcelFileName(col1, col2, col3) - it will return Excel file name
    • Add calculated column ExcelName to the table t1 and reference that function  dbo.GetExcelFileName
    • Use conditional split in SSIS

    Sergei
    Friday, July 23, 2010 3:02 AM