I have created an SSIS package that runs from the following stored procedure:
Code:
DECLARE
@SQLQuery ASVARCHAR(2000)
DECLARE
@ServerName VARCHAR(200)
SET
@ServerName ='myServerName'
SET
@SQLQuery ='DTExec /F ^"\\...\...\My_Package.dtsx^" '
EXEC
master..xp_cmdshell @SQLQuery
I then have an Access2010 application with a few buttons that run 3 procedures all set up the same way running the package using the following code:
Code:
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim sConnString As String
sConnString = "Provider=sqloledb; Server=MyServerName; Database=MyDB; Trusted_Connection=True; Integrated Security=SSPI;"
conn.Open sConnString
With cmd
.ActiveConnection = conn
.CommandType = adCmdText
.CommandText = "EXEC my_sproc;"
End With
Now I need to add text boxes to two of the buttons that trigger the procedures where a user will enter a file name. The button needs to pass that file name to the stored procedure and the stored procedure needs to then use it in the package.
Any suggestions are greatly appreciated. This is my first time doing something like this and I am a little lost.
Thanks,
Scott