Results 1 to 4 of 4
  1. #1
    scottdg is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2015
    Posts
    31

    Passing Access variable to Stored procedure running package

    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

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    In general, you'd add the variables as input parameters instead of declaring them:

    Code:
    ALTER PROCEDURE [dbo].[my_sproc] 
    
    @FileName As varchar(100)
    
    AS
    
    your process here, using @FileName where you want the input used
    Then call it

    Exec procDriverSheets 'FileNameHere'

    In your code that would look like:

    .CommandText = "EXEC my_sproc '" & Me.TextboxName & "'"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    scottdg is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2015
    Posts
    31
    I altered my procedure and it works with the temporary value in the procedure for @FileName. I am not sure now how to get the value from Access into the @FileName variable though.

    Code:
    DECLARE
    @cmd VARCHAR(1000)
    DECLARE
    @SSISPath VARCHAR(1000)
    DECLARE
    @FilePath VARCHAR(1000)
    DECLARE
    @FileName VARCHAR(1000)
    SET
    @SSISPath ='\\Server\...\...\...\...\Package.dtsx'
    SET
    @FilePath ='\Server\...\...\...\'
    SET
    @FileName ='myImportFile.txt'--temp value - need to get value from Access form text box
    SELECT
    @cmd ='dtexec /f "'+ @SSISPath +'"'
    SELECT
    @cmd = @cmd +' /SET \Package.Variables[User::FileName].Properties[Value];"'+ @FilePath + @FileName +'"'
    EXEC
    master..xp_cmdshell @cmd

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Was my code not clear enough? What comes before what you posted in the SP? You need to declare @FileName earlier, before AS, to make it an input parameter.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 08-29-2012, 08:01 AM
  2. Running a shell command from a stored procedure.
    By sstrauss87 in forum SQL Server
    Replies: 3
    Last Post: 03-01-2012, 04:39 PM
  3. Replies: 4
    Last Post: 01-03-2012, 08:11 PM
  4. Passing variable values to Stored Procedure
    By rodrigopcnet in forum Access
    Replies: 1
    Last Post: 04-14-2010, 10:35 AM
  5. Replies: 1
    Last Post: 04-13-2010, 12:18 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums