-1

I have an ssis package that takes two flat files and a database tables as connections. I want to run the ssis package from command line by passing these 3 connections as command line parameters. How should I call?

After some google search I found that if we are using a DB as connection, this is how to pass. But couldn't figure out how to pass connection parameters for flat files.

DTExec.exe /F "<packagepath> /set \package.connections[MyDB].properties[ServerName];SS2K8SV01_Prod
HadoopAddict
  • 225
  • 6
  • 18
  • Can you use a stored proc? That's how I've done it. Like so: https://learn.microsoft.com/en-us/sql/integration-services/system-stored-procedures/catalog-create-execution-ssisdb-database?view=sql-server-2017 (you also need to set the parameter as the source in the properties for the connection in the package) – Jacob H May 25 '18 at 14:24
  • Would you like to directly configure the Connection Manager or are you using SSIS Variables which then drive the ConnectionString property for your connection manager? – billinkc May 25 '18 at 14:53
  • @billinkc : I would like to directly configure the connection manager and then pass the paths of files as command line parameters – HadoopAddict May 25 '18 at 15:01
  • @Jacob : No , cannot use a stored Proc in my case. – HadoopAddict May 25 '18 at 15:02

1 Answers1

1

Assume I have a flat file connection manager called FFCM and I'd like to update the ConnectionString property to C:\ssisdata\output\B.txt

dtexec.exe 
/file Package2.dtsx 
/set 
\Package.Connections[FFCM].Properties[ConnectionString];C:\ssisdata\input\B.txt

These are case sensitive values so the connection manager must be called FFCM. If there is a ffcm, this wouldn't work.

If you have to deal with spaces in the path, honestly, it's easier to rename the path than to deal with it but I believe the entire argument needs be in double quotes, not just the portion after the semicolon

dtexec.exe 
/file Package2.dtsx 
/set 
\Package.Connections[FFCM].Properties[ConnectionString];C:\ssisdata\input\B.txt
/set 
\Package.Connections[FFCM2].Properties[ConnectionString];C:\ssisdata\input\BC.txt
/set 
\Package.Connections[MyDB].Properties[ServerName];localhost

How do I determine what the thing is after the /set

By default, F4 will bring up the Properties window for whatever you have clicked on in the SSIS editor. Look for the PackagePath property of a given object and that identifies the "base" path.

enter image description here

From there, you will need to access the Expressions collection to identify the property you'd like to set ConnectionString

enter image description here

Thus making the full path we will SET via command line as 

\Package.Connections[FFCM].Properties[ConnectionString]
billinkc
  • 59,250
  • 9
  • 102
  • 159