Post reply

Executing SSIS package with parameters and 32 bit data source from SSMS

  • March 14, 2013 at 5:02 pm

    #271658

    Greetings,

    I have a SSIS package that imports data via a 32 bit data source. I am able to run this from the command line on the server, but am unable to execute if from SSMS. The package property Run64BitRuntime is set to False.

    Here is the syntax when I run it successfully from command line from C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn

    dtexec.exe /FILE "C:\Program Files\Microsoft SQL Server\100\DTS\Packages\MAS90Hybrid\MAS90Hybrid.dtsx" /CHECKPOINTING OFF /REPORTING EWCDI /set \package.variables[StartDate].Value;2013-02-01 /set \package.variables[EndDate].Value;2013-02-28 /set \package.variables[CountryCode].Value;AF /set \package.variables[ODBCConnection].Value;"AFG"

    Here is the syntax I'm using in SSMS:

    declare @param varchar(4000)

    declare @country varchar(2)

    declare @odbc varchar(10)

    SET @country = 'AF'

    SET @odbc = 'AFG'

    SET @param = 'C:\"Program Files (x86)"\"Microsoft SQL Server"\100\DTS\Binn\DTExec.exe /F "C:\Program Files\Microsoft SQL Server\100\DTS\Packages\MAS90Hybrid\MAS90Hybrid.dtsx"'

    + ' /CHECKPOINTING OFF /REPORTING EWCDI '

    + ' /set \package.variables[StartDate].Value;2013-02-01'

    + ' /set \package.variables[EndDate].Value;2013-02-28'

    + ' /set \package.variables[CountryCode].Value;' + @country

    + ' /set \package.variables[ODBCConnection].Value;"' + @odbc + '"'

    EXEC master..xp_cmdshell @param

    I believe the main error "Description: System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified" is cause by not being able to access the 32 bit driver. Why can I run this from the command line, but not SSMS? I'm trying to run it in SSMS because I would like to put it into a stored procedure and have it called by an end user. Any thoughts or suggestions on other methods?

    Thanks,

    Tim

  • March 15, 2013 at 7:33 am

    #1597488

    Maybe SSMS uses the SQL Server account to run DTEXEC instead of your own account?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply