flexible column width based on data length in a matrix

I would like my matrix to have the column widths be the same as the maximum
length of data in that column, just like what we like to see when we are
looking at data in Excel. I do not want the data to wrap, I want it to push
the width of the column to the right so all data is on one line.
How do I do that?
StephanieOn Aug 22, 11:12 am, Stephanie <Stepha...@.discussions.microsoft.com>
wrote:
> I would like my matrix to have the column widths be the same as the maximum
> length of data in that column, just like what we like to see when we are
> looking at data in Excel. I do not want the data to wrap, I want it to push
> the width of the column to the right so all data is on one line.
> How do I do that?
> Stephanie
If I'm understanding you correctly, you should be able to select the
table/matrix cell and select F4 for the Properties window and then set
'Can Grow' below Layout to 'True.' Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||My understanding of the 'Can Grow' property is that it will allow it to grow
vertically, not horizontally. Is that correct? I've tried 'Can Grow' with
no luck.
"EMartinez" wrote:
> On Aug 22, 11:12 am, Stephanie <Stepha...@.discussions.microsoft.com>
> wrote:
> > I would like my matrix to have the column widths be the same as the maximum
> > length of data in that column, just like what we like to see when we are
> > looking at data in Excel. I do not want the data to wrap, I want it to push
> > the width of the column to the right so all data is on one line.
> >
> > How do I do that?
> >
> > Stephanie
>
> If I'm understanding you correctly, you should be able to select the
> table/matrix cell and select F4 for the Properties window and then set
> 'Can Grow' below Layout to 'True.' Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Aug 28, 9:32 am, Stephanie <Stepha...@.discussions.microsoft.com>
wrote:
> My understanding of the 'Can Grow' property is that it will allow it to grow
> vertically, not horizontally. Is that correct? I've tried 'Can Grow' with
> no luck.
> "EMartinez" wrote:
> > On Aug 22, 11:12 am, Stephanie <Stepha...@.discussions.microsoft.com>
> > wrote:
> > > I would like my matrix to have the column widths be the same as the maximum
> > > length of data in that column, just like what we like to see when we are
> > > looking at data in Excel. I do not want the data to wrap, I want it to push
> > > the width of the column to the right so all data is on one line.
> > > How do I do that?
> > > Stephanie
> > If I'm understanding you correctly, you should be able to select the
> > table/matrix cell and select F4 for the Properties window and then set
> > 'Can Grow' below Layout to 'True.' Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
Unfortunately, the Size: Width Property cannot be modified as part of
a table cell, etc. One other thing to consider is to verify that you
don't have merged cells when exporting to Excel. This could be do to
using table controls over each other (i.e., one on the top of the
report and one on the bottom) that have different widths or have cells
of different widths. For the most part, you will want all table/matrix
controls to all have the exact same physical dimensions amongst cells
and overall. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Flat Files Containing Dates

Hi everyone.
I'm trying to use a Flat File Connector to read in a fixed field width file that contains some date columns.
The problem is that the date column is in a CCYYMMDD format (with no delimiters) so that todays date, as an example, would be 20050711.
When it attempts to import the file it fails due to a "Data Conversion Failed" error. I can't find any way to specify the format of the column in the FFC dialog so my only option appears to be read in the column as a string and transform it later.
Is that correct?
Steve
Steve,
It sounds like it is, yes. Your other option is to write a custom connection manager and source component but that's like using a sledgehammer to crack a nut.

-Jamie|||Thanks Jamie, that's just what I was expecting.
Steve
|||

Jamie Thomson wrote:

Steve,
It sounds like it is, yes. Your other option is to write a custom connection manager and source component but that's like using a sledgehammer to crack a nut.

-Jamie

Or you could also use a script component as a source. Again, it may be overkill!

-Jamie|||Looks like ISO 8601 sans the '-' character. You can write a simple derived column expression to parse this out and convert it to a date. Like you say, just retrieve it as a string the new column will be a date.

Here's one way to do it:

(DT_DATE)(SUBSTRING(Date,6,2) + "-" + SUBSTRING(Date,8,2) + "-" + SUBSTRING(Date,1,5))

That will convert a string date column like this:

Date Derived Column 1 20050112 1/12/05 20031122 11/22/03 20050509 5/9/05 20010101 1/1/01 20000301 3/1/00 20021003 10/3/02 20022002 2/20/02 19631003 10/3/63 19621002 10/2/62 20051111 11/11/05

|||Thanks for those replies guys.
I'd like to create a derived column transform programmatically using the SSIS object model. I can't find any help in BOL regarding this - but I've managed to get this so far, which creates the derived column transformation object (the dataFlow object is a MainPipe object created elsewhere):

DTSComponentMetaData90 DerivedColumn;
DerivedColumn = dataFlow.ComponentMetaDataCollection.New();
DerivedColumn.Name = "DateTransform";
DerivedColumn.ComponentClassID = "DTSTransform.DerivedColumn.1";
CManagedComponentWrapper instance = DerivedColumn.Instantiate();
instance.ProvideComponentProperties();
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();

The problem I have now is that I don't know how to create new columns from old columns ( as I will need to do in my case ). I have used other components which have mapped the virtual columns from the input to the output, so I'm assuming it's something similar, but I can't get it to work.
I've even tried creating a transform in the BIDS and then opening the package in code to see what the object looks like, but some of the properties were read-only and must be set another way. I'm really stuck now so any help would be really appreciated.
Thanks.
Steve
|||Steve,

To create a new column from an existing column you need to add an output column to the derived column transform (InsertOutputColumAt) and then set the FriendlyExpression (or Expression) custom property on that column (SetOutputColumnProperty). The FriendlyExpression would be something like LEFT([oldcolname], 5) to take the left 5 chars of the [oldcolname] column (assuming the oldcolname column was a string or wstring). You could use the expression property but it isn't as obvious and you need to get the existing column's lineage id (e.g. LEFT(#27, 5) if 27 was oldcolname's lineageid). Additionally, you have to set the virtual input column's usage type (IDTSDesigntimeComponent90::SetUsageType) to read only to tell the dataflow that this component needs to use this column for reading.

HTH,|||I tried this but got following error:

Derived Column [2497]: An error occurred while attempting to perform a type cast.
thanks,
Nitesh Ambastha
nitesh.ambastha@.csfb.com

|||

KirkHaselden wrote:

Looks like ISO 8601 sans the '-' character. You can write a simple derived column expression to parse this out and convert it to a date. Like you say, just retrieve it as a string the new column will be a date.

Here's one way to do it:

(DT_DATE)(SUBSTRING(Date,6,2) + "-" + SUBSTRING(Date,8,2) + "-" + SUBSTRING(Date,1,5))

That will convert a string date column like this:

Date Derived Column 1 20050112 1/12/05 20031122 11/22/03 20050509 5/9/05 20010101 1/1/01 20000301 3/1/00 20021003 10/3/02 20022002 2/20/02 19631003 10/3/63 19621002 10/2/62 20051111 11/11/05

To be more specific, I used the above idea and wrote this expression:
(DT_DATE)(SUBSTRING((YYYYMM + "01"),6,2) + "-" + SUBSTRING((YYYYMM + "01"),8,2) + "-" + SUBSTRING((YYYYMM + "01"),1,5))

This throws a cast exception.
Any suggestions?

thanks,
Nitesh Ambastha
nitesh.ambastha@.csfb.com|||May be the cast error is due to the fact that input YYYYMM can be null or empty string. Can someone suggest a better expression? Or I have to write a script?|||

What do you mean when you say it "throws a cast exception"?

Have you tried entering this expression in the derived column UI to see if it gives an error message?

If you think the input column might be null or empty, you could check that with ISNULL() or LEN() calls first using a conditional operator.

sql

flat file source and destination - need fixed width output

I have a text file that is comma delimited and im pulling it in with a flatfile connection manager. I want to read some of the data, then output another flat file but in a fixed column width. What settings do I made to the connection manager of the output flatfile ?Choose a fixed-width format when setting up the destination. And then define your columns.|||

Hi

A solution is:

- Go to advanced properties of output flat file.

- Define the fields as text and the property OutputColumnWith with the size you want

- Export your data to that file and convert it to text.

Raul

|||I had some other steps in the middle, so I took them out just to simplify things. In the connection mgr for my destination, it wants to know the input column widths. Should I really need to bother with this, since in the end, I just want whatever output i get during any previous steps, to simply be output to the flat file as fixed width ?|||Ok, ive managed to get my output in fixed width in the output file, but it appears the lines arent terminating where they should be. What controls where the lines terminate ? I dont have a header row (no column names in the first row), so what, if anything should the "header row" settings be set to ?|||Ive got a data file with values seperated with commas. I want to read in this text file, do a lookup and add the lookup column on the front of the other columns and save the output in a fixed width format, similar to what you would get if you saved the query reqults to a file in sql mgt studio.

What I have so far is just a flat file source , a lookup and a flat file destination.

I can get the output to generate, but there doesnt seem to be any row terminating, its all one big string.

help ?|||Use "Fixed width with Row Delimiters" option for the flat file connection manager.|||

Eric Wisdahl wrote:

Use "Fixed width with Row Delimiters" option for the flat file connection manager.

Where is that option available ? Under the general tab on my flat file conn mgr, I have only the options:
"fixed width"
"delimited"
"ragged right"

If I have fixed width selected, and go to the advanced tab, start entering my own columns, the column delimiter option is greyed out.|||When you are first creating the flat file connection manager it gives you the option of delimited, fixed width, fixed width with row delimiters, ragged right. All that fixed width with row delimiters does is add another column to each record which contains the row delimiter. You can accomplish the same thing by adding it in with the derived column and adding it to the end of your output record.

Flat File Records Dropped During Import

Hello,
I am attempting to import a fixed width flat file into a SQL Server table. When I import the file, 704 records don't make it into the table. I know this because if I do the import with MS Access 2003 into an Access table, all of the records from the flat file make it into the table. The flat files have a .txt extension.

The only possible problem that I can see is that some of the rows in the flat file do not contain the full set of characters. When I do the import into SQL Server and create a table on the fly, I still end up 704 records short. There are no error messages during or after the import.

I suppose I could isolate some of the missing records, put them into a different file and try to import them to see what would happen. Other than that, how do I begin to troubleshoot this problem? Are there known issues where records can be dropped from a fixed width file?

Thank you for your help!

cdun2I may have found the problem. The first record does not contain a full set of characters, and when I set up the fixed field column positions originally, I was not able to define the columns for the full string. Only the first one third of the row characters need to be imported, so I ignored this issue.

I have moved a single full length record to the top of the flat file, and the text file properties box now can 'see' the full string. I'll include the rest of the columns (which are not imported) and see if that works.|||No, that didn't work. I moved a record containing all characters to the top of the file, redefined the columns based on the full string, changed the column mappings, and reconfigured the transformations. When I did the import, the destination SQL Server table received even fewer records.

What can I do about this problem?

Thanks again.

cdun2|||If this is a one time only situation, why don't you just import the flat file into Access 2003 and then import the Access table to SQL server?|||What are you using to import the data? DTS? BCP?sql

Flat File import question

I have a fixed width flat file that I'm trying to import, and I'm just about there. The last column that I'm struggling with, is a decimal amount. The data in the column looks like this 00000000500 and I need to dump it into a column as 5.000 In otherwords, the data in the file does not have any decimals, and I'm putting it into a sql server column that has the datatype numeric(11,4) I've set the InputColumnWidth to 11, the DataPrecision to 11 and the datascale to 2, and the value is still being imported as 500.000 Is there any way to achieve this other than using a script component to calculate the value? Thanks!

tee bone wrote:

I have a fixed width flat file that I'm trying to import, and I'm just about there. The last column that I'm struggling with, is a decimal amount. The data in the column looks like this 00000000500 and I need to dump it into a column as 5.000 In otherwords, the data in the file does not have any decimals, and I'm putting it into a sql server column that has the datatype numeric(11,4) I've set the InputColumnWidth to 11, the DataPrecision to 11 and the datascale to 2, and the value is still being imported as 500.000 Is there any way to achieve this other than using a script component to calculate the value? Thanks!

Add a derived column to take that value and divide it by 100 (or 1000, if you need)|||Thanks for the quick reply, so in other words there's no actual way to handle this situation in the connection manager itself? The solution that you have proposed will work, I was just trying to cut down on the time it takes for the package to process. We have to load a daily file that's about 300mb, so it takes a long time to process. I've cut it down to under a minute, but I was afraid adding another step in the flow would add quite a bit of time. I'm pretty new to SSIS, so please let me know if I'm worrying about it for no reason. Thanks!
|||On a side note, I'd go try this out and test it out for myself, but I'm going to have to apply this derived column transformation on about 150 columns, so I'm trying to get a good idea of what to expect before I get started
|||Yeah, the connection manager just reads the data as it is. What it currently sees (without a decimal point) is an integer. It is what it is. Either fix it in the source, or use a derived column. Sucks, yes, but that's the way it is.|||Sounds good, thanks for the help!

Fixed Width Text Report

I'm trying to create a fixed width text report. I've created a function that
takes a string and a length and returns the string either truncated or padded
with spaces to the given length.
I then use url parameters to modify the CSV device information settings to
change the encoding to ascii, change the extension to txt and change the
FieldDelimiter to %1f (unicode symbol for some kind of field grouping or
something).
Things seems to properly but I don't like having to set the FieldDelimiter
to anything. I tried setting it to null by saying isnull=true but that
generates an error about referencing a null object. I read something that
said to make it an empty string but I can seem to be able to do that using
url parameters. I could try it programmatically but I would prefer using the
url.
Does anyone have any ideas?(The parent post is mine, I just changed my login)
I decided that setting the FieldDelimiter to %1f was not a good idea. I
did try to set the parameter to an empty string programmatically but it
just went to the default comma delimiter.
I've decided to just plug in the url encoded value of which stands
for a null ascii character. I don't know if this is the best solution
but I am going with it. Here is my final url:
http://localhost/ReportServer?/Devel/TestFile&rs:Format=CSV&rs:Command=Render&rc:Extension=txt&rc:NoHeader=true&rc:FieldDelimiter=&rc:Encoding=ascii
If anyone else comes up with any other ideas of how to use RS to create
fixed width text files I would be glad to hear them. I can't find
anything that explains a way of doing this. This would work perfect if
I could set the FieldDemlimiter parameter to nothing but it keeps going
to the default comma.
Thanks.
Gary

Fixed width output problem

I'm sending the results of an SSIS data flow to an fixed-width flat file output, but instead of getting separate rows of data, like so:

row1data...
row2data...
row3data...
etc...

I get:

row1data...row2data...row3data...etc...

Is there some setting I'm missing in either the flat file output or the file connection to turn this on?

The fixed width format does not include the row delimiters.

You can use the Ragged right format if your last column always has a fixed format or you do not care for it to be fixed.

If you do have a requirement for the fixed length of the last column (like you always want integer values to take 10 characters in the file), you can create your Flat File Connection manager by clicking "New..." on the Flat File Destination UI and choose the "Fixed width with row delimiters" option. That will actually create the ragged right file with a dummy row delimiter column.

HTH.

|||Thank you. That's what I needed.|||Thanks so much for this. I wanted SSIS to mimic the fixed width behavior of DTS or SQL Server 2000 export wizard. This was exactly what I was looking for.