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 Output PDF files

Is there a way to output reports to the same PDF file. Basicly Appending several reports to the one PDF.

Without combining the reports using subreports, I think you would use code & custom PDF components, unless you want to jump into writing a rendering extension.

Softartisians Officewriter & abcPDF.NET are two possible options. You can also try automating Acrobat.

http://www.planetpdf.com/forumarchive/84747.asp

One alternative may be to export to a different format and then convert to pdf afterwards.

cheers,

Andrew

|||

we're trying to do this on the report server with out having to add another process.

Is there any good tutorials for writing a rendering extension? I've heard its difficult.

|||

Not sure if it has changed since RS 2000, but here is a comment on how difficult...

http://blogs.msdn.com/bryanke/archive/2004/03/16/90797.aspx

If you want to develop one, it might help to travel to Microsoft's main campus and stay for a month or two while you work alongside Reporting Services developers.

cheers,

Andrew

|||

LOL, Yeah I saw that, showed it to my boss. He said no to funding that trip..Oh well...LOL

Doubt they would let me in anyways

But hey if Microsoft wants to use me as a test case, I'm all for it!!! {WINK WINK, NUDGE NUDGE}

sqlsql

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!

combine separate date & time fields into one datetime field?

Good morning.

I am importing an XLS file into one of my tables. The fields are:

Date Id Time IO

12/22/2006

2

12:48:45 PM

9

12/22/2006

16

5:40:55 AM

1

12/22/2006

16

12:03:59 PM

2

When I do the import, I get the following:

Date Id Time IO
12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 2
12/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 1
12/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2

Here are my doubts:

1. Is it be better to combine the Date & Time fields into one column? Advantages/Disadvantages?
2. If I don't combine them, should I use varchar or datetime data type?
2. What issues or problems might I have when I program SQL reports, if I leave the fields as they are?

Any comments or suggestions will be very much welcomed.

Cheers mates.I was suggested to try this out:

UPDATE tbl
SET Date = Date + convert(char(8), Time, 108)

I'll run it after I use DTwizard to export the data into my table. I should also mention I have no PKs defined, just a FK that references Id from a table called Employees. I'm thinking it's best to define ID and Date and Time as PKs.|||

As far as SQL Server is concerned, it will be far easier over time to work with and deal with date/time data if it is stored as one column. I recommend combining the two.

One primary reason is that the combined column will allow easier datetime comparisions and searches.

combine separate date & time fields into one datetime field?

Good morning.

I am importing an XLS file into one of my tables. The fields are:

Date Id Time IO
12/22/2006 2 12:48:45 PM 9
12/22/2006 16 5:40:55 AM 1
12/22/2006 16 12:03:59 PM 2

When I do the import, I get the following:

Date Id Time IO
12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 2
12/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 1
12/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2

Here are my doubts:

1. Would it be better to combine the Date & Time fields into one
column? If so, how?
2. What issues or problems might I have when I program SQL reports, if
I leave the fields as they are?

Any comments or suggestions will be very much welcomed.

Cheers mates.drurjen (jfontecha@.gmail.com) writes:

Quote:

Originally Posted by

Good morning.
>
I am importing an XLS file into one of my tables. The fields are:
>
Date Id Time IO
12/22/2006 2 12:48:45 PM 9
12/22/2006 16 5:40:55 AM 1
12/22/2006 16 12:03:59 PM 2
>
When I do the import, I get the following:
>
Date Id Time IO
12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 2
12/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 1
12/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2
>
Here are my doubts:
>
1. Would it be better to combine the Date & Time fields into one
column? If so, how?

Most probably. (In the end it depends on business needs, which I don't
anything about.)

A way to merge the columns would be:

UPDATE tbl
SET Date = Date + convert(char(8), Time, 108)

Quote:

Originally Posted by

2. What issues or problems might I have when I program SQL reports, if
I leave the fields as they are?

That you get 1899-12-30 printed all over the place, which you probably
don't want to. So you will need a lot of code to filter the date away.

--
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|||Erland,

Thank you for replying. Basically the DB is for employee time
attendance records. I start out with a flat txt file and run that
through an Excel macro that:

a) eliminates repeat entries in a time lapse of 5min
b) erases null entries.

I then take the XLS file and use DTWizard to export it into a table
with the same fields as before: Date, Id, Time, IO. I have no primary
keys defined in this table, just a FK (Id). I believe the primary keys
should be ID, Date & Time.

I'll try your suggestion. Thx again, and sorry for the repeat post.

Combine multiple RDL files into one RDL file

Hello,

I need to generate a report, which should display 4 reports. Two tables and some charts. I have all these reports (I mean the .RDL files) individually. I can render the reports separately. But, now the need is to combine these reports in the one RDL file. Is this possible? If yes, how?

Also, I tried to create a stored procedure, which would call all these 4 SP inturn and provide 4 result sets. I thought of have an RDL by calling only this SP which would give 4 result sets. But infortunately, it gave only the first SP's result set. So, I have to combine the 4 RDL files into one to show on the Reporting Console. Can anyone please help me in this? Help would be grately appreciated.

Thanks a lot. Let me know if the question is not clear.

Mannu.

Well let's see.

You would need to create 4 datasets, one for each of the original reports. You can do this on the data tab by clicking the dataset drop down and Add new dataset.

Then, copy and paste each of the individual layouts into this "super" report.

On each of the tables and charts, go to the properties for the table or chart and select the appropriate dataset as the "dataset name" that you created.

|||

Yes GregSQL gives you right approach for the problem but there is a catch.

If you create 4 datasets using same SP that will still give you the first resultset. To overcome this issue I believe you have to pass an extra parameter say ReportType with datatype as Char. Give a condition in SP to return the Dataset you want to return for that perticular ReportType.

IN StoredProcedure:

IF @.ReportType = 'A'

BEGIN

YOUR DATASET

END

IF @.ReportType = 'B'

BEGIN

YOUR DATASET

END

Comming to report , click on each Dataset and go to Parameters section and give the ReportType value for each dataresult.'A' for First Dataset, 'B' for Second Datset.

I think this will complete the whole scenario...

Hope this helps

columns in different filegroups

hi, is posible in sql server 2000 or 2005 asign a column to a filegroup
or file and other column of the same table to another filegroup or
file?... or i need to create two tables? thanks
The short answer is to use two tab les. The long answer is that you can
have text/image columns in a separate filegroup.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1133290695.567297.250330@.g47g2000cwa.googlegr oups.com...
> hi, is posible in sql server 2000 or 2005 asign a column to a filegroup
> or file and other column of the same table to another filegroup or
> file?... or i need to create two tables? thanks
>
|||ok, thanks

columns in different filegroups

hi, is posible in sql server 2000 or 2005 asign a column to a filegroup
or file and other column of the same table to another filegroup or
file?... or i need to create two tables? thanksThe short answer is to use two tab les. The long answer is that you can
have text/image columns in a separate filegroup.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1133290695.567297.250330@.g47g2000cwa.googlegroups.com...
> hi, is posible in sql server 2000 or 2005 asign a column to a filegroup
> or file and other column of the same table to another filegroup or
> file?... or i need to create two tables? thanks
>|||ok, thanks

columns in different filegroups

hi, is posible in sql server 2000 or 2005 asign a column to a filegroup
or file and other column of the same table to another filegroup or
file?... or i need to create two tables? thanksThe short answer is to use two tab les. The long answer is that you can
have text/image columns in a separate filegroup.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1133290695.567297.250330@.g47g2000cwa.googlegroups.com...
> hi, is posible in sql server 2000 or 2005 asign a column to a filegroup
> or file and other column of the same table to another filegroup or
> file?... or i need to create two tables? thanks
>|||ok, thanks

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

ColumnNamesInFirstDataRow Expression

I have a SSIS package I am trying to create that will accept two types of files. They are the same exact file except for one contains a header and one does not. So I setup a conneciton manager to the csv file. I then set a variable of bool type, and then assigned that to the ColumNamesInFirstDataRow expressions property of the conneciton manager.

So the pacakge runs. Loops a directory, runs a script that sets the Header variable to true/false based on the file name. But when it gets to the data flow it always ignores the property and never bypasses the header row. The variable is being set. I tried datarowstoskip and set it to 1 instead of the above mentioned property, and that does not work either.

How can I accomplish this?

There is a known bug in evaluation of flat file connection properties that might be causing this. IT is scheduled to be fixed for the next release. Unfortunately, I do not see an easy workaround...

-Bob

|||Is there any know work around to this with .net code or anything else?|||

You could set up two connection managers (one with headers, one without) and two dataflows to match, and use a script task to check the file for a header row. Then use precedence constraints to pick the data flow to execute.

Or you could parse the whole file in a script source, but that could be a lot of work, depending on the complexity of your file.

ColumnNamesInFirstDataRow Expression

