No replies to this topic

[bsmithjjatl]

Ok so I am working on converting a DTS package to an SSIS package.I am trying to query my database inside VB and then set the query result to a string. I am not 100% sure where the query result is stored post execution so I think that piece is causing this to be much more difficult that it really needs to be. The end goal is 3 "ConnectionStrings" for flat file connections. Basically i am trying to name my 3 export files with the current date(ie. //server/folder/MMDDYY_filename.csv). Im not really sure If I even need the Where statements but that was the only way I could think to export the query results to a string variable. Let me know what you think. Any comments or suggestion will be much appreciated. I have not used vb in a long long time so forgive my novice ignorance.

-Bren

Heres the code:

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

	Public Sub Main()
    	Dim SQLdb As New SqlConnection()
    	Dim SQLdr As SqlDataReader
    	SQLdb.ConnectionString = "Data Source=JJMSSRV007;Initial Catalog=jjms_app;Provider=SQLOLEDB.1;Integrated Security=SSPI;"
    	SQLdb.Open()

    	Dim sValBal As String
    	Dim sValCk As String
    	Dim sValTrn As String

    	Dim sqlBal As New SqlCommand()
    	Dim sqlCk As New SqlCommand()
    	Dim sqlTrn As New SqlCommand()

    	sqlBal.Connection = SQLdb
    	sqlCk.Connection = SQLdb
    	sqlTrn.Connection = SQLdb

    	sqlBal.CommandText = "select path + (select convert(varchar,getdate(),12)) + 'filename' From xreconnet Where user1 = 'BAL'"
    	sqlCk.CommandText = "select path + (select convert(varchar,getdate(),12)) + 'filename' From xreconnet Where user1 = 'TRN'"
    	sqlTrn.CommandText = "select path + (select convert(varchar,getdate(),12)) + 'filename' From xreconnet Where user1= 'CK'"

    	SQLdr = sqlBal.ExecuteReader
    	While SQLdr.Read()
        	sValBal = SQLdr.ToString

    	End While

    	SQLdr = sqlCk.ExecuteReader
    	While SQLdr.Read()
        	sValCk = SQLdr.ToString

    	End While

    	SQLdr = sqlTrn.ExecuteReader
    	While SQLdr.Read()
        	sValTrn = SQLdr.ToString
    	End While

    	Dts.Variables("mValBal").Value = sValBal
    	Dts.Variables("mValCk").Value = sValCk
    	Dts.Variables("mValTrn").Value = sValTrn

    	Dts.TaskResult = Dts.Results.Success
	End Sub

End Class