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»»

SSIS problem with oledb destination and too many insert columns Expand / Collapse
Author
Message
Posted Thursday, February 21, 2008 4:07 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 5:45 AM
Points: 247, Visits: 695
I have an ssis package that needs to populate a sql table with data from a pipe-delimited text file containing 992 (!) columns per record. Not the best design I know but out of my control I'm afraid! Initially I'd set up the package to contain a data flow task to use an ole db destination control where the access mode was set to Table or view mode. For some reason though, when running the package it would crash, with an error stating the parameter 'value' was not valid in the sp_cursor procedure. On setting up a trace in profiler to see what this control actually does it appears it tries to insert the records using the sp_cursor procedure. Running the same query in SQL Server Management Studio gives the same result.
After much testing and pulling of hair out, I've found that by replacing the sp_cursor statement with an insert statement the record populated fine which suggests that sp_cursor cannot cope when more than a certain number of parameters are attempted. Not sure of the figure. Has anyone else come across this issue? If it is an issue, should I report it to Miicrosoft and if so, how?

I've managed to get round this problem however by setting the access mode to be "Table or view - fast load". Viewing the trace again confirms that SSIS attempts this via a "insert bulk" statement which loads fine.


  Post Attachments 
feeSched.txt (16 views, 12.86 KB)
Post #458453
Posted Thursday, February 21, 2008 7:52 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 21, 2014 10:39 AM
Points: 214, Visits: 568
Correct me if I am wrong. You have 992 columns coming in from a flat file?

Imagination is more important than knowledge-Albert Einstein
Post #458554
Posted Thursday, February 21, 2008 7:56 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 5:45 AM
Points: 247, Visits: 695
That's right, or rather 992 "logical" columns i.e. in each record of the flat file there's 992 fields separated by 991 pipes if that makes sense!
Post #458556
Posted Thursday, February 21, 2008 8:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 5:45 AM
Points: 247, Visits: 695
I've attached the file to illustrate it better.
Post #458560
Posted Thursday, February 21, 2008 8:10 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 21, 2014 10:39 AM
Points: 214, Visits: 568
Dude!!that's totally crazy to be having that many columns in a row!!. Did you check if you have exceeded the amount allowed? you have to compare that with the bytes you have in the data. By the way, can you please copy and paste your WHOLE error description here. There might be someone here who can also check it for you.



Imagination is more important than knowledge-Albert Einstein
Post #458569
Posted Thursday, February 21, 2008 8:36 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 21, 2014 10:39 AM
Points: 214, Visits: 568
Hi, I tried it my self with the chunk of info you had in the file you uploaded here. It worked for me. All I did was from flat file to an OLE DB destination in my dataflow. What else did you add that made the package crashed?

Imagination is more important than knowledge-Albert Einstein
Post #458593
Posted Thursday, February 21, 2008 8:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 5:45 AM
Points: 247, Visits: 695
Tell me about it! Provided by an external body unfortunately so we have no say in their layout. SQL tables allow 1024 columns so I checked that part out. Checked to see sp_cursor had a limit on number of parameters too but couldn't find anything.

The error message on running sp_cursor in sql server management studio (I've attached the full statement) is:

Msg 16909, Level 16, State 1, Procedure sp_cursor, Line 1
sp_cursor: The cursor identifier value provided (abcdef3) is not valid.


  Post Attachments 
exec sp.doc (9 views, 73.00 KB)
Post #458597
Posted Thursday, February 21, 2008 8:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 5:45 AM
Points: 247, Visits: 695
This is the error within SSIS that I believe causes the issue:

Error: 0xC0202009 at Populate the feeSchedTbl table, Populate the feeSchedTbl table [27920]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E23.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E23 Description: "sp_cursor: The value of the parameter 'value' is invalid.".
Post #458601
Posted Thursday, February 21, 2008 10:17 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 21, 2014 10:39 AM
Points: 214, Visits: 568
From reviewing the errors and also the files you have sent, it is clearly something is wrong somewhere in either the stored Procedure itself or the way you call it in SSIS. Can you elaborate how you are doing this by the way?

Imagination is more important than knowledge-Albert Einstein
Post #458668
Posted Thursday, February 21, 2008 10:22 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 5:45 AM
Points: 247, Visits: 695
I don't specifically request to use sp_cursor I think SSIS uses this as a default version when your access mode is set to Table or View. I only found out that sp_cursor was being used when I'd set up the trace. Difficult to find any info on sp_cursor around really.
Post #458674
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse