Problem writing to fixed width text file destination

I am trying to export data from a query in SQL Server 2005 SSIS to a flat file destination. Everything works fine except the rows returned from my query are written to the flat file in one long string (i.e., without line breaks). I have tried appending a new line character to the rows returned from the query but that only throws an error when the package is executed. My rows returned from the query are 133 characters wide (essentially only one column per row) so I have set the properties accordingly for a fixed width file format with 133 character wide rows.

Any suggestions or ideas on how to correct this would be greatly appreciated.

Thank you,

Michael

What viewer are you using when looking at the text file? Are you sure you don't have newlines in the files? Look in notepad and again in word and see if that helps.|||

The Flat File Connection manager has a Format property, and I guess you have selected "Fixed Width", but strictly speaking this format does not include row delimiters. What you actually need is Ragged Right.

The best way to do this is to open your Destination, and click the New connection button. Now read the options carefull, as most people probably select #2, as it says Fixed Width, but #3 is what you want, Fixed Width with Row Delimiters. This builds the appropriate connection using Ragged Right, giving you what you want.

Problem with way to access all recordsets returned by sp_spaceused using ODBC APIs on C++

Hi all,
I have a problem executing a sp_spaceused on a database.
Please execute sp_spaceused on your local database so that further
discussion will be more easier to understand.
The code snippet is as follows :
SQLCHAR database_size[15];
SQLCHAR unused[15];
The database_size is member of first record set and
unused is member of second recordset returned by the sp_spaceused.
SQLINTEGER rlength;
retcode = SQLAllocHandle(SQL_HANDLE_STMT,hDBC,&hStmt);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
{
retcode = SQLExecDirect(hStmt,(unsigned char *)sqlCommand,
SQL_NTS);
retcode = SQLBindCol(hStmt,2, SQL_CHAR, (SQLPOINTER) &database_size,
sizeof(database_size),&rlength);
//Problem - was not able to bind any
column of the second recordset so not able to fetch the unused space.
if (retcode != SQL_SUCCESS )
{
AfxMessageBox("Error occured while binding");
}
CString csDatabase_size;
//The while loop below returns after the first iteration as only first
recordset is available and nothing is accessible of second recordset so
cant get 'unused' field
while ((retcode = SQLFetch(hStmt)) == SQL_SUCCESS)
{
stored the return
values of database_size and unused.
}
SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
}
else
{
AfxMessageBox("error while allocating a statement handle");
}
So the Problem is :
Is there any way to access all recordsets returned by sp_spaceused
using ODBC APIs on C++?
Please reply.
Thanks in advance
-Ashutosh
exec sp_spaceused returns:
Result set 1:
database_name
database_size
unallocated space
Result set 2:
reserved
data
index_size
unused
So, from your description you are trying to get database_size (col2) from
first result set, and unused (col4) from second result set.
You have to:
SQLExecDirect()
SQLBindCol( database_name, col2 )
SQLFetch() until all records read from first result set
SQLMoreResults() if returns SQL_SUCESS then you have another result set, so
...
SQLFreeStmt( SQL_UNBIND )
SQLBindCol( unused, col4 )
SQLFetch() until all records read from second result set
cmk
|||Thanx alot for your help !
Chris Kushnir wrote:
> exec sp_spaceused returns:
> Result set 1:
> database_name
> database_size
> unallocated space
> Result set 2:
> reserved
> data
> index_size
> unused
> So, from your description you are trying to get database_size (col2) from
> first result set, and unused (col4) from second result set.
> You have to:
> SQLExecDirect()
> SQLBindCol( database_name, col2 )
> SQLFetch() until all records read from first result set
> SQLMoreResults() if returns SQL_SUCESS then you have another result set, so
> ...
> SQLFreeStmt( SQL_UNBIND )
> SQLBindCol( unused, col4 )
> SQLFetch() until all records read from second result set
>
> cmk

Problem with values returned by Calculated Member

Dear Friends,

I have a problem with CM:

Example

Row Field1 Field2 Field3 Total

1 31662 1 14,75 467014,5

2 250000 1 6,94 1735000

Total for each row= Field1*Field3 / Field2

The value I want for Calculated Member is Total1 + Total 2, 2202014,5, but the value is wrong... The calculated member gives me the value from: SUM Field1* SUM Field3 / SUM Field2, 3054624,39

The MDX:

[Measures].[Open Pos]*[Measures].[New Price]/[Measures].[Fx Rate Rep Cur]

And all the measures are with AggregateFunction as SUM...

Where is the problem? How can I calculate each row in spite of the sum?

Thanks!!

More help to you:

29-12-2006 BANCO PASTOR SA 31662 1 14,75 467.014,50 29-12-2006 GALP ENERGIA SGPS SA B 250000 1 6,94 1.735.000,00

For the date 29.12.2006 I want the calculated member with the value 2202014,5

And currently I'having the value 3054624,39.

|||Is the required calculation Sum(Field 1 * Field 3) / Sum (Field 2)? If so, try adding a calculated column to the DSV that does the (Field 1 * Field 3) calculation. Add this as a new measure, with Sum as the aggregation function. Then your calculated measure will be (New Measure) / (Field 2).|||

for the date 29-12-2006 I need to calculate each line of fact Table...

Example:

Date Field1 Field2 Field3 EachRow 29-12-2006 100 1 5 500 29-12-2006 200 1 4 800 29-12-2006 300 1 2 600 Correct 1900

As I did, in Calculated Member, the value returned is not 1900, but is 2200!! :-(

29-12-2006 600 3 11 2200

Understood?

Thanks!

|||

Then just add the entire calculation ( (Field 1 * Field 3) / Field 2 ) as a calculated column in your DSV. Create a new measure on that column, and set the AggregationFunction to SUM.

Anytime you need something calculated at the fact level, it's usually best to either add it to fact table directly, or include it in the DSV.

|||But field1 is a named calculation, and I cant use this value to create a new named calculation... :-(|||You should be able to replicate the calculation for Field1 in your new calculated column.|||

How can I do it?

When I am creating the second named calculation, in the expression textbox I wrote: [Field1]+1

and returned me the error message: Invalid column named 'Field1'

Field1 is a named calculation... :-(

|||Copy the expression from Field1 into Field2, and add the additional calculations that need to be performed.|||

The problem is that the calculations in named calculation Field 1 is not so simple...

yeh.. but I understood that is no possible call a NC from another NC... thanks!

Problem with values returned by Calculated Member

Dear Friends,

I have a problem with CM:

Example

Row Field1 Field2 Field3 Total

1 31662 1 14,75 467014,5

2 250000 1 6,94 1735000

Total for each row= Field1*Field3 / Field2

The value I want for Calculated Member is Total1 + Total 2, 2202014,5, but the value is wrong... The calculated member gives me the value from: SUM Field1* SUM Field3 / SUM Field2, 3054624,39

The MDX:

[Measures].[Open Pos]*[Measures].[New Price]/[Measures].[Fx Rate Rep Cur]

And all the measures are with AggregateFunction as SUM...

Where is the problem? How can I calculate each row in spite of the sum?

Thanks!!

More help to you:

29-12-2006

BANCO PASTOR SA

31662

1

14,75

467.014,50

29-12-2006

GALP ENERGIA SGPS SA B

250000

1

6,94

1.735.000,00

For the date 29.12.2006 I want the calculated member with the value 2202014,5

And currently I'having the value 3054624,39.

|||Is the required calculation Sum(Field 1 * Field 3) / Sum (Field 2)? If so, try adding a calculated column to the DSV that does the (Field 1 * Field 3) calculation. Add this as a new measure, with Sum as the aggregation function. Then your calculated measure will be (New Measure) / (Field 2).|||

for the date 29-12-2006 I need to calculate each line of fact Table...

Example:

Date

Field1

Field2

Field3

EachRow

29-12-2006

100

1

5

500

29-12-2006

200

1

4

800

29-12-2006

300

1

2

600

Correct

1900

As I did, in Calculated Member, the value returned is not 1900, but is 2200!! :-(

29-12-2006

600

3

11

2200

Understood?

Thanks!

|||

Then just add the entire calculation ( (Field 1 * Field 3) / Field 2 ) as a calculated column in your DSV. Create a new measure on that column, and set the AggregationFunction to SUM.

Anytime you need something calculated at the fact level, it's usually best to either add it to fact table directly, or include it in the DSV.

|||But field1 is a named calculation, and I cant use this value to create a new named calculation... :-(|||You should be able to replicate the calculation for Field1 in your new calculated column.|||

How can I do it?

When I am creating the second named calculation, in the expression textbox I wrote: [Field1]+1

and returned me the error message: Invalid column named 'Field1'

Field1 is a named calculation... :-(

|||Copy the expression from Field1 into Field2, and add the additional calculations that need to be performed.|||

The problem is that the calculations in named calculation Field 1 is not so simple...

yeh.. but I understood that is no possible call a NC from another NC... thanks!