July 24, 2017 at 4:16 am
Hi
I have an excel document with a column called Code
this column is formatted to General
there are codes like
3.01
3.02
3.03
some codes look like 3.03.06... I don't need the data after the second decimal point for this piece of data I would need just the 3.03
left function does not work well in SSIS .. so I have tried to make a derived column and I have tried to use the substring function with no joy
any ideas
please help
where
July 24, 2017 at 4:40 am
joanna.seldon - Monday, July 24, 2017 4:16 AMHiI have an excel document with a column called Code
this column is formatted to General
there are codes like
3.01
3.02
3.03some codes look like 3.03.06... I don't need the data after the second decimal point for this piece of data I would need just the 3.03
left function does not work well in SSIS .. so I have tried to make a derived column and I have tried to use the substring function with no joyany ideas
please help
where
Isn't Excel a huge amount of fun in SSIS?
In cases like this I generally import the data into a staging table, which has everything defines as character and nullable. This staging table will also have additional columns, of the format that I actually require for the data. Then I can write a stored proc that can validate and convert from the character columns to whichever column as it copies the data to the column that has a definition that I need.
July 24, 2017 at 5:53 am
This would work, you just need to change the variable to use the column that you need.SUBSTRING( @[User::Code] , 1, FINDSTRING( @[User::Code]+"..", "." , 2) - 1)
And here's another option.TOKEN( @[User::Code] , ".", 1 ) + "." + TOKEN( @[User::Code] , ".", 2 )
July 25, 2017 at 2:12 am
Hi
I keep having error about does not support data type DT_R8
I have tried to go to the advanced editor of the derived column, but it won't let me change the data type
any ideas...
please help
July 25, 2017 at 2:31 am
joanna.seldon - Tuesday, July 25, 2017 2:12 AMHi
I keep having error about does not support data type DT_R8
I have tried to go to the advanced editor of the derived column, but it won't let me change the data type
any ideas...
please help
You can't change the data type for when using the ACE drivers; they're "nice" (a pain in a*se) and decide what they think the data is for you (normally based on the first 5-10 rows).
If you have the ability to amend your spreadsheet before import, I suggest changing the data format of the column to "text". Is that something you're able to do? The ACE driver's should then read the column as string, rather than trying to interpret it as something some of the rows are not.
If not, then I do have a solution, however, it is not pretty (and involves building your own Script Component as a datasource).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 25, 2017 at 5:33 am
joanna.seldon - Tuesday, July 25, 2017 2:12 AMHi
I keep having error about does not support data type DT_R8
I have tried to go to the advanced editor of the derived column, but it won't let me change the data type
any ideas...
please help
A couple of things:
- DT_R8 is a double precision floating point data type.
- There's no such thing as a double decimal point, there's only a second point point in a string.
That said, something like 3.03.06... is not a DT_R8, it must be some kind of string (or formatted date which is also a string). If DT_R8 is your source, there might be something wrong with your connection. If it's your destination, you just need to do an additional cast. If you used some transformation to define it as a float, then you need to figure out what happened. You're not giving enough information for us to help.
July 26, 2017 at 7:04 am
HI
Converting to text does seem the best way forward
because when I manually convert the column 'Code' in excel to text the data comes through fine to the SQL server... I am trying to avoid any manual changing of columns, hence why I am trying to use the derived column
(DT_NTEXT, 1252)[Code]
when I am entering this I get an error incomplete token
any ideas
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply