SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Excel source having more than 255 characters


Excel source having more than 255 characters

Author
Message
gward 98556
gward 98556
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 475
I had a similar issue in SSIS 2008 with an Excel file that had columns wider than 255 characters, as well as embedded text qualifiers and delimiters. A real pain as Microsoft were not handling embedded delimiters well in SSIS at the time. I got around it by saving from Excel in csv format, then putting a Schema.ini file in the same folder as the csv, and specifying the data types of each column (Memo for long strings, or Text, Date, Double etc) within the Schema.ini file. In Connection Managers I chose "New OLE DB Connection", New Provider: Microsoft Jet 4.0 OLE DB Provider.
Clicked "All" in left pane and set Extended Properties to the following (without the quotes): "text;HDR=Yes;FMT=Delimited"
Clicked "Connection" in the left pane and set Database file name to the folder where the csv is (without including the csv filename).
Created an OLE DB Source in the Data Flow and pick the new connection manager in the first dropdown.
Set data access mode to "SQL command" and set the SQL command text to "SELECT * FROM myexportname.csv" or whatever the name of the csv file is.

The text within the Schema.ini file should look something like this:
[myexportname.csv]
ColNameHeader = True
Format = CSVDelimited
DateTimeFormat=dd/MM/yyyy
Col1=Category Text
Col2=Registration Text
Col3=Description Text
Col4="Sales Big Description" Memo
etc....

If you have to load multiple csvs from a single folder with this method, they all must use the same Schema.ini file but this file can include separate schemas for each csv filename.
Not fun to set up but once done it is reliable.
riggerjon
riggerjon
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 85
Thanks to the contributors in this thread - it helped a bunch! Just three things to add:

1. My Jet registry key was under HKEY_LOCAL_MACHINE/SOFTWARE/Wow6432Node/Microsoft/Jet...
2. Setting TypeGuessRows to 0 can affect performance on large spreadsheets, but it scans all rows and avoids the issue in this thread.
3. Here's more detail on editing the registry: [url=http://support.microsoft.com/kb/189897/en-US][/url]
gward 98556
gward 98556
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 475
Don't forget that whenever you ever move the package to another server it will fail again until you or a colleague finds out about the registry hack. Ultimately that is why I didn't choose that route. If an SSIS package needs a registry hack to work then it's definitely worth putting a comment about it somewhere obvious in the package!:-)
waxingsatirical
waxingsatirical
SSC Veteran
SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)

Group: General Forum Members
Points: 210 Visits: 242
Have just been hitting these problems in SSIS with Visual Studio 2015.

Even though there is an 'Advanced Editor' which allows you to set the external datatypes to DT_NTEXT, this reverts to the detected type as soon as the window closes. Didn't try the registry hacks, just edited the first line of the spreadsheet to force correct datatypes.

This also alphanumeric columns that just happen to have only numbers in the first few rows. SSIS helpfully detects these as floats or something.
drew.allen
drew.allen
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15574 Visits: 11211
gward 98556 (2/27/2014)
Don't forget that whenever you ever move the package to another server it will fail again until you or a colleague finds out about the registry hack. Ultimately that is why I didn't choose that route. If an SSIS package needs a registry hack to work then it's definitely worth putting a comment about it somewhere obvious in the package!:-)


No, this registry entry is used when editing a package, so this is only an issue when the package is being developed, so the registry hack only needs to be applied on the developers' machines.

Drew

PS: I realize that this is an old thread, but this information is still relevant.

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search