Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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
Author
Message
Posted Thursday, March 14, 2013 5:02 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:08 AM
Points: 922, Visits: 1,334
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
Post #1431297
Posted Friday, March 15, 2013 7:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:28 AM
Points: 13,630, Visits: 11,501
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 LessThanDot.

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

Add to briefcase

Permissions Expand / Collapse