February 21, 2008 at 4:08 am
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.
February 21, 2008 at 7:52 am
Correct me if I am wrong. You have 992 columns coming in from a flat file?
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
February 21, 2008 at 7:56 am
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!
February 21, 2008 at 8:00 am
I've attached the file to illustrate it better.
February 21, 2008 at 8:10 am
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.
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
February 21, 2008 at 8:36 am
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?
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
February 21, 2008 at 8:38 am
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.
February 21, 2008 at 8:44 am
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.".
February 21, 2008 at 10:17 am
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?
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
February 21, 2008 at 10:22 am
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.
February 21, 2008 at 10:41 am
Ok, Let me get it right. You are just trying to feed a table from a flat file right? If so, just do a simple package by having flat file to OLE DB. I have attach the looks, let me know of any problems.
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
February 21, 2008 at 2:53 pm
In case you were looking for some info on that system SP - it's available only via OLE and ODBC connections. It's mostly documented under API cursors or OLE DB, and NOT under SQL Server.
Some intro info:
http://msdn2.microsoft.com/en-us/library/ms187088.aspx
http://msdn2.microsoft.com/en-us/library/ms189546.aspx
http://msdn2.microsoft.com/en-us/library/aa172588(sql.80).aspx
That being said - from what those documents mention that sp_CURSOR is ALWAYS invoked as part of mapping fields from an OLE DB source to SQL Server, so I am not sure there is an issue IN that system stored proc (since the import works to a simple table).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 22, 2008 at 2:42 am
I'm doing something similar to that as it is. See the attached.
February 22, 2008 at 3:49 am
Thanks Matt. Having looked at those web pages there's no limitation on the number of parameters either, although I wouldn't call a 992 columned table a simple table! I can only assume this is the problem. Would it help if I attached the create table statement for the sql table it's populating?
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply