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
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.
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
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
Jmandawg wrote:
You are right, i didn't think about it that way.
Thanks again for your help guys.
No problem. Glad to help.