How to connect to a database through a script object

• Blair Dee-474691

  Right there with Babe

  Points: 718

  More actions

  July 25, 2011 at 2:48 pm

  #241997

  Hi,

  I am working on creating an SSIS package that will import data from a CSV into a database table. Then use a script task to look through each record in that table, build an XML statement from it and submit that XML to a web service.

  I have that data import working, and I am able to use the script task to submit hard coded XML to the web service but I am unsure of how to connect to the table from my script task to loop through the records....

  How would I do this?

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  July 25, 2011 at 2:56 pm

  #1358157

  This should help with that

  http://blogs.msdn.com/b/mattm/archive/2008/08/22/accessing-oledb-connection-managers-in-a-script.aspx

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• Chuck Hottle

  SSCarpal Tunnel

  Points: 4777

  More actions

  July 25, 2011 at 2:59 pm

  #1358159

  You'd populate a recordset. This seems to be a good descriptino of how to acces it in a script task.

  http://sqlblog.com/blogs/andy_leonard/archive/2007/10/14/ssis-design-pattern-read-a-dataset-from-variable-in-a-script-task.aspx