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!

Combining many records into 1

Using SQL 2000, how can you combine multiple records into 1?
The source data is varchar(255), the destination will be text. I need help
with the select statement.

example tables:
CREATE TABLE [NoteHeader] (
[NoteID] [int],
[CustomerID] [int] ,
[Desc1] [varchar] (255),
[Date] [datetime] ,
)
GO

CREATE TABLE [NoteDetail] (
[NoteId] [int],
[SeqNum] [int] NOT NULL ,
[Note1] [varchar] (255),
[Note2] [varchar] (255),
[Note3] [varchar] (255),
[Note4] [varchar] (255),
[Note5] [varchar] (255)
)
GO

Sample script joining tables:
SELECT *
FROM NoteHeader INNER JOIN
NoteDetail ON NoteHeader.NoteID = NoteDetail.NoteId

Sample results:
NoteID CustomerID Desc1 Date
Note1 Note2
....Note5
1111 987 Note Header Description 2007-07-15
Notes detail record 1 field 1 Notes detail record 1 field2 ....
1111 987 Note Header Description 2007-07-15
Notes detail record 2 field 1 Notes detail record 2 field 2

Desired results:
NoteID CustomerID Desc1 Date
CombinedNotes
1111 987 Note Header Description 2007-07-15
Notes detail record 1 field 1 +

Notes detail record 1 field2 +

Notes detail record 2 field 1 +

Notes detail record 2 field 2 +

through unlimited number of records up to 5
fields each

The NoteID field is the unique number. 1 record per NoteID in NoteHeader,
NoteDetail can have unlimited number of same NoteID (usually not more than
10)rdraider (rdraider@.sbcglobal.net) writes:

Quote:

Originally Posted by

Using SQL 2000, how can you combine multiple records into 1?
The source data is varchar(255), the destination will be text. I need
help with the select statement.

SQL Server MVP Anith Sen as a couple of methods on
http://www.projectdmx.com/tsql/rowconcatenate.aspx.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||rdraider (rdraider@.sbcglobal.net) writes:

Quote:

Originally Posted by

Thanks for the info. My problem is the resulting data will be too large
for varchar(8000). All these examples seem to use varchar(8000)
I need to convert to a text datatype. I can concat multiple varchar fields
from 1 record into text but the problem is how the source data is
structured.
The source data is from an app called 'Onyx' running SQL 6.5 (I'm naming
names !!). I upgraded the SQL 6.5 to SQL 2000. I don't hav SQL 2005.

I think you have two options:

1) Get SQL 2005.
2) Do it client-side.

I think you can do it on SQL 2000, but then you would have to run
a cursor, and use WRITETEXT and UPDATETEXT and it would be very very
painful. Please don't ask me to write the code for you, but if you
have problems with using WRITETEXT and UPDATETEXT, I can try to assist.

Quote:

Originally Posted by

I assume it was designed this way because SQL 6.5 largest data type was
varchar(255) ?

Yes, that is correct.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsqlsql

Column mapping in SSIS

Hi,

My Destination columns are more than source columns....

So, how to do column mapping if my source and destination columns are different?

Thanx,

Ruja

Even if the number of columns is different, the mapping should be done automatically if the names of the destination columns are the same of the source.

For all the others destination columns ... you have to do it manually.

Cosimo

|||

Ruja wrote:

Hi,

My Destination columns are more than source columns....

So, how to do column mapping if my source and destination columns are different?

Thanx,

Ruja

I don't understand very well. Are you taking about diffrences in data types? for that you have to cast them to the proper data type using data Conversion or derived column transforms.

If the diffrence is just on the name; you have to create the mapping manually (drag and drop to create the arrows)...

If you have more columns on source than in the target....you shoud know what to do, since you are the only one that knows your data

|||If you simply have more columns in the destination than the source, you can "ignore" the destination columns that don't map to the source, and they'll simply get NULLs (or the default value if defined in the database schema) inserted into those fields that you ignore.

You don't have to match on the number of columns.|||

Ruja wrote:

Hi,

My Destination columns are more than source columns....

So, how to do column mapping if my source and destination columns are different?

Thanx,

Ruja

How can anyone except yourself possibly answer that question?

If you have more columns in your destination than in your source and this is a problem to you - change either your source or your destination.

-Jamie

Column Mapping in a Custom Component

Does anyone know how to get destination coulmns to show up in the advanced editor for a custom component? I have a custom flat file destination component that builds the output based on a specific layout. It works as long as the upstream column names match my output names. What I want is to allow non-matching columns to be mapped by the user as they can in a stock flat file destination. The closest that I have been able to come is to get the "column mappings" tab to show up and populate the "Available Input Columns" by setting ExternalmetadataColumnCollection.IsUsed to true on the input. The problem is that the "Available destination columns" box is always empty. I have tried the IsUsed property on the output and pretty much every other property that I could find. On the Input and Output properties all of my columns show up under the output as both External and Output columns. Is there a separate collection for "destination" columns that I can't find? It's getting a little frustrating, is this something that can be done or do I have to write a custom UI to make it happen?

Thanks!
Harry

Have you added columns to the external metadata column collection for you destination? The available destination columns come from this collection so if you haven't put any columns there then there are none to be displayed.

Matt

|||

Matt,

Thanks for your response. Yes, I think that I have added the columns to the external metadata for the output. The output is built from a sql server table containing the layout. The layout (copybook) is supplied via a custom property. All of the columns show up correctly on the input and output page and there is an external and output list there. Here is my code that builds the output. I have also tried setting the SyncronousInputID and it doesn't make any difference.
(Please keep in mind that I am just learning C# )

Thanks!
Harry

IDTSOutput90 output = this.ComponentMetaData.OutputCollection.New();
output.Name = _copybook;
output.ExternalMetadataColumnCollection.IsUsed = true;
output.SynchronousInputID = 0;
IDTSOutputColumnCollection90 outCols = output.OutputColumnCollection;
IDTSExternalMetadataColumnCollection90 extCols = output.ExternalMetadataColumnCollection;
SqlConnection sqlConn = new SqlConnection();
UpdateConnectionString();
sqlConn.ConnectionString = _connectionString;
sqlConn.Open();
SqlCommand sqlCmd = sqlConn.CreateCommand();
sqlCmd.CommandText = "Select ColumnName, ColumnType, Length, Precision, Scale" +
" From " + _copybook +
" Order By ColumnSeq";
SqlDataReader sqlRdr = sqlCmd.ExecuteReader();
DataType dtColType;
while (sqlRdr.Read()) {
string ColumnName = sqlRdr.GetString(0).Trim();
string ColumnType = sqlRdr.GetString(1).Trim();
Int16 Len = sqlRdr.GetInt16(2);
Int16 Prec = sqlRdr.GetInt16(3);
Int16 Scale = sqlRdr.GetInt16(4);
IDTSOutputColumn90 outColumn = outCols.New();
outColumn.Name = ColumnName;
switch (ColumnType) {
case "Z":
dtColType = DataType.DT_NUMERIC;
Len = 0;
break;
case "P":
dtColType = DataType.DT_NUMERIC;
Len = 0;
break;
case "B":
if (Len > 4)
dtColType = DataType.DT_I8;
else if (Len > 2)
dtColType = DataType.DT_I4;
else
dtColType = DataType.DT_I2;
Len = 0;
break;
default:
dtColType = DataType.DT_WSTR;
break;
}
outColumn.SetDataTypeProperties(dtColType, Len, Prec, Scale, 0);
IDTSExternalMetadataColumn90 extColumn = extCols.New();
extColumn.Name = outColumn.Name;
extColumn.DataType = outColumn.DataType;
extColumn.Length = outColumn.Length;
extColumn.Precision = outColumn.Precision;
extColumn.Scale = outColumn.Scale;
extColumn.CodePage = outColumn.CodePage;
outColumn.ExternalMetadataColumnID = extColumn.ID;
}

sqlRdr.Close();
sqlCmd.Dispose();
sqlConn.Close();
sqlConn.Dispose();

|||

Well, I don't know if this is the only problem but destinations don't have outputs only inputs (genrally speaking anyhow). A destination usually just has an input (and many have an error output, but that is just for rows that fail to get written to the destination. You would then add external columns to the input's external columns collection and map the column to the associated input column.

Matt

|||

Matt,

Thanks, that was it. I didn't know that the mapping relation was between the input and the external metadata for the input. It makes sense, I was just stuck on mapping input to output. This is really supposed to be a tranformation so that's where the output came from. I just made it a destination to see if that was my issue. It probably did look a bit odd to have an output on a destination

Again, thank you for your help.

Harry

Column Datatype

Hi folks, need ur help.

I want to convert strings from a textfile and convert em to appropriate datatype for the destination columns, eg if i have a source value in string 11111 and the destination column is MONEY. I want to use CONVERT(functionretruningdatatype,'11111') in my insert statement.

Any guidance!

Howdy!SQL Server does implicit data type conversion. Refer to
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

If you need any conversion outside of the implicit conversions, you need to use dynamic SQL to construct the INSERT statement with the appropriate datatype