Jmandawg


We are importing Flat file data from our Mainframe system. We have a lot of money amounts coming in, but the mainframe does not store the decimals in the flat file. So for example a row in the file might look like this:

+0000007894-0000000563

Where the first value is $78.94 and the second value is -$5.63

Is there anyway to have the Flat file connection manager put in the decimal place for me, or do i have to create derived columns for each column and divide it by 100 There like 50-100 columns per file, so i'm looking for a better, quicker way.

Thanks in advance.

John



Re: Adding implied decimal without a derived column from Flat file datasource

Jamie Thomson


Jmandawg wrote:
We are importing Flat file data from our Mainframe system. We have a lot of money amounts coming in, but the mainframe does not store the decimals in the flat file. So for example a row in the file might look like this:

+0000007894-0000000563

Where the first value is $78.94 and the second value is -$5.63

Is there anyway to have the Flat file connection manager put in the decimal place for me, or do i have to create derived columns for each column and divide it by 100 There like 50-100 columns per file, so i'm looking for a better, quicker way.

Thanks in advance.

John

The Connection Manager will not do this for you, nor will the Flat File Source Adapter. You will have to use a Derived Column component I'm afraid.

-Jamie







Re: Adding implied decimal without a derived column from Flat file datasource

Phil Brammer

I agree with Jamie. You could though, load the data into an appropriate formatted table (correct precision/scale for those fields) and then after loading all of the data, issue a batch update. Something like this:

UPDATE myTable
SET myColumn1 = myColumn1 / 100,
myColumn2 = myColumn2 / 100,
myColumn3 = myColumn3 / 100,
.
.
.
myColumnn = myColumnn / 100






Re: Adding implied decimal without a derived column from Flat file datasource

Jmandawg

Ok, thanks everyone. Thats what i was afraid of, but my friend bet my paycheck that there was an easier/cleaner way. I guess he owns me. Either way, it's going to slow things down a bit, there are a few million rows in these files.

Thanks again for the help.




Re: Adding implied decimal without a derived column from Flat file datasource

Phil Brammer

Jmandawg wrote:
Ok, thanks everyone. Thats what i was afraid of, but my friend bet my paycheck that there was an easier/cleaner way. I guess he owns me. Either way, it's going to slow things down a bit, there are a few million rows in these files.

Thanks again for the help.


Okay, well, you could probably code a solution that iterates the columns in a given table to assemble the batch update statement and then either populate a variable with that update statement, or execute it within. This could likely be done in an Execute SQL task. At least, that's the vision I have in my head.

The row count shouldn't be an issue so don't worry about it.





Re: Adding implied decimal without a derived column from Flat file datasource

Jamie Thomson

Jmandawg wrote:
Ok, thanks everyone. Thats what i was afraid of, but my friend bet my paycheck that there was an easier/cleaner way. I guess he owns me. Either way, it's going to slow things down a bit, there are a few million rows in these files.

Thanks again for the help.

Why is the number of rows an issue Even if this could be done without a Derived Column there would still have to be some parsing going on and some conditional transformation somewhere (unless I've misunderstood) hence using a Derived Column is not going to make it any slower than it would otherwise be.

I happen to think there is no cleaner way than using a Derived Column. It is "clean" because it is explicit, I hate the idea of something happening implicitly that I am just supposed to trust.

-Jamie






Re: Adding implied decimal without a derived column from Flat file datasource

Jmandawg

I thought if it could be done via the Flat File Connection Manager i would be able to do a fast load. I guess i was wrong.




Re: Adding implied decimal without a derived column from Flat file datasource

Phil Brammer

Connection managers shouldn't be responsible for formatting data.





Re: Adding implied decimal without a derived column from Flat file datasource

Jamie Thomson

Jmandawg wrote:
I thought if it could be done via the Flat File Connection Manager i would be able to do a fast load. I guess i was wrong.

Unfortunately not. The connection manager only defines the structure of the file. What YOU want to do is parse the file and then transform it. Which incidentally is exactly what SSIS excels at.

-Jamie






Re: Adding implied decimal without a derived column from Flat file datasource

Jmandawg

You are right, i didn't think about it that way.
Thanks again for your help guys.




Re: Adding implied decimal without a derived column from Flat file datasource

Jamie Thomson

Jmandawg wrote:
You are right, i didn't think about it that way.
Thanks again for your help guys.

No problem. Glad to help.