Combining XML Files

I have to combine two seperate fixed-length flat files into 1 xml file, however, the catch is the 1st flat file (header) can/will be converted to an xml file but if there is another flat file *detail) found, then we must transform that to xml and combine into the header into one xml file.

So, the standalone header xml file would look like this:
<document>
<tables>
<header>
<rows>
<row>
<field1>header file 1 info....</field1>
<field2>header file 1 info....</field2>
</row>
</rows>
</header>
</tables>
<document>

and if a detail file was found, then the xml would look like this:
<document>
<tables>
<header>
<rows>
<row>
<field1>header file 1 info....</field1>
<field2>header file 2 info....</field2>
</row>
</rows>
</header>
<detail>
<rows>
<row>
<detfield1>details file 1 info....</detfield1>
<detfield2>details file 2 info....</detfield2>
</row>
</rows>
</detail>
</tables>
<document>

Seeing how you can't easily perform any if logic in a data-flow, what would be the best way to achieve this?

JAson

Are you saying there will be more than one details file? In your example, is detfield2 really supposed to contain a value from the second detail file?

|||

sorry, I meant that there would be more than 1 filed in the details file. So,

<detfield1>details file field_1 info....</detfield1>
<detfield2>details file field_2 info....</detfield2>

The tricky part is just to figure out how to manipulate an xml file (like removing just a few end tags, then inserting another xml portion, and replace the end tags that were removed) after it has been created and written to to a destination.

|||Given that there is no "XML Destination" adapter provided with SSIS, I think you're going to have to write one yourself if your goal is to end up with an XML document. If I were in your shoes, I think I'd write a custom, managed (not script) component. I'd give it two inputs: One for the header row, and the other for the detail rows. There's a good sample in BOL that you can use to get started.

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!

Columns [not null]

hi, I have a problem with a flat file and a table

I have a flat file with 5 columns and in the table I have 30 columns and they all are (not null)
I cannot leave other columns in target for which it throws mistake, have to insert something, since as I insert information in the demas columns?

Since I can solve this problem?

helps please

Use a derived column transform to create default values for each column not in text file|||

TheViewMaster wrote:

Use a derived column transform to create default values for each column not in text file

Thank you very much, I work perfectly

|||Glad to be of assistance
Please mark my previous post as an answer if you have a chance - need to make it to top asnwerererers

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 Length Help

I have a table this is populated by a flat file. In the desc field from the flat file some of the data is very long up to 150 characters in length. I have to export this data after some transformations to a fixed width file. I need to check the length of the data in the desc field and any thing that is to long needs to be put in a desc2 field, however any values in desc that are shorter than 36 characters in length needs to have spaces padding it up to 36 characters as well as the desc2 field. Any help would be great. I am not the best T-SQL programmer in the world and have limited time to get this done.

well, I think you can just do (I might be missing something, of course....):

select cast(substring (desc,1,36) + replicate(36,'') as char(36)) as desc,

cast(substring (desc,37,114) + replicate(114,'') as char(114)) as desc2

from sourceTable

Not sure why you would want to pad the data, that is a tremendous waste of space, generally

|||

Here it is,

Code Snippet

create table imported

(

id int,

desc1 varchar(36),

desc2 varchar(36)

);

Go

create table #T

(

id int,

longdesc varchar(1000)

)

BULK INSERT #T

FROM 'c:\flat\flat.txt'

WITH

(

FIELDTERMINATOR = ' ',

ROWTERMINATOR = '\n'

)

Insert Into imported

select

id,

cast(Substring(longdesc ,1,36) as char(36)),

cast(case when len(longdesc) >36 Then Substring(longdesc,37,36) Else '' End as char(36))

from

#T

drop table #T

select *, len(desc1+ '.')-1, len(desc2+ '.')-1 from imported

|||

The padding for the file is for a really old system that we use. We are looking to replace this soon. So we are converting from one old system to another. I worked out this solution.

SELECT TOP 500

CONVERT( char(8), rtrim( ltrim( bi.cusip))) + ',' + -- as cusip,

CASE WHEN LEN( bi.[Security Name]) <= 36

THEN CONVERT( char(36), bi.[Security Name])

ELSE CONVERT( char(36), LEFT( bi.[Security Name], 36))

END + ',' + -- AS 'Description1'

CASE WHEN LEN( bi.[Security Name]) > 36

THEN CONVERT( char(36), SUBSTRING( bi.[Security Name], 37, 36))

ELSE CONVERT( char(36), '')

END + ',' + -- AS [Description 2],

CONVERT( char(36), '') + ',' + -- [Description 3]

CONVERT( char(36), '') + ',' + -- [Description 4]

CONVERT( char(6), LEFT( RTRIM( LTRIM( REPLACE( bi.Ticker, '.', ''))), 6)) + ',' +

CONVERT( char(3), LEFT( s.[Classification Code], 3)) + ',' + -- as [ClASsification Code]

CONVERT( char(4), LEFT( RTRIM( LTRIM( s.[industry code])), 4)) + ',' + -- [industry code]

CONVERT( char(3), LEFT( RTRIM( LTRIM( s.[tax code])),3)) + ',' + --[TAX CODE]

CONVERT( char(1),LEFT( RTRIM( LTRIM('E')), 1)) + ',' +

CONVERT( char(1),LEFT( RTRIM( LTRIM( 'A')),1)) + ',' +

CONVERT( char(13),LEFT( LTRIM( RTRIM( ISNULL( bi.[Income Rate],''))),13))+ ',' +

CONVERT( char(8), ISNULL( REPLACE( CONVERT( varchar, bi.maturity, 101), '/', ''), '')) + ',' + -- Maturity Date

CONVERT( char(4), ISNULL( ifq.Sunguard, '')) + ',' + -- Income Frequency

CONVERT( char(4), '') + ',' + -- Quality Rating

CONVERT( char(1), '') -- Accrual Method

--INTO tblSentSecurties

FROM dbo.mytable bi

LEFT JOIN dbo.Sl2 s on s.cusip = bi.cusip

LEFT JOIN dbo.tblIFreq ifq

ON ifq.AxysFCDate = CONVERT( varchar(2), DATEPART( month, REPLACE( bi.maturity, '?', ''))) + '/' + CONVERT( varchar(2), DATEPART( day, REPLACE( bi.maturity, '?', '')))

AND ifq.AxysCF = REPLACE( bi.Freq, '?', '')

|||

Thank you I will give this a try and compare the performance. This needs to only be run once a day.

See above post to see my solution.

column Length Help

I have a table this is populated by a flat file. In the desc field from the flat file some of the data is very long up to 150 characters in length. I have to export this data after some transformations to a fixed width file. I need to check the length of the data in the desc field and any thing that is to long needs to be put in a desc2 field, however any values in desc that are shorter than 36 characters in length needs to have spaces padding it up to 36 characters as well as the desc2 field. Any help would be great. I am not the best T-SQL programmer in the world and have limited time to get this done.

well, I think you can just do (I might be missing something, of course....):

select cast(substring (desc,1,36) + replicate(36,'') as char(36)) as desc,

cast(substring (desc,37,114) + replicate(114,'') as char(114)) as desc2

from sourceTable

Not sure why you would want to pad the data, that is a tremendous waste of space, generally

|||

Here it is,

Code Snippet

create table imported

(

id int,

desc1 varchar(36),

desc2 varchar(36)

);

Go

create table #T

(

id int,

longdesc varchar(1000)

)

BULK INSERT #T

FROM 'c:\flat\flat.txt'

WITH

(

FIELDTERMINATOR = ' ',

ROWTERMINATOR = '\n'

)

Insert Into imported

select

id,

cast(Substring(longdesc ,1,36) as char(36)),

cast(case when len(longdesc) >36 Then Substring(longdesc,37,36) Else '' End as char(36))

from

#T

drop table #T

select *, len(desc1+ '.')-1, len(desc2+ '.')-1 from imported

|||

The padding for the file is for a really old system that we use. We are looking to replace this soon. So we are converting from one old system to another. I worked out this solution.

SELECT TOP 500

CONVERT( char(8), rtrim( ltrim( bi.cusip))) + ',' + -- as cusip,

CASE WHEN LEN( bi.[Security Name]) <= 36

THEN CONVERT( char(36), bi.[Security Name])

ELSE CONVERT( char(36), LEFT( bi.[Security Name], 36))

END + ',' + -- AS 'Description1'

CASE WHEN LEN( bi.[Security Name]) > 36

THEN CONVERT( char(36), SUBSTRING( bi.[Security Name], 37, 36))

ELSE CONVERT( char(36), '')

END + ',' + -- AS [Description 2],

CONVERT( char(36), '') + ',' + -- [Description 3]

CONVERT( char(36), '') + ',' + -- [Description 4]

CONVERT( char(6), LEFT( RTRIM( LTRIM( REPLACE( bi.Ticker, '.', ''))), 6)) + ',' +

CONVERT( char(3), LEFT( s.[Classification Code], 3)) + ',' + -- as [ClASsification Code]

CONVERT( char(4), LEFT( RTRIM( LTRIM( s.[industry code])), 4)) + ',' + -- [industry code]

CONVERT( char(3), LEFT( RTRIM( LTRIM( s.[tax code])),3)) + ',' + --[TAX CODE]

CONVERT( char(1),LEFT( RTRIM( LTRIM('E')), 1)) + ',' +

CONVERT( char(1),LEFT( RTRIM( LTRIM( 'A')),1)) + ',' +

CONVERT( char(13),LEFT( LTRIM( RTRIM( ISNULL( bi.[Income Rate],''))),13))+ ',' +

CONVERT( char(8), ISNULL( REPLACE( CONVERT( varchar, bi.maturity, 101), '/', ''), '')) + ',' + -- Maturity Date

CONVERT( char(4), ISNULL( ifq.Sunguard, '')) + ',' + -- Income Frequency

CONVERT( char(4), '') + ',' + -- Quality Rating

CONVERT( char(1), '') -- Accrual Method

--INTO tblSentSecurties

FROM dbo.mytable bi

LEFT JOIN dbo.Sl2 s on s.cusip = bi.cusip

LEFT JOIN dbo.tblIFreq ifq

ON ifq.AxysFCDate = CONVERT( varchar(2), DATEPART( month, REPLACE( bi.maturity, '?', ''))) + '/' + CONVERT( varchar(2), DATEPART( day, REPLACE( bi.maturity, '?', '')))

AND ifq.AxysCF = REPLACE( bi.Freq, '?', '')

|||

Thank you I will give this a try and compare the performance. This needs to only be run once a day.

See above post to see my solution.

Column Delimiter as a SPACE?

I'm in a flat file connection manager editor and I have a flat file where the columns are separated by a space. Does anyone know how to specify a space in the column delimiter option? I've tried {' '} and {\s} but these don't work. Not sure what the syntax is for indicating a space. Thanks in advanceJust hit the space bar in the column delimiter box. Make sure that the first row is delimited by a space (even if it's a header).

Collations and simple import from a csv-file

Hello. Here a two different problems that occur one and a while when I try to import a textfile to SQL Server 2005.

I have a flat file connection to a csv-file that originally is a export from an AS4000 DB2 database This csv-file is defined as a variable length file Why do SSIS automatically interpret the length of each column as varchar(50)? It does not matter if a define the same file as a fixed lenght file. The problem is that I will get a warning that information in columns will be truncated. I would like to do a direct export to the SQL Server 2005 table with shorter varchar fields. I can solve this by using the task for transforming data types but this only works on the text fields. This task cannot transform a string to a decimal or an integer column in the SQL Server 2005 table. Is there no other way than having a staging table between the text file and the SSIS-data pipe? I also get a lot of collation or code page errors even if we set the receiving columns to nvarchar and nchar. Is there any good article on this subject? Code page errors

Last question. Is there parameter support in the data reader source connection?

I appreciate all help.

Regards

Thomas Ivarsson

You can control the data types and lengths that SSIS uses for flat files by going to the Advanced area in the flat file connection manager. It will show you the columns defined, their data types, and their lengths, and you can alter the values to match what you want.

Parameter support in the data reader source: I don't believe that it does, but you can create the SQL that you want by building it in a variable, then assigning the variable to the Data Reader Source.SQL Command property using an expression.

|||

Thank's for the answere jwelch. I have actually tried the advanced properties and have set the flat file connection data types according to the target table data types.

Perhaps I have another error but this configuration will work?

I will have a look at your advice regarding the data reader source.

Regards

Thomas Ivarsson