jueves, 31 de octubre de 2013

How connect to sql database in script task SSIS --Como conectarse a una base de datos sql en un script task de SSIS

  • Create a New ADO .Net Connection  "BDRepoManager"
  • Create a script task right click and edit script
 Public Sub Main()
        Dim sqlConn As SqlClient.SqlConnection       ' Object to hold connection
        Dim sqlCmd As SqlCommand                     ' SQL Command object
        Dim rdrReader As SqlDataReader               ' Data reader to hold output of command
        Dim sqlCmdText As String                     ' SQL Command text
        sqlConn = DirectCast(Dts.Connections("BDRepoManager").AcquireConnection(Dts.Transaction), SqlClient.SqlConnection)

   sqlCmdText = Dts.Variables("User::storedProcedure").Value
        ' Execute the command and put the results into the data reader
        sqlCmd = New SqlCommand(sqlCmdText, sqlConn)
        sqlCmd.CommandType = CommandType.StoredProcedure
        Dim parameters As String = Dts.Variables("User::parameters").Value
        Dim parametersArray() As String
        parametersArray = parameters.Split(",")
        Dim paramValues As String = Dts.Variables("User::paramValues").Value
        Dim paramValuesArray() As String
        paramValuesArray = paramValues.Split(",")
        For i As Integer = 0 To parametersArray.Length - 1
            sqlCmd.Parameters.Add(New SqlParameter(parametersArray(i), paramValuesArray(i)))
        Dim sda As New SqlDataAdapter()
        Dim dt As New DataTable()
        sda.SelectCommand = sqlCmd
  End Sub

No hay comentarios.:

Publicar un comentario