I have a SSIS package I am trying to create that will accept two types of files. They are the same exact file except for one contains a header and one does not. So I setup a conneciton manager to the csv file. I then set a variable of bool type, and then assigned that to the ColumNamesInFirstDataRow expressions property of the conneciton manager.

So the pacakge runs. Loops a directory, runs a script that sets the Header variable to true/false based on the file name. But when it gets to the data flow it always ignores the property and never bypasses the header row. The variable is being set. I tried datarowstoskip and set it to 1 instead of the above mentioned property, and that does not work either.

How can I accomplish this?

There is a known bug in evaluation of flat file connection properties that might be causing this. IT is scheduled to be fixed for the next release. Unfortunately, I do not see an easy workaround...

-Bob

|||Is there any know work around to this with .net code or anything else?|||

You could set up two connection managers (one with headers, one without) and two dataflows to match, and use a script task to check the file for a header row. Then use precedence constraints to pick the data flow to execute.

Or you could parse the whole file in a script source, but that could be a lot of work, depending on the complexity of your file.

Column-conscious bulk insert

I am trying to bulk insert a text file. The file has fixed-length fields
with no field terminators. BOL says that field terminators are only
needed when the data does *not* contain fixed-length fields, which
implies they are optional -- so I made a format file without any (two
consecutive tabs with nothing between them). The following message
resulted:

Server: Msg 4827, Level 16, State 1, Line 1
Could not bulk insert. Invalid column terminator for column number
1 in format file

That sounds like I am required to have some sort of terminator in the
format file, even though there aren't any in the data file. Unfortunately,
the documentation on bcp/bulk copy and format files does not directly
address this point, and I would appreciate some help.

BTW, putting '""' (empty string) for the terminator also leads to errors,
with the first field overflowing -- bulk insert can't figure out where
it ends.

Thanks,
Jim Geissman
Countrywide Home Loansjim_geissman@.countrywide.com (Jim Geissman) wrote in message news:<b84bf9dc.0401281622.34aa0e42@.posting.google.com>...
> I am trying to bulk insert a text file. The file has fixed-length fields
> with no field terminators. BOL says that field terminators are only
> needed when the data does *not* contain fixed-length fields, which
> implies they are optional -- so I made a format file without any (two
> consecutive tabs with nothing between them). The following message
> resulted:
> Server: Msg 4827, Level 16, State 1, Line 1
> Could not bulk insert. Invalid column terminator for column number
> 1 in format file
> That sounds like I am required to have some sort of terminator in the
> format file, even though there aren't any in the data file. Unfortunately,
> the documentation on bcp/bulk copy and format files does not directly
> address this point, and I would appreciate some help.
> BTW, putting '""' (empty string) for the terminator also leads to errors,
> with the first field overflowing -- bulk insert can't figure out where
> it ends.
> Thanks,
> Jim Geissman
> Countrywide Home Loans

Jim,

Just a thought, but have you tried using the "-c" flag with the BCP IN
command instead of using a format file? Create a target table where
the column widths exactly match the fields in your file, and give it a
try. ("-c" takes no parameters). Assuming you've got record
terminators in the correct place, I think this should work.
Personally, I hate using format files and avoid them like the plague
if I can.

bcp <db>..<target_tbl> in <datafile> -Uuser -Ppass -Sserver -c

Phil|||Thanks, Phil.

I wish that were true. However it seems that -c assumes \t (tab)
separators. At least it doesn't work. Putting in -t (specify separator
but don't provide one) causes bcp to just sit there and do nothing.
I'm going to use DTS and specify column by column where they all end.
It's such a waste of effort, though, because the data is from the Census
and the input exactly matches the table, character by character.

Thanks again
Jim

> Jim,
> Just a thought, but have you tried using the "-c" flag with the BCP IN
> command instead of using a format file? Create a target table where
> the column widths exactly match the fields in your file, and give it a
> try. ("-c" takes no parameters). Assuming you've got record
> terminators in the correct place, I think this should work.
> Personally, I hate using format files and avoid them like the plague
> if I can.
> bcp <db>..<target_tbl> in <datafile> -Uuser -Ppass -Sserver -c
> Phil|||Jim Geissman (jim_geissman@.countrywide.com) writes:
> I am trying to bulk insert a text file. The file has fixed-length fields
> with no field terminators. BOL says that field terminators are only
> needed when the data does *not* contain fixed-length fields, which
> implies they are optional -- so I made a format file without any (two
> consecutive tabs with nothing between them). The following message
> resulted:
> Server: Msg 4827, Level 16, State 1, Line 1
> Could not bulk insert. Invalid column terminator for column number
> 1 in format file
> That sounds like I am required to have some sort of terminator in the
> format file, even though there aren't any in the data file.
> Unfortunately, the documentation on bcp/bulk copy and format files does
> not directly address this point, and I would appreciate some help.

You must specify the separator in quotes, but it can be the empty
string, "". The tabs does not mean anything to BCP, as far as I know.
At least it never complain about lack of tabs in my format files.

>BTW, putting '""' (empty string) for the terminator also leads to errors,
>with the first field overflowing -- bulk insert can't figure out where
>it ends.

What about posting:

o CREATE TABLE statement for your table.
o The format file. (The one with "" in it.)
o A sample file to bulk-load.

That makes it a little easier to have a guess of what is going on.

If the data file is more than 75 characters wide, you are probably
better of putting it an attachment.

--
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 widths are changing when deploying report

I designed a report in VS.Net 2003. It was actually an existing RDL file
that I just modified for my new report, adding some columns and doing various
formatting things.
In VS when I look at the report design, I have the report at 8.5 in high by
11 in wide with .25 in margins all around. I have 2 columns on the right
side of the table that, when deployed and viewed in PDF, are wider than the
width I defined for the column. For all cells in the columns Can Grow is
false. I have tried moving things all over the place but it won't affect
these two columns, they always stay as wide as they are each time.CanGrow only applies to vertical sizes, not horizontal sizes. The only
things that will grow horizontally are matrices and sized images. Do you
have any images?
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"AdamB" <AdamB@.discussions.microsoft.com> wrote in message
news:1E9C6385-B0D0-44DC-95D8-B0317A3F07A5@.microsoft.com...
>I designed a report in VS.Net 2003. It was actually an existing RDL file
> that I just modified for my new report, adding some columns and doing
> various
> formatting things.
> In VS when I look at the report design, I have the report at 8.5 in high
> by
> 11 in wide with .25 in margins all around. I have 2 columns on the right
> side of the table that, when deployed and viewed in PDF, are wider than
> the
> width I defined for the column. For all cells in the columns Can Grow is
> false. I have tried moving things all over the place but it won't affect
> these two columns, they always stay as wide as they are each time.|||No, but I seem to have resolved the problem by deleting then recreating the
columns
"Brian Welcker [MS]" wrote:
> CanGrow only applies to vertical sizes, not horizontal sizes. The only
> things that will grow horizontally are matrices and sized images. Do you
> have any images?
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "AdamB" <AdamB@.discussions.microsoft.com> wrote in message
> news:1E9C6385-B0D0-44DC-95D8-B0317A3F07A5@.microsoft.com...
> >I designed a report in VS.Net 2003. It was actually an existing RDL file
> > that I just modified for my new report, adding some columns and doing
> > various
> > formatting things.
> >
> > In VS when I look at the report design, I have the report at 8.5 in high
> > by
> > 11 in wide with .25 in margins all around. I have 2 columns on the right
> > side of the table that, when deployed and viewed in PDF, are wider than
> > the
> > width I defined for the column. For all cells in the columns Can Grow is
> > false. I have tried moving things all over the place but it won't affect
> > these two columns, they always stay as wide as they are each time.
>
>

Column Row Delimeter Problem

I'm trying to import a comma delimited text file into a SQL table-

The row delimiters I am assuming are {CR}{LF}

When I try to open up my file in Excel, the data file parses perfectly.

When I try to port it over in SSIS, I get an error:

"The column delimeter for column <my column> was not found."

"An error occurred while processing the file <my file> on data row 2076"

I've tried looking at that data row, and i am having a hard time finding anything wrong with the row.

Anybody know of any good ways to debug that?

n/m- I figured out my problem- the data was buggy- it had dual double-quotations- yet the double-quotations are what signified text qualifiers- and SSIS was not correctly picking up the text qualifiers correctly.

How do you get SSIS to understand quotes if the text qualifier is a quote?

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 Heading in Output File

Hi
I am running a SQL query and printing the output to a file, as well as
showing it in the Grid. The Grid has column headings, my file does not. Is
there a way of getting my column headings into the output file?
To create the output file I am using the following;
EXEC master..xp_cmdshell 'bcp "DATABASE.dbo.FILE" out
"C:\SQLSCRIPT\OUTPUT.txt" -c -t, -q -Sserver -U"username" -P"password"'
GO
Any help appreciated.
SteveBCP will not export the headings. You can use OSQL to do that.
Rand
This posting is provided "as is" with no warranties and confers no rights.

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).