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 «««123

Excel import - force column to be string (SSIS 2005) Expand / Collapse
Author
Message
Posted Wednesday, August 21, 2013 9:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 10, 2014 1:50 PM
Points: 178, Visits: 367
I found some code to open excel and read contents. I am thinking of testing it out.

Hoping to use a script task to open excel, modify it by sorting by character count on the "255 problem column", and close excel. Then use Excel source.. and it should not truncate, or at least that's the plan.

If you guys have any luck please update.
Post #1486786
Posted Wednesday, August 21, 2013 10:29 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 16, 2014 11:55 AM
Points: 1,197, Visits: 1,239
Donny Z (8/21/2013)

I know what QUOTENAME's function is.
What I mean is how Jeff apply it in the excel source, cause AFAIK we can't use every T-SQL function when we try to select from excel, and we can't define text-qualifier unless we're using Flat File Source

So I thought maybe Jeff got something up his sleeve or know something I don't.
And basically I asked the same question suslikovich asked before my post.

Sorry, I misunderstood where the unknown was.

Throwing together a Q&D test SSIS package, I see that the Jet engine squawks if you try to use the QUOTENAME() function in a SELECT against the Excel data source.

Rob
Post #1486801
Posted Tuesday, August 27, 2013 2:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 16, 2014 7:33 AM
Points: 45, Visits: 346
Hi,

Did you get the solution ??? @50th row I have string... the destination data type is varchar only. now it is storing as NULL.

it is not working for me.

Plz suggest how to overcome this problem...!
Post #1488607
Posted Monday, July 14, 2014 6:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 11, 2014 7:18 PM
Points: 3, Visits: 74
Grasshopper had a nice idea in response to your post... I am pasting his workaround below....it works!!!


----------------------------------------
GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 6/17/2013 2:51 PM
Points: 21, Visits: 143

"A quick update... temporary solution is to actually turn off the "use headers" option in the connection so that the first row is text (i.e. import the headers then delete the header row later). Rubbish solution as I then need to convert the non-text fields back to whatever they are meant to be. "

----------------------------------------
Post #1592384
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse