floor function

I am trying to pull the number preceeding the decimal, but I want my output in a fixed lengh. Here is what I tried thinking it might work, however it did not.

sf_retail = right('000' + floor(cast(labsf.last_retail_price as varchar)),3),

the number I am running this against is '0000001.45' I would like my output to read '001'.....I am getting only '1'

Any suggestions?We just did something like that...

Check out...

http://www.dbforums.com/t987264.html|||Thanks, that was helpful. I ended up using:

sf_retail = right('000' + convert(varchar(3), floor(labsf.last_retail_price)),3),

Flight Recorder in SSAS

Hi -

I am trying to use fight recorder in analysis services. But when I replay a trace file I shows only very few columns in output.

Is there any way to see all the coulums when you replay the trace?Any kind of setting that I need to modify.

Thanks,

Deepak,

The events and columns captured by the Flight Recorder are controlled by a trace definition file. The advanced server property named Log\FlightRecorder\TraceDefinitionFile controls the definition file used by the Fligth Recorder. By default, the property is blank, which means the Flight Recorder defaults to using the trace file definition stored in the file flightrecordertracedef.xml, which is stored in the ..\OLAP\bin directory wherever SSAS is installed.

Patrice Truong does a nice job of explaining how to modify the trace definition file in the following blog entry:

http://patrice-truong.spaces.live.com/blog/cns!62BC7696F42B5!181.entry

One thing I'd likely recommend is saving the modified flightrecordertracedef.xml file as a new name, then update the server property listed above to reflect that new file name (to keep the original file in tact just in case you need to revert to it for some reason).

HTH,

Dave Fackler

sql

Flatfile output truncating after 255 characters

Hi,

iam bringing an output in flatfile which is truncating after 255 characters,some of the data is vanishing because of this.can anyone pls help.

pls advice.

Regards,

sg

Check the field definitions in the flat file connection.|||

Hi phil,

Iam running the combined query in Management studio and finding that the result is truncated.

some rows are exceeding 255 characters and the data after these 255 characters are getting truncated.

What should be done for this?

Regards,

sg.

|||

swan_sgp wrote:

Hi phil,

Iam running the combined query in Management studio and finding that the result is truncated.

some rows are exceeding 255 characters and the data after these 255 characters are getting truncated.

What should be done for this?

Regards,

sg.

I don't think the results are getting truncated. If you are expecting more characters, try exporting the results to a file and look there. It may be Management Studio's behavior to only show 255 characters in the result list.|||Also, look in Management Studio... Tools, Options, Query Results, SQL Server, Results to Grid and Results to Text. There are settings in each of those that specify how many characters are returned.|||

Hi phil,

I tried all the ways you have mentioned,you were correct that management studio is showing 255 characters and truncating all characters after that,

i ran the package,and some of the table-column's that were not shown in management studio are shown when a flatfile is generated but one table is not generating though,unable to solve even after recoding repeatedly.

only one table is not coming in flatfile fully.

pls advice.

Regards,

sg.

|||Redirect errors and inspect them. Or ensure that your flat file has been defined correctly. Embedded NULLs inside strings will terminate them, so for example:

If you have the string, "THIS STRING CONTAINS A [NULL] VALUE", the only thing that SSIS will see will be "THIS STRING CONTAINS A ".

Just a thought.

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 Source - Add Output Field

I am moving data from a flat file source to a SQL Server table. But I want to add a columm that IS in the destination table, but NOT in the source file. Say the table column name is XXX in destination table, and there will be a global variable called @.[User::XXX] that remains constant throughout the package. I would like to put the variable value into the destination column, even though the source file does not contain the field. Is there an easy way to do this?Add a derived column transformation between your source and destination. Then simply drop the User:XXX variable into it. That will add a new column to the data flow.|||Thanks! This was the solution. I actually came across that while trying to solve a problem or recalculating and input field while passing through.

Flat File Processing - Cannot get Error Output to Write

I cannot seem to get my flat file to write columns in error when inserting into a SQL table. I have tried a few examples from MS and did not get anything written to my flat file output. I have set the Source Error Output on this flat file and it uses a script task to created the error description and then write it to a Flat File Destination.

I am new to SSIS and have not had any formal training on it. However, I am very familiar with VS.Net/c# and SQL 2000 DTS - I need to get this working ASAP as there are 45 total flat files that need to be processed. Once I have this solved for one, the rest will follow suit.

If more details would help, I can provide them.

Kind Regards,

Ron

Yep, please provide more details. Any error messages? What doesn't work, exactly?|||I'm confused about what you're trying to do. Can you try the description again?
|||

Well, I have my flat file source and my SQL server destination. One flat file represents a table and the SQL destination represents one table. The columns are all mapped one-to-one exactly. I had to modify the flat file source data types to get rid of the data type errors. When I run the package, this task fails because of a datatype error. One of the datetime fields is not valid. The flat file that I am having troubles with has 145 columns and about 25000 rows. I have 45 total flat files I need to process. The one that I am having troubles with and would like to log the errors for, is the longest in terms of columns.

Currently, I have the Error Output going to a Script Component that has an output column for Error Description. Under this I have the Flat File Destination that I would like to write the error to. I have tested with a smaller flat file and I manually corrupted the file so it would fail, but this still did not write any error output.

I honestly did not think that what I was doing with SSIS was going to be this challenging, but I was wrong. The high-level overview of what I am ultimately trying to do is process 45 flat files into a SQL DB with 45 tables. The field names in both source and destination are the same. We would eventually like to add some process logging and other check points, but we have to get this proof of concept going first.

Hope this helps a bit more.

|||

ronemac wrote:

Well, I have my flat file source and my SQL server destination. One flat file represents a table and the SQL destination represents one table. The columns are all mapped one-to-one exactly. I had to modify the flat file source data types to get rid of the data type errors. When I run the package, this task fails because of a datatype error. One of the datetime fields is not valid. The flat file that I am having troubles with has 145 columns and about 25000 rows. I have 45 total flat files I need to process. The one that I am having troubles with and would like to log the errors for, is the longest in terms of columns.

Currently, I have the Error Output going to a Script Component that has an output column for Error Description. Under this I have the Flat File Destination that I would like to write the error to. I have tested with a smaller flat file and I manually corrupted the file so it would fail, but this still did not write any error output.

I honestly did not think that what I was doing with SSIS was going to be this challenging, but I was wrong. The high-level overview of what I am ultimately trying to do is process 45 flat files into a SQL DB with 45 tables. The field names in both source and destination are the same. We would eventually like to add some process logging and other check points, but we have to get this proof of concept going first.

Hope this helps a bit more.

In the error output area of the Source Adapter, have you made sure that Redirect Row is set on all the appropriate choices (Error and Truncation for each column)?

|||Yes sir.|||So what I have is this:
Flat File Source --> SQL Server Destination
After this fails (the SQL Server Destination is Red while the Flat File Source is green) I look at the Execution Results and I see that the SQL Server Destination had a conversion issue - (Error converting data type DBTYPE_DBTIMESTAMP to datetime)
Unfortunately, there are roughly 20 datetime columns in this table - 145 total columns - so I have no idea which column in which row is failing.
I would like to capture each row (row ID) and column that is failing so I can log it and reject the file. I have tried adding a script task and using the Error Output from the Flat File Source, but I don't even know if this is the correct place. I am sure the file structure looks just fine to SSIS, it's when SQL tries to insert the data that the DT conversion fails.
Suggestions?|||

Redirect errors from the SQL Server destination.

|||

That is what I WANT to do, but did not think that was possible - there is no Red arrow for error output on the SQL Destincation Data Flow Component... DId I mention that I was new to SSIS? Sorry.

|||

Ron,

Some stuff here that may help:

Get error descriptions

(http://blogs.conchango.com/jamiethomson/archive/2005/08/08/SSIS-Nugget_3A00_-Get-error-descriptions.aspx)

Enhanced Error Transform (adds the name of the column in which the error occurred):

(http://sqlblogcasts.com/blogs/simons/archive/2005/09/21/637.aspx)

-Jamie

|||

Yep, sorry, try using an OLE DB destination instead.

SQL Server destinations are an "in-memory" hook to SQL Server. It is a strongly typed destination and requires that all fields are the correct data type and constraints have been met, etc...

|||

Cool.. I will give it a rip. Thanks again.

|||Right on! And thank you for your help!

Flat File Processing - Cannot get Error Output to Write

I cannot seem to get my flat file to write columns in error when inserting into a SQL table. I have tried a few examples from MS and did not get anything written to my flat file output. I have set the Source Error Output on this flat file and it uses a script task to created the error description and then write it to a Flat File Destination.

I am new to SSIS and have not had any formal training on it. However, I am very familiar with VS.Net/c# and SQL 2000 DTS - I need to get this working ASAP as there are 45 total flat files that need to be processed. Once I have this solved for one, the rest will follow suit.

If more details would help, I can provide them.

Kind Regards,

Ron

Yep, please provide more details. Any error messages? What doesn't work, exactly?|||I'm confused about what you're trying to do. Can you try the description again?
|||

Well, I have my flat file source and my SQL server destination. One flat file represents a table and the SQL destination represents one table. The columns are all mapped one-to-one exactly. I had to modify the flat file source data types to get rid of the data type errors. When I run the package, this task fails because of a datatype error. One of the datetime fields is not valid. The flat file that I am having troubles with has 145 columns and about 25000 rows. I have 45 total flat files I need to process. The one that I am having troubles with and would like to log the errors for, is the longest in terms of columns.

Currently, I have the Error Output going to a Script Component that has an output column for Error Description. Under this I have the Flat File Destination that I would like to write the error to. I have tested with a smaller flat file and I manually corrupted the file so it would fail, but this still did not write any error output.

I honestly did not think that what I was doing with SSIS was going to be this challenging, but I was wrong. The high-level overview of what I am ultimately trying to do is process 45 flat files into a SQL DB with 45 tables. The field names in both source and destination are the same. We would eventually like to add some process logging and other check points, but we have to get this proof of concept going first.

Hope this helps a bit more.

|||

ronemac wrote:

Well, I have my flat file source and my SQL server destination. One flat file represents a table and the SQL destination represents one table. The columns are all mapped one-to-one exactly. I had to modify the flat file source data types to get rid of the data type errors. When I run the package, this task fails because of a datatype error. One of the datetime fields is not valid. The flat file that I am having troubles with has 145 columns and about 25000 rows. I have 45 total flat files I need to process. The one that I am having troubles with and would like to log the errors for, is the longest in terms of columns.

Currently, I have the Error Output going to a Script Component that has an output column for Error Description. Under this I have the Flat File Destination that I would like to write the error to. I have tested with a smaller flat file and I manually corrupted the file so it would fail, but this still did not write any error output.

I honestly did not think that what I was doing with SSIS was going to be this challenging, but I was wrong. The high-level overview of what I am ultimately trying to do is process 45 flat files into a SQL DB with 45 tables. The field names in both source and destination are the same. We would eventually like to add some process logging and other check points, but we have to get this proof of concept going first.

Hope this helps a bit more.

In the error output area of the Source Adapter, have you made sure that Redirect Row is set on all the appropriate choices (Error and Truncation for each column)?

|||Yes sir.|||So what I have is this:
Flat File Source --> SQL Server Destination
After this fails (the SQL Server Destination is Red while the Flat File Source is green) I look at the Execution Results and I see that the SQL Server Destination had a conversion issue - (Error converting data type DBTYPE_DBTIMESTAMP to datetime)
Unfortunately, there are roughly 20 datetime columns in this table - 145 total columns - so I have no idea which column in which row is failing.
I would like to capture each row (row ID) and column that is failing so I can log it and reject the file. I have tried adding a script task and using the Error Output from the Flat File Source, but I don't even know if this is the correct place. I am sure the file structure looks just fine to SSIS, it's when SQL tries to insert the data that the DT conversion fails.
Suggestions?|||

Redirect errors from the SQL Server destination.

|||

That is what I WANT to do, but did not think that was possible - there is no Red arrow for error output on the SQL Destincation Data Flow Component... DId I mention that I was new to SSIS? Sorry.

|||

Ron,

Some stuff here that may help:

Get error descriptions

(http://blogs.conchango.com/jamiethomson/archive/2005/08/08/SSIS-Nugget_3A00_-Get-error-descriptions.aspx)

Enhanced Error Transform (adds the name of the column in which the error occurred):

(http://sqlblogcasts.com/blogs/simons/archive/2005/09/21/637.aspx)

-Jamie

|||

Yep, sorry, try using an OLE DB destination instead.

SQL Server destinations are an "in-memory" hook to SQL Server. It is a strongly typed destination and requires that all fields are the correct data type and constraints have been met, etc...

|||

Cool.. I will give it a rip. Thanks again.

|||Right on! And thank you for your help!sql

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.