SQL Server Destination [16]

SQL Server Destination [16]

• dazza70

  Darren,

  The pkg is being developed/run on my desktop. I guess I can't use the Sql Server destination then. I'll have to change it to OLE DB destination.

  
  

• Jeff

  Can I ask the obvious question, is the package being run/developed on the production server or yoru desktop The SQL Server Destination requires that you are sat at/running the package on the SQL Server machine.

  
  
  

• ZGutt

  I have the same situation; the package is in a network folder. That seems like an unusual requirement. SSIS can't run against a network server
  

• Doug DeBug

  Can anyone tell me why it only works on a local server, and how we may run a package on a remote server without using SQL Agent (sample C# code )

  The environment we are using already has a unix scheduler, and using SQL Agent is redundant.

  Perhaps if this task failed it could revert back to using OLE DB

  
  
  

• Przemo2

  There is nothing wrong with using network folders. The package is just like any other file, you need permissions to access it. If you have problems then perhaps you could describe it more detail, and include any error messages you get.

  The previous post was specific to the SQL Server Destination, which does not use network shares or files, but does require the SQL Server to be local. There are alternatives, OLE-DB, but that is the trade off for the increased performance of the fast load features of the SQL Server Destination.

  
  
  

• robertz2

  It makes use of SQL Server's BULK INSERT facility (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/a0227cd8-6944-4547-87e8-7b2507e26442.htm) which must be running on the same machine. it isn't a deliberate limitation that they chose to put into it, its just the way it is.

  You could perhaps build in that functionality but I don't recommend it. You should know where your package is going to execute and build the package accordingly. The OLE DB destination is pretty speedy.

  -Jamie

  
  
  

• karl_otto

  Do you not, or can you not run a local instance of SQL Server Whilst you will probably not have all your data, or even all tables or rows, developing against a local instance is much more flexible, reliable and saves you having conflicts with other members of your team. You can then use the SQL Destination quite happily. If you cannot do this, then yes you will have to use the OLE-DB Destination. To be fair the OLE-DB destination still has the fast load option, and this has done us rather well prior to SSIS, so unless you really need the perf I'd go with OLE-DB if it is easier for you.
  
  

• DaveJ4

  Willy,

  I agree. Also, the name 'SQL Server Destination' gives no hint that it is only for (local) server.

  
  

• Rattlerr

  I really appreciated finding this post, since I was getting ready to reinstall SSIS on our SQL 2005 Test server because of this error.

  While I understand the intent here -- providing a specific Connection setup for programmers in a Development environment -- better documentation about the limitations of the SQL Destination object would have saved me several hours of time.

  Thankfully, this posting saved me at least half a day.

  Thanks,

  Willy

  
  
  

• MCTronix.com

  Books Online says "You should not use the SQL Server destination in packages that access a SQL Server database on a remote server, Instead, the packages should use the OLE DB destination." That kind of covers it, but I would strongly encourage you to submit feedback on BOL when you find topics lacking. See the "Send Feedback" link in the top right hand corner of every page.

  I think that is OK, it covers the point, but should be more explicit, so I submitted some feedback. This gets rolled up and assessed for the regular BOL updates MS have committed to producing during the SQL 2005 lifecycle. We are on our second update already if you include the SP1 drop, so it is worthwhile.

  
  
  
• SQL Server Destination [16]