I have created an SSIS package that imports an Excel file into my database. I have created a variable that I would like to use as the Excel filepath for the excel connection manager.
The name of the variable in my SSIS package is "ExcelSource" and it is supposed to represent the full path. I would like to eventually set this dynamically because the filename contains a date.
What is the T-SQL code to run this? Here is what I have so far:
DECLARE @ssisstr VARCHAR(8000)
, @packagename VARCHAR(200)
, @servername VARCHAR(100)
DECLARE @params VARCHAR(8000)
--my package name
SET @packagename = 'MyPackage'
--my server name
SET @servername = 'MYCOMPUTER\MYSERVER'
SET @params = '/set \package.variables[ExcelSource].Value;"\"Y:\excelFile\Test File - June 11 2012.xlsx\""'
SET @ssisstr = 'dtexec /sq ' + @packagename + ' /ser ' + @servername + ' '
SET @ssisstr = @ssisstr + @params
DECLARE @returncode INT
EXEC @returncode = xp_cmdshell @ssisstr
SELECT @returncode
The problem you're going to run into is escaping of values with xp_cmdshell. I could not get around that for a different problem I had with running packages with complex commandline arguments. If someone can provide information to the contrary, let me know and I will amend/remove my response.
What I can suggest as an alternate means of solving your problem would be to either let your package determine what the current file is or use a different mechanism for directing that behaviour.
Package, know thyself
This approach is my preferred method. You provide the intelligence to your package to solve the problem. How do you know what the correct Excel file is? You state it has a date in it so how do you know what that date is?
If it's today's date, you can use an expression on the variable something to paste in the current date in the supplied format.
Is it the only file in a folder? Then use a ForEach enumerator of type file to identify all the .xlsx files out there. This question and most excellent answer ;) describe how to use SSIS to import the most recent CSV. It'd be a trivial change to find the most recent Excel file string fileMask = "*.xlsx";
If you have a business rule describing how to determine the right file, I'd be happy to provide insight on how you could use SSIS to implement said rule.
Tell me what you want
The other option is to use external configuration to supply the run-time value. SSIS provides for a number of out of the box configuration options. I'm rather partial to use SQL Server for this purpose but your options are
- SQL Server table
- XML file
- Environment variable
- Registry value
- Parent Package
The last 3 are special use cases in my mind and not particularly handy for your problem but for completeness I've listed them. Whatever configuration option you use, it should be a simple matter to click SSIS, Package Configuration, check the enable configuration button and use the wizard to set up your configuration type.
A second option for using external configuration is to do what you're doing, supplying command-line options to control package behaviour (no package changes required). Instead, you trade in the xp_commandshell for some custom PowerShell. I think PS was only an option from 2008+ but you could write a fairly simple script to import the SSIS object model, create an instance of the Application, open the existing package, apply the command line parameters and run the Application. I could probably cobble something together based on the $app and $package bits from the answer over here
Edits
1) The reason you are seeing "Option 12.0 is not valid" is due to xp_cmdshell being greedy and eagerly parsing the spaces in the command line options as separate arguments. If you start searching on limitations of xp_cmdshell you'll get plenty of hits where spaces in arguments cause problems.
2) To the best of my ability to understand, SQL Agent jobs are static things. It'd be awesome to be able to configure them to call whatever steps (sql, ssis, etc) with variable valued parameters (things evaluated at run-time) but generally speaking, I haven't found a clean means of doing so.
3) If you're intent on not changing the package to determine what the "right" file is, using configurations or rolling your own invocation method (PS is an job step type in SQL Agent), you could try a low-tech solution of using your existing logic to build out the dtexec call but have that all in a .bat file. xp_cmdshell then calls the batch file which should not have the trouble of handling the space in the argument name.
