Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Executing SSIS package with parameters and 32 bit data source from SSMS Expand / Collapse
Posted Thursday, March 14, 2013 5:02 PM

Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, December 5, 2016 8:45 AM
Points: 1,165, Visits: 1,535

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"'
+ ' /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?

Post #1431297
Posted Friday, March 15, 2013 7:33 AM



Group: General Forum Members
Last Login: Yesterday @ 1:35 AM
Points: 15,510, Visits: 13,170
Maybe SSMS uses the SQL Server account to run DTEXEC instead of your own account?

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1431529
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse