August 20, 2013 at 11:38 pm
whereisSQL? (8/20/2013)
I am stuck at a similar dilemma with Excel source and Jet provider.Cannot edit registry key.
I was thinking about this.. but I am not sure if it is possible.. Can I add a dummy row with more than 255 chars each time data flow task runs by using a script task and then take the dummy row out using an identifier?
Script Task (add dummy row, move all rows one level below dummy)
Excel Source
Some other Task (which you would normally go to and take dummy row out)
I have near zero scripting skills and am looking for help to see if this is a possibility
If you can't use a CSV as your source instead - which is my preferred approach whenever the subject of Excel comes up - then yes, you can do this. Use a Conditional Split in your dataflow to send the unwanted row to an unused output.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 21, 2013 at 6:10 am
What about adding the dummy line? I am not loading the file,to open it and add the line. It is getting loaded through application GUI on client side.
August 21, 2013 at 6:29 am
whereisSQL? (8/21/2013)
What about adding the dummy line? I am not loading the file,to open it and add the line. It is getting loaded through application GUI on client side.
Sorry - I misunderstood your post - I thought that the dummy line was going to be in the source file.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 21, 2013 at 6:39 am
robert.gerald.taylor (8/20/2013)
Donny Z (8/19/2013)
Jeff Moden (10/16/2011)
The way I solved this problem for my group was to simply use QUOTENAME(columnname, '"') and make sure that I used '"' as the text qualifier.Can anyone explain more about this??
This kind of statement is like "I know how, but I won't tell." kinda statement.
Take a look at: http://technet.microsoft.com/en-us/library/ms176114(v=sql.105).aspx
A simple search brought that back. :rolleyes:
HTH,
Rob
Hi Rob,
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 :ermm:
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.
--------------------------------------
I already spent few days looking for answer like this for SSIS 2008 (yup, the saga continues to 2008) and bcoz it's driver caused problem, I'm stuck w/ converting the excel to other delimited format file then use SSIS to read the result file. Still trying to find free converter which can be executed through CMD prompt though :pinch:
August 21, 2013 at 9:51 am
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.
August 21, 2013 at 10:29 am
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 :ermm:
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
August 27, 2013 at 2:19 am
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...!
July 14, 2014 at 6:11 pm
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. "
----------------------------------------
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply