2

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
eek
  • 688
  • 2
  • 21
  • 32

1 Answers1

1

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.

Community
  • 1
  • 1
billinkc
  • 59,250
  • 9
  • 102
  • 159
  • The date will be today's date. I would love to do as much of this as possible in SQL server. I keep getting the dreaded "Option 12.0 is not valid" error when I run this using the command line and a 32 bit version of dtexec. Would I be able to get the SQL Server Agent to run this instead? – eek Jun 11 '12 at 17:04
  • I have added my response in-line. – billinkc Jun 11 '12 at 19:22
  • I have actually figured out how to get this to run using a SQL agent job. I have, however, not been able to change the file name dynamically in SSIS yet. I am using a script task to do it, but I'm having some trouble setting the variables. I'm almost there. – eek Jun 11 '12 at 19:30
  • Right click on your Flat File Connection Manager and select Properties. Find Expressions and click the ellipses (...) Select the ConnectionString in the left drop down menu and the right hand would need the variable `@[User::ExcelSource]` – billinkc Jun 11 '12 at 20:22
  • Thanks, that's what I did. I am having trouble setting the variables using a script task now. I have the script task connected to the data flow task in the control flow window. Both execute successfully, but I'm not sure if SSIS is executing them in order correctly. Is there anything I need to do besides have them connected with an arrow? – eek Jun 12 '12 at 12:21
  • You can set variable values in script tasks, but quite often I find using an expression, possibly with a second variable to be cleaner. Mind opening a new question or at least editing the existing one so future readers aren't slogging through all the comments? – billinkc Jun 12 '12 at 13:50
  • I got it working! I modified the ExcelFilePath to evaluate as an expression. The trick was to select a "dummy" file as your default variable value and then set the actual variable using the Script Task. The variable must have a default value or else the connection manager won't work initially. Thanks. – eek Jun 12 '12 at 14:21