sqlpackage table list

Question

I'm trying to run sqlpackage from powershell with a dynamic list of tables but am getting an error putting the list of tables into a var. I need to put the table list into a var because it is going to be generated dynamically.

The following code works just fine:

.\sqlpackage.exe `
    /a:Export `
    /scs:"Data Source=$($ServerName);Initial Catalog=$($SourceDbName);User ID=my_user;Password=my_password" `
    /tf:$BacPacFileName `
    /p:TableData=[dbo].[QRTZ_CALENDARS] /p:TableData=[dbo].[QRTZ_SCHEDULER_STATE]

However, when I put the list of tables in a var, it fails:

$tableList = "/p:TableData=[dbo].[QRTZ_CALENDARS] /p:TableData=[dbo].[QRTZ_SCHEDULER_STATE]"

.\sqlpackage.exe `
    /a:Export `
    /scs:"Data Source=$($ServerName);Initial Catalog=$($SourceDbName);User ID=my_user;Password=my_password" `
    /tf:$BacPacFileName `
    $($tableList)

Error text:

.\sqlpackage.exe : Argument 'TableData' has an invalid value:
'[dbo].[QRTZ_CALENDARS] /p:TableData=[dbo].[QRTZ_SCHEDULER_STATE]'.
Table to extract data from must be specified using the two-part table
name format: schema_name.table_identifier.
At line:1 char:1
+ .\sqlpackage.exe `
+ ~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (Argument 'T...ble_identifier.:String) [], RemoteException
    + FullyQualifiedErrorId : NativeCommandError  Incorrect syntax near /.

Answer 1

The value of $tableList is passed as a single argument, not a list of arguments. What you actually want to do here is called splatting:

$cs = "Data Source=${ServerName};Initial Catalog=${SourceDbName};" +
      'User ID=my_user;Password=my_password'

$params = '/a:Export',
          "/scs:`"${cs}`"",
          "/tf:${BacPacFileName}",
          '/p:TableData=[dbo].[QRTZ_CALENDARS]',
          '/p:TableData=[dbo].[QRTZ_SCHEDULER_STATE]'

.\sqlpackage.exe @params