November 4, 2009 at 12:11 pm
I've configured the lookup and have it checking corresponding ID's, not the whole table. The excel source goes into the lookup box. The error says 'input column ID and reference column ID have incompatible data types. I've been troubleshooting it and haven't been able to fix it. I don't have an error output connected. I think I am missing something. The outflow of the lookup goes to the destination database table. Thanks!
November 4, 2009 at 1:09 pm
One guess would be that your PK data in Excel is in numeric format and that your PK on SQL Server is held as varchar(n) (even though it may look numeric). That would cause an error like that.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 4, 2009 at 2:29 pm
Everything in the xcel sheet is text formatted. The datatypes are nvarchar(255). I could try disconnecting and refreshing all the connections in the data flow controls. im a little stumped...i tried switching the data connections from excel.ID --> ID to lookup.ID--> ID. it didnt help. it could be something else not even related to the data type that it's not happy with...i wish i could give more information that may be the problem. i made some screenshots but the attachment box here doesn't seem to be working. thanks so much for your help!
November 4, 2009 at 2:37 pm
Formatting as text does not affect how Excel holds data.
Specifically, if you format a number as text in Excel and then import it, it will still come in as a number.
You might think about sending your ID from Excel through a data conversion transformation (transform to NVarchar) and then into your lookup, using the transformed value ... But if your data has any leading zeros, they are already toast and you'll have to put them back.
I guess you're learning a lot today. Not only is SSIS quite a steep learning curve, but you're throwing in the idiosyncrasies of good old Excel too.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 5, 2009 at 7:25 am
Hi Phil,
lol thanks. Yea it's taking a bit (I've been getting along with just a source and a destination the whole time). The file is formatted as text because some of the other columns are primarily numerical but there's a small amount of text fields and I learned the solution to that was to format the cells as text and then put in the values. Also there's another column with some leading 0's and so those give me trouble as well. The non-numerical values in those primarily numerical columns show as null, and I've re-typed those in manually a few times over to get them to show. Needless to say, it's not going to work that well from this point out so that's my next issue to tackle.
Anyway that's why I have text formatting. I will look at your idea for the lookup error, which is persisting. Thank you!
November 5, 2009 at 8:20 am
I have a couple of Excel suggestions for you:
1) When entering numeric data that you want to to be treated as text, enter a single quote as your 1st character. This forces Excel to treat whatever is entered after the quote as text. After entering data this way, you should notice that it is left justified in its cell (numeric data is usually right justified within its cell).
This is obviously a pain for your users though. Here's another option.
2) You could try creating a new calculated column to the right of your existing columns and use the TEXT function to convert all your numeric data to text. Use this column in your import, rather than the heterogeneous-datatype column.
As I mentioned before, formatting data in Excel affects only display within Excel, not how the data is held.
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 5, 2009 at 2:05 pm
Phil,
For the data mismatch, I tried deleting and putting the lookup back in and now it doesn't even want to connect the ID column but everything else.
The transformation box doesn't want to convert it to txt bc "it's not allowed". there's no option for nvarchar that i can see.
I took the transformation out and tried to just use the lookup again and im not sure what to tweak to make it compare ID values. It refuses to even try. google here i come...
November 5, 2009 at 4:34 pm
I think it's that when I connect the excel source to the data conversion, it thinks it's a double precision float RT_8 and it sees the rest as DT_WSTR. How can I keep it from thinking this? I think that's why it does not want to do the look up on the IDs. I don't see a place in the source options to change it and I've tried changing the formats in excel and that doesn't seem to help, either. it was a cheap shot but i had to try it.
November 8, 2009 at 8:27 am
"How do i achieve it thru SSIS or thru DTSWizard?"
I have a humble question.
Must you use etc?
I would recommend you to save the Excelfile as as a TAB separated text file
and then use Bulkinsert. I started to use first DTS and then SSIS to import Excel-files
and ran into problems which I found tricky to handle.
If a user works with Excel and wants to save some data in SQL server I also may use
a macro run in Excel (VBA and ADO and remote OLE DB).
/Gosta
November 12, 2009 at 12:24 pm
Thanks very much Phil and Gosta.
I'm going to post the solution to my problem for anyone else who needs:
In order to append new rows from my excel files to tables in a SQL server 2005 database(s) using SSIS, I used: excel source-->data conversion--> lookup--> ole db destination. I connected the red error output to the destination, making sure to tell lookup to redirect the new rows there (within Configure error output).
For the ID data types that SSIS said did not match from source to destination, I ran them through the conversion to make sure they were DT_WSTR and not DT R8 double (which made a copy of that column w the new data type called "copy of ID"). Then in the destination, I connected the output (copy of ID) to the ID destination and it worked.
I had an issue w it telling it was truncating another column although the data type was correctly set. I went in and told it to ignore the failure. I pulled up the field in the db and it seemed to have imported fine.
The reason I'm using excel is because I need to use macros to get the data I need into the cells and then use macros to clean it up to be inputted into the dbs I have. It's all manual data. 🙁
Once again, thanks guys!
November 12, 2009 at 12:29 pm
yes, thanks, I tried your suggestion and made it calculate the value and then feed it in. That made the db recognize what it needed to. thanks!!
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply