combining multiple tables into a single flat file destination

Hi,

I want to combine a series of outputs from tsql queries into a single flat file destination using SSIS.

Does anyone have any inkling into how I would do this.

I know that I can configure a flat file connection manager to accept the output from the first oledb source, but am having difficulty with subsequent queries.

e.g. output

personID, personForename, personSurname,
1, pf, langan

***Roles
roleID, roleName
1, developer
2, architect
3, business analyst
4, project manager
5, general manager
6, ceo

***joinPersonRoles
personID,roleID
1,1
1,2
1,3
1,4
1,5
1,6

Use Merge Joins to join your data sources. You'll need to use more than one because the Merge Join uses two inputs only.

http://msdn2.microsoft.com/en-us/library/ms141775.aspx

|||

You can do the merge join transformations as phil stated, or use a series of lookup transformations, or you could simply do a join on your initial data source query...

select out.personID, out.personForename, out.personSurname, role.roleID, role.roleName

from output as out

INNER JOIN

joinPersonRoles as pr

ON

pr.personID = out.personID

INNER JOIN

roles as role

ON

role.roleID = pr.roleID

Do you want your output to have a single row per person or are multiple rows ok? If you need it all in a single row you will also need to use a pivot transformation (or use pivot in your t-sql statement).

|||Performing the join in the source query via T-SQL as EWisdahl stated is the best route because it lets the database engine do the work.|||Ah, but you're missing my question.

I know how to do joins in order to produce a single recordset to output to a flat file.

I don't want to output a single set of records. I want to produce 3 sets, and output them all to the same file.

the example output I provided is exactly as I want the flat file.

Basically I want to run 3 data flow tasks in sequential order that appends their own output to the flat file destination.|||

So make three data flow tasks and three flat file connection managers, each referencing the same file.

Hook the data flow tasks up together in the control flow to enforce precedence.

What's the problem, I guess? It sounds like you've got it figured out: "Basically I want to run 3 data flow tasks in sequential order that appends their own output to the flat file destination."

|||

This is an odd request, however, you could potentially build up your own csv record.

Have each record be a single column text field and use a derived column transformation to string all of your current columns together for each of the sources.

After you pull these together do a union all.

If needed, you can do a select to grab the metadata information (i.e. table and column names) to push into the output as well. (i.e. select 'tablename'; select 'column1, column2, columnN'; etc...)

:edit - phil once again provided a working answer above while I was typing ... :

|||

great.. thank you so much!

Column Count from Stored Procedure

I am trying to determine the number of columns that are returned from
a stored procedure using TSQL. I have a situation where users will be
creating their own procedures of which I need to call and place those
results in a temp table. I will not be able to modify those users
procedures. I figure if I have the number of columns I can dynamically
create a temp table with the same number of columns, at which point I
can then perform an INSERT INTO #TempTableCreatedDynamically EXEC
@.UserProcCalled. With that said, does anyone have any idea how to
determine the number of rows that an SP will return in TSQL?

Thanks!rolandobarberis@.hotmail.com (Rolando Barberis) wrote in message news:<ca3f9ee4.0405051740.7e090c11@.posting.google.com>...
> I am trying to determine the number of columns that are returned from
> a stored procedure using TSQL. I have a situation where users will be
> creating their own procedures of which I need to call and place those
> results in a temp table. I will not be able to modify those users
> procedures. I figure if I have the number of columns I can dynamically
> create a temp table with the same number of columns, at which point I
> can then perform an INSERT INTO #TempTableCreatedDynamically EXEC
> @.UserProcCalled. With that said, does anyone have any idea how to
> determine the number of rows that an SP will return in TSQL?
> Thanks!

If you really need to do this, the easiest way would be to do it on
the client side - for example, retrieve an ADO RecordSet, then use the
RecordSet metadata to create a table.

However, there are some issues with this general approach - if you
don't know the format of the result set in advance, then it's
difficult to do anything meaningful with it. You can't write SQL code
to process the temp table, because you don't know anything about the
number of columns, the data types, the row count (you might want to
use paging for a large result set, for example) etc. And this is in
addition to the obvious issues (security, performance, maintenance)
which may arise from allowing users to create their own code in the
database.

But since you don't give any detailed information about your goals or
your environment, it's possible that you do have good reasons for
looking at this solution. If you can give more information about what
you're trying to do, though, someone may have an alternative idea to
propose.

Simon|||Rolando Barberis (rolandobarberis@.hotmail.com) writes:
> I am trying to determine the number of columns that are returned from
> a stored procedure using TSQL. I have a situation where users will be
> creating their own procedures of which I need to call and place those
> results in a temp table. I will not be able to modify those users
> procedures. I figure if I have the number of columns I can dynamically
> create a temp table with the same number of columns, at which point I
> can then perform an INSERT INTO #TempTableCreatedDynamically EXEC
> @.UserProcCalled. With that said, does anyone have any idea how to
> determine the number of rows that an SP will return in TSQL?

This is a dead end. There is no way you can do this in SQL only. As Simon
says, you need to go client-side and deal with the the procedures there.

Not even client-side there is any good way to determine the number of
columns without running the procedure. In some contexts, ADO uses
SET FMTONLY ON which causes SQL Server to only sift through the statements
without executing them, but still return information about the result
sets. But there are several unexpected things that can happen with SET
FMTONLY ON, so in my opinion it's useless.

Then again, once you are client-side, it is not problem to run the
procedures. SQL Server will return information about the result sets,
and you will get recordsets, data tables or whatever depending on
which client library you use. (My personal preference is for ADO .Net.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Column Count from Stored Procedure

I am trying to determine the number of columns that are returned from
a stored procedure using TSQL. I have a situation where users will be
creating their own procedures of which I need to call and place those
results in a temp table. I will not be able to modify those users
procedures. I figure if I have the number of columns I can dynamically
create a temp table with the same number of columns, at which point I
can then perform an INSERT INTO #TempTableCreatedDynamically EXEC
@.UserProcCalled. With that said, does anyone have any idea how to
determine the number of rows that an SP will return in TSQL?

Thanks!rolandobarberis@.hotmail.com (Rolando Barberis) wrote in message news:<ca3f9ee4.0405051740.7e090c11@.posting.google.com>...
> I am trying to determine the number of columns that are returned from
> a stored procedure using TSQL. I have a situation where users will be
> creating their own procedures of which I need to call and place those
> results in a temp table. I will not be able to modify those users
> procedures. I figure if I have the number of columns I can dynamically
> create a temp table with the same number of columns, at which point I
> can then perform an INSERT INTO #TempTableCreatedDynamically EXEC
> @.UserProcCalled. With that said, does anyone have any idea how to
> determine the number of rows that an SP will return in TSQL?
> Thanks!

If you really need to do this, the easiest way would be to do it on
the client side - for example, retrieve an ADO RecordSet, then use the
RecordSet metadata to create a table.

However, there are some issues with this general approach - if you
don't know the format of the result set in advance, then it's
difficult to do anything meaningful with it. You can't write SQL code
to process the temp table, because you don't know anything about the
number of columns, the data types, the row count (you might want to
use paging for a large result set, for example) etc. And this is in
addition to the obvious issues (security, performance, maintenance)
which may arise from allowing users to create their own code in the
database.

But since you don't give any detailed information about your goals or
your environment, it's possible that you do have good reasons for
looking at this solution. If you can give more information about what
you're trying to do, though, someone may have an alternative idea to
propose.

Simon