execute ssis

Hi,

In the SSIS package, I have made the filename dynamic i.e. set as a variable.

How is it possible to do the same thing for the database name in the oledb connection?

I looked at the connectionString property for the OLEDB connection. ConnectionString looks long and the databasename is in this text.

Not sure how to make this databasename inside the connectionstring dynamic?

Thanks

hi,
What is wrong with this please?
I am passing two variables to execute a ssis package.
Thanks

set @.cmd = 'dtexec /f ' + @.FullPackagePath + ' /set \Package.Variables[User::FileName].Properties[Value];"' + @.FullFilePath + '"' +
' \Package.Variables[User::ConnectionPath].Properties[Value];"' + @.ConnectionPath + '"'
print @.cmd

error is:
Option "\Package.Variables[User::ConnectionPath].Properties[Value];Data Source=server1\databasename" is not valid.

please note I just retyped the data source name here.

|||

Get the raw command line working first then port it to SQL version. You are setting two properties, so try giving each it's own /SET. Also try quoting the property ID string bit.

/FILE "P:\My Documents\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW /SET "\Package.Variables[User::ConnectionPath].Properties[Value]";"Var Value" /SET "\Package.Variables[User::ConnectionPath].Properties[Value]";"XX XX"

I just mocked that up using DTEXecUI, as it can almost act like a command line builder tool for you.

|||

Hi,

It works if I only pass the filename variable but not with both parameters.

As you see I am using the /set option.

Can you see what is wrong with what I sent initially please?

Thanks

|||

You can just replace the entire connection string. Perhaps save the package with a default connection in a variable and then use an expression and REPLACE to change the place holder database for the real one, or just save a connnection string wout the database and add in via and expression. So I guess the trick is to manage the connection string differently, perhaps as sections, such that you can construct what you need with expressions latter. Not ideal, but no doubt driven by the nature of connection types, and connection string properties rather than the known common properties.

If using configurations you can change the InitialCatalog property, but unfortunatey this is not available as the target for a property expression.

|||

I can see you used one set option, but passed two sets against it.

Look at my example again and you will see that I have two /SET options in there, one for each property to be set.

Error -

/FILE "P:\My Documents\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW /SET "\Package.Variables[User::ConnectionPath].Properties[Value]";"Var Value" "\Package.Variables [User::ConnectionPath].Properties[Value]";"XX XX"

Option "\Package.Variables[User::ConnectionPath].Properties[Value];XX XX" is not valid.

OK -

/FILE "P:\My Documents\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW /SET "\Package.Variables[User::ConnectionPath].Properties[Value]";"Var Value" /SET "\Package.Variables[User::ConnectionPath].Properties[Value]";"XX XX"

|||

I am getting close to what I was after.

Basically I am passing a variable but not sure how to pass two variables. See below. Do you see what is wrong with the below query please?

It works if filename variable is used but not if the second variable is included.

set @.cmd = 'dtexec /f ' + @.FullPackagePath + ' /set \Package.Variables[User::FileName].Properties[Value];"' + @.FullFilePath + '"' +

' /set \Package.Variables[User::ConnectionPath].Properties[Value];"' + @.ConnectionPath + '"'

print @.cmd

|||

Get the same error.

This is what I have:

set @.cmd = 'dtexec /f ' + @.FullPackagePath + ' /set \Package.Variables[User::FileName].Properties[Value];"' + @.FullFilePath + '"' +

'/set \Package.Variables[User::ConnectionPath].Properties[Value];"' + @.ConnectionPath + '"'

|||Can you show us the result of "print @.cmd"?|||

dtexec /f d:\sysappl\CEM\SSIS\Imports\Trades\TradeCreds.dtsx /set \Package.Variables[User::FileName].Properties[Value];"d:\ApplData\CEM\WorkingTemp\CollateralEx.csv"

/set \Package.Variables[User::ConnectionPath].Properties[Value];"Data Source=server1\instance1, 2025;Initial Catalog=database1;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"

|||What is this?

Data Source=server1\instance1, 2025;|||

I replaced the actual servername\instance

|||

arkiboys wrote:

I replaced the actual servername\instance

What is ", 2025"|||

port no.
This is how I connect to the database.

|||

I think I worked it out:

set @.cmd = 'dtexec /f ' + @.FullPackagePath +

' /set \Package.Variables[User::FileName].Properties[Value];"' + @.FullFilePath + '"

/set \Package.Variables[User::ConnectionPath].Properties[Value];"' + @.ConnectionPath + '"'

print @.cmd