What is significance of OpenRowSet property of oledb destination control in SSIS? My SQL admin has declared a new policy in which he says "Applications which use OpenRowset queries will not function." so I'm concerned about this whether my packages will work or not. Please advice.
+1
A:
Can you use SQL Profiler to check what is going on under the hood when your package runs? You should be able to see if it uses an OpenRowset query.
revelator
2010-03-17 11:02:32
Are you providing the table name to the destination provider? If so, I believe this will use OpenRowset. You may need to change the property to a SQL Command and use that for your inserts.
revelator
2010-03-17 11:20:51
I ran SQL profiler and disn't find any OpenRowSet query, does it mean that nothing to worry about :)@revelator - Yes, I'm mentioning the table name, but from profiler trace I didn't find anything.
Pramodtech
2010-03-17 12:32:56
It may still be that the destination uses OpenRowSet despite not seeing it in Profiler - see this blog post by Jamie Thomson - http://consultingblogs.emc.com/jamiethomson/archive/2006/08/14/SSIS_3A00_-Destination-Adapter-Comparison.aspxIf you use a SQL Command in the destination it should use sp_executesql instead.
revelator
2010-03-17 14:02:57
@revelator - Thanks. So it is good if I use SQL command, but then do I need to write complete query e.g. Insert into table values(....)? and how wiil I map columns? can you explain bit more?
Pramodtech
2010-03-18 06:56:45
From the Jamie Thompson link in @revelator's comment, it appears that you can avoid all of this by simply using a SqlServerDestination instead of an OLEDB destination. No need to use SQL Command (which is always much slower since it acts row at a time rather than bulk)
William Todd Salzman
2010-03-18 10:17:44
Yes you could use the SqlServerDestination but only if you are accessing a local SQL Server. If you need to move servers you'd need to re-engineer your SSIS code. I've just done a test now I'm on my SSIS box, and with OpenRowset disabled on the server, the OleDB Destination still runs when any of the OpenRowset options are used in the AccessMode. So your package should be fine...give it a test if your SQL Admin can disable OpenRowset for you.
revelator
2010-03-18 10:30:53
Thanks a lot revelator. I tried it and it worked for me. But before that I fought for this and now Admin has enabled it :)
Pramodtech
2010-03-18 14:51:04