August 17, 2010 at 1:10 am
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
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
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
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