SSIS Script Component As Data Source - Read NText Data

• rka

  SSCommitted

  Points: 1599

  More actions

  May 17, 2016 at 12:17 am

  #314032

  I have a package with the following structure

  Control Flow

  - Script Task (Sets Global Values i.e. api_call_id & api_call_date into 2 variables)

  Data Flow Task

  - Script Source (Restful API Response into a Object Variable + Global variables)

  - OLEDB Destination

  My issue is that the Restful response comes back in a string which is more than 8000 characters in length.

  How do i pass this into the Output buffer. The webresponse variable which I am using is an Object Type

  Note: I have only used an open API here as an example

  here is the script component code that I have been using:

  ' Declare API URL

  Dim uri As String

  uri = "https://api.spotify.com/v1/albums/0sNOF9WDwhWunNAHPD3Baj"

  ' Create Web Request for the URL

  Dim request As System.Net.WebRequest

  request = HttpWebRequest.Create(uri)

  ' Get the Response

  Dim response As WebResponse

  response = request.GetResponse()

  ' Get the Stream

  Dim dataStream As Stream

  dataStream = response.GetResponseStream()

  ' Open the stream using stream reader

  Dim reader As New StreamReader(dataStream)

  ' Read the response

  Dim webresponse As Object

  webresponse = reader.ReadToEnd()

  ' Output the Response

  With MyAPIBuffer

  .AddRow()

  .apicallid = Variables.apicallid

  .apicallname = Variables.apicallname

  .apicallresponse = webresponse

  .apicalldate = Variables.apicalldate

  End With

  ' Cleanup Streams and Response

  reader.Close()

  response.Close()

  The output I am expecting is:

  api_call_id: string guid

  api_call_name: <some global string passed from Control Flow>

  api_call_date: string datetime (passed from control flow)

  api_call_response: large ntext response

  Or is there any better way of doing this? Please assist

  Thanks