Post reply

Need Help - SSIS pkg

  • August 17, 2010 at 1:10 am

    #239034

    I am new to 2005 SSIS.

    I need to create a SSIS package for reporting purpose.

    Previously this was in sql proc and it was called through agent job.

    --------------------

    That proc has 3 table variables, and it getting data from different tables and variables.

    Finally the result comes from 3 select stmts using union all stmts.

    Then the result will insert to a staging table.

    Finally we export the staging table data to flat file.

    ----------------------

    Now we want change the above process to 2005 SSIS pkg.

    We need to start with 3 select stmts and then combine with union stmt through SSIS.

    The result will insert the staging table with sorted order.

    Finally the data will send to the flat file.

    can any one help for this?

    thanks in advance

    Nithi

  • August 17, 2010 at 1:37 am

    #1207685

    Nithi

    Please will you be more specific about what sort of help you're looking for? A good place to start would be if you posted your stored procedure definition.

    John

  • August 17, 2010 at 1:53 am

    #1207687

    my proc looks like,

    declare @tblvar1 (company varchar(25), ...)

    declare @tblvar2 (company varchar(25), ...)

    declare @tblvar3 (company varchar(25), ...)

    insert into @tblvar1 (company ,....)

    select company......

    from

    (select company ...

    from tbl1 inner join tbl2...)

    union all

    select company....

    from tbl3 inner join tbl4 ) kt

    group by company.....

    insert into @tblvar2 (company,....)

    select company ... from tbl5

    insert into @tblvar3 (company,....)

    select company ... from

    (select company...

    from tbl5 inner join @tblvar1 ....)

    truncate table rpttable

    insert into rpttable

    (company .....)

    select company ....

    from

    ( select company .... from @tblvar1

    union all

    select company .... from @tblvar2

    union all

    select company .... from @tblvar3

    ) ht

    order by company.....

  • August 17, 2010 at 2:02 am

    #1207689

    Nithi

    You still haven't specified what you need help with.

    Let's look at your stored procedure, though. It looks unnecessarily complicated. Why do you not just insert straight from the source tables to the staging table, and dispense with the table variables?

    I imagine that you're looking for help with creating the SSIS package to move the data from the staging table to the text file? What aspect of this are you struggling with?

    John

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

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