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


SSIS problem with oledb destination and too many insert columns


SSIS problem with oledb destination and too many insert columns

Author
Message
Griffster
Griffster
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1645 Visits: 713
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.
Attachments
feeSched.txt (45 views, 12.00 KB)
SQLdevotee
SQLdevotee
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1154 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
Griffster
Griffster
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1645 Visits: 713
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!
Griffster
Griffster
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1645 Visits: 713
I've attached the file to illustrate it better.
SQLdevotee
SQLdevotee
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1154 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
SQLdevotee
SQLdevotee
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1154 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
Griffster
Griffster
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1645 Visits: 713
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.
Attachments
exec sp.doc (42 views, 73.00 KB)
Griffster
Griffster
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1645 Visits: 713
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.".
SQLdevotee
SQLdevotee
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1154 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
Griffster
Griffster
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1645 Visits: 713
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.
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