I'm trying figure out where I have to change this and am frankly confused. It seems SSIS is selecting various columns as unicode/WSTR data types, but I want them to import as regular string types.
On the Data Flow tab in SSIS, I right-click on the source Data Flow component (the Excel file) and select Show Advanced Editor. Then on the last tab, Input and Output Properties, there's a tree view for the Excel output. There are "External Columns" and "Output Columns" containers in the tree view.
I tried setting some of these but they don't seem to "take". Do I need to change the data type for each column under both the External and Output columns?
That seems like a lot of work! And, as I say, I tried setting some, but I still got the same validation errors. So, then I go back to this spot (Advanced Editor -> Input and Output Properties tab) and my changes seem to have been lost.
Any help would be appreciated!
The recommended way for doing this is to use the Data Conversion Transform and explicitly specify your data type conversions there.
Try using the Import/Export wizard to generate a sample package for this.
|||Hi Bob,
What is your destination? Is it SQL Server or MS Access or any other database? If it is SQL Server, declare the varchar column as nvarchar to avoid this kind of conversion errors. But if you are importing data from Flat File, in the Flat File Connection Manager you have an option to set unicode characters by means of selecting the "Unicode" check box.
If it is Excel Source, then you need to change the datatype in your database. I don't find any other solution for this. Is anybody having any other solution, it is well and good.
Thanks & Regards,
Prakash Srinivasan.
|||I am going from Excel to a SQL table. Changing the data type on the SQL type isn't really going to be a reasonable solution, essentially doubling (or halving, depending on how you look at it) storage requirements.
From the SSIS tutorials, I know you can change the data type on the Flat File connection manager and am really struggling to understand why you can't do this w/ an Excel file. In fact, the Excel provider has "picked" the wrong data type in many cases... it "saw" some numbers in a column and decided it was a numeric field, but it's wrong, it's a string field, and in fact some of the data has an alpha in it.
So, I'm now back to trying to figure out how to sort this out when setting up the source file. I believe I can use a data conversion transformation, but I just don't understand why I can't do this at the source, as it were. If you have to use a data transformation, then the Excel provider should just bring in everything as a generic string and not try to cast it at all for you. And why shouldn't I then be able to tell it to "default" to a non-unicode string data type rather than unicode?
Also I'm all the more wondering what the "Input and Output Properties" tab in the Advanced Editor is all abou then? When do you use the External columns vs Output columns, vs both?
BOL does not seem to offer any meaningful information here.
|||I have the exact same issue. Row one in the excel file is numeric (20), many of the rest are text (20A, 20B etc). The excel connector forces this to a type of double, and won't let me convert to text, even if it did, it strips out the non-double values and gives me nulls. Same effect in the stored procedure that drove me to try and use SSIS. This is so easy outside of Excel! There has to something to allow you to override what Excel "thinks" the datatype is right?SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\temp\jjt.xls', 'SELECT * FROM [Jobs$]')
No comments:
Post a Comment