Powershell for data transfer from non-SQL Server database

• mcginn

  SSCommitted

  Points: 1592

  More actions

  July 26, 2010 at 4:24 pm

  #99344

  I have a tool that uses SSIS and stored procedures to extract data from a non-SQL Server database (Sybase) and loads the data into a SQL Server database. I have upgraded to SQL Server 2008 R2 and I am in the process of upgrading to Powershell 2.0. I want to wrap this process in a Powershell script and I would like to be able to remove SSIS from the equation if there is an OLEDB type SMO object available which would allow me to connect to a Sybase database source and manage the data in much the same way as I manage SQL Server databases through the use of SMO.

• patricklambin

  SSCrazy Eights

  Points: 9994

  More actions

  May 30, 2011 at 3:33 am

  #1331552

  Hello,

  What does that mean SMO ? = SQL Server Management Objects

  SMO , when it connects to an instance , is using a ServerConnection object , itself using a SqlConnection object which cannot be used with databases which are not included in a SQL Server instance.

  For what you want to do ( if i have well understood ), you should rely on a linked server.

  But there is some classes in the namespace System.Data.Oledb which could help you like

  OledbConnection, OledbCommand

  For that, i suggest you to have a look at this link :

  http://msdn.microsoft.com/en-us/library/system.data.oledb(v=VS.90).aspx

  Have a nice day