SSIS Package help

[Bernie]

OK, I have never really worked with this but have taken over a project and have 1 small issue.

Basically I am extracting data out a database to a CSV file.

All is working except this one issue which I cannot seem to solve.

In the CSV file I am getting an amount field looking like this: 12345.67000000000000000000

I don't want the trailing zeros.

The source field is this:

CAST(sum(Amount) as DECIMAL(18,2)) as [Amount],

which is defined as decimal(38,20) in the database.

In the Flat File Connection Manager I have this field as DT_DECIMAL with DataScale 2.

What am I missing.

TIA.

 

[Batista]

CAST(sum(Amount) as DECIMAL(18,2)) as [Amount]

Amount.tostring(##.##)
Cast back as decimal and save?

The problem may be excel, sometimes its retarded and you may need to change the field type in the excel file depending on how you extracting to the excel?

 

[Bernie]

CAST(sum(Amount) as DECIMAL(18,2)) as [Amount]

Amount.tostring(##.##)
Cast back as decimal and save?

The problem may be excel, sometimes its retarded and you may need to change the field type in the excel file depending on how you extracting to the excel?

Thanks, actually excel is showing fine, but the raw text file has the trailing zeros. Will look at your suggestion.

 

[Compton_effect]

SSIS - SQL Server Irritation Services.

When it works it can be monitored by a trained monkey. But if something goes wrong you spend hours trying to fix it.
And it just does not play well with Excel.

Have you tracked it with Data Viewer to see what it looks like while running?

 

[Bernie]

SSIS - SQL Server Irritation Services.

When it works it can be monitored by a trained monkey. But if something goes wrong you spend hours trying to fix it.
And it just does not play well with Excel.

Have you tracked it with Data Viewer to see what it looks like while running?

I so agree

My solution so far

sed s/000000000000000000//g infile.csv > outfile.csv - it works

Let me investigate this Data viewer - thanks.

 

[envo]

Erm, use Round.... because you're casting it to decimal with 18 places

ROUND(CAST(sum(Amount) as DECIMAL(18,2)),2) as [Amount]

 

[Batista]

Erm, use Round.... because you're casting it to decimal with 18 places

ROUND(CAST(sum(Amount) as DECIMAL(18,2)),2) as [Amount]

False, he is casting to 2 places.18 is the the placeholder for the numbers before the '.'

 

[envo]

False, he is casting to 2 places.18 is the the placeholder for the numbers before the '.'

Okay, so I was wrong, but rounding it will work

 

[Bernie]

Sort of solved. No matter what I changed in the SQL it didnt work.

So I just deleted the OLE DB Source and re-created it - it then worked. So it seems that once you have created the package it caches your column formats, well it seemed to do it for me.

 

[Compton_effect]

Sort of solved. No matter what I changed in the SQL it didnt work.

So I just deleted the OLE DB Source and re-created it - it then worked. So it seems that once you have created the package it caches your column formats, well it seemed to do it for me.

Your Source metadata. SSIS sometimes does weird stuff with it.

 

[envo]

Sort of solved. No matter what I changed in the SQL it didnt work.

So I just deleted the OLE DB Source and re-created it - it then worked. So it seems that once you have created the package it caches your column formats, well it seemed to do it for me.

Ugh, I hate when that happens. There should be a refresh available so you don't have to delete/recreate