Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Excel source having more than 255 characters Expand / Collapse
Author
Message
Posted Monday, February 24, 2014 9:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 4:32 AM
Points: 5, Visits: 202
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.
Post #1544610
Posted Wednesday, February 26, 2014 1:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 3:29 PM
Points: 1, Visits: 37
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]

Post #1545570
Posted Thursday, February 27, 2014 2:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 4:32 AM
Points: 5, Visits: 202
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!
Post #1545767
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse