How can i use dtsExec command in stored procedure to execute ssis package by passing entire configuration from a table?
I am using given command line to execute ssis packages in sp in sql server.
SET @dtsExecCmd = @DTSEXECCMDPATH + ltrim(rtrim(@ssisPkgFilePath)) + '" /CONFIGFILE "' + ltrim(rtrim(@ssisCommonConfigPath)) + '"'
EXEC @result = master..xp_cmdshell @dtsExecCmd
where,
@DTSEXECCMDPATH = 'G:"Program Files (x86)""Microsoft SQL Server"110DTSBinndtexec /F "'
@ssisPkgFilePath = '\dtsx package pathpackage.dtsx'
@ssisCommonConfigPath =
'\CommonConfigurationpathConfiguration.dtsConfig'
Now i want to execute the same package by passing the configuration(present in Configuration.dtsConfig earlier) from table, how do i have to edit this command line if @config contains configuration from table.
.net
sql-server ssis msdn dtexec asked Nov 16 '18 at 6:25
Rehan HaqueRehan Haque
409
|
show 4 more comments
I am using given command line to execute ssis packages in sp in sql server.
SET @dtsExecCmd = @DTSEXECCMDPATH + ltrim(rtrim(@ssisPkgFilePath)) + '" /CONFIGFILE "' + ltrim(rtrim(@ssisCommonConfigPath)) + '"'
EXEC @result = master..xp_cmdshell @dtsExecCmd
where,
@DTSEXECCMDPATH = 'G:"Program Files (x86)""Microsoft SQL Server"110DTSBinndtexec /F "'
@ssisPkgFilePath = '\dtsx package pathpackage.dtsx'
@ssisCommonConfigPath =
'\CommonConfigurationpathConfiguration.dtsConfig'
Now i want to execute the same package by passing the configuration(present in Configuration.dtsConfig earlier) from table, how do i have to edit this command line if @config contains configuration from table.
.net
sql-server ssis msdn dtexec asked Nov 16 '18 at 6:25
Rehan HaqueRehan Haque
409
What version of SSIS? From SQL 2012 onwards there is a more sophisticated T-SQL API which means you don't need to resort to insecurexp_cmdshellhacks.
– Nick.McDermaid
Nov 20 '18 at 7:26
If you're still using.dtsConfigthen I guess it's an older version. You need to change your package to read its config from a database table, not a dtsConfig file. This is a change made in the package. But really, this is very old technology now. You should definitely not be building anything new in this version.
– Nick.McDermaid
Nov 20 '18 at 7:27
Oh.. i see110in your bin path so this is SQL 2012? You really need to upgrade to SSISDB and project deployment.
– Nick.McDermaid
Nov 20 '18 at 7:29
yes nick this is sql server 2012 and ssis is also really old ...its been in use since long
– Rehan Haque
Nov 20 '18 at 13:16
Nick as of now i'll be using sql server 2012(or 2017 in near future) only ,can you please suggest what actually has to be updated in ssis packages , so that i can read configuration in dtexec command from table(encrypted), and how big that change would be ? thanks in advance!!
– Rehan Haque
Nov 20 '18 at 13:31
|
show 4 more comments
I am using given command line to execute ssis packages in sp in sql server.
SET @dtsExecCmd = @DTSEXECCMDPATH + ltrim(rtrim(@ssisPkgFilePath)) + '" /CONFIGFILE "' + ltrim(rtrim(@ssisCommonConfigPath)) + '"'
EXEC @result = master..xp_cmdshell @dtsExecCmd
where,
@DTSEXECCMDPATH = 'G:"Program Files (x86)""Microsoft SQL Server"110DTSBinndtexec /F "'
@ssisPkgFilePath = '\dtsx package pathpackage.dtsx'
@ssisCommonConfigPath =
'\CommonConfigurationpathConfiguration.dtsConfig'
Now i want to execute the same package by passing the configuration(present in Configuration.dtsConfig earlier) from table, how do i have to edit this command line if @config contains configuration from table.
.net
sql-server ssis msdn dtexec asked Nov 16 '18 at 6:25
Rehan HaqueRehan Haque
409
I am using given command line to execute ssis packages in sp in sql server.
SET @dtsExecCmd = @DTSEXECCMDPATH + ltrim(rtrim(@ssisPkgFilePath)) + '" /CONFIGFILE "' + ltrim(rtrim(@ssisCommonConfigPath)) + '"'
EXEC @result = master..xp_cmdshell @dtsExecCmd
where,
@DTSEXECCMDPATH = 'G:"Program Files (x86)""Microsoft SQL Server"110DTSBinndtexec /F "'
@ssisPkgFilePath = '\dtsx package pathpackage.dtsx'
@ssisCommonConfigPath =
'\CommonConfigurationpathConfiguration.dtsConfig'
Now i want to execute the same package by passing the configuration(present in Configuration.dtsConfig earlier) from table, how do i have to edit this command line if @config contains configuration from table.
.net
sql-server ssis msdn dtexec .net
sql-server ssis msdn dtexec asked Nov 16 '18 at 6:25
Rehan HaqueRehan Haque
409
asked Nov 16 '18 at 6:25
Rehan HaqueRehan Haque
409
edited Nov 20 '18 at 5:55
Rehan Haque
asked Nov 16 '18 at 6:25
Rehan HaqueRehan Haque
409
asked Nov 16 '18 at 6:25
Rehan HaqueRehan Haque
409
asked Nov 16 '18 at 6:25
Rehan HaqueRehan Haque
409
409
What version of SSIS? From SQL 2012 onwards there is a more sophisticated T-SQL API which means you don't need to resort to insecurexp_cmdshellhacks.
– Nick.McDermaid
Nov 20 '18 at 7:26
If you're still using.dtsConfigthen I guess it's an older version. You need to change your package to read its config from a database table, not a dtsConfig file. This is a change made in the package. But really, this is very old technology now. You should definitely not be building anything new in this version.
– Nick.McDermaid
Nov 20 '18 at 7:27
Oh.. i see110in your bin path so this is SQL 2012? You really need to upgrade to SSISDB and project deployment.
– Nick.McDermaid
Nov 20 '18 at 7:29
yes nick this is sql server 2012 and ssis is also really old ...its been in use since long
– Rehan Haque
Nov 20 '18 at 13:16
Nick as of now i'll be using sql server 2012(or 2017 in near future) only ,can you please suggest what actually has to be updated in ssis packages , so that i can read configuration in dtexec command from table(encrypted), and how big that change would be ? thanks in advance!!
– Rehan Haque
Nov 20 '18 at 13:31
|
show 4 more comments
What version of SSIS? From SQL 2012 onwards there is a more sophisticated T-SQL API which means you don't need to resort to insecurexp_cmdshellhacks.
– Nick.McDermaid
Nov 20 '18 at 7:26
If you're still using.dtsConfigthen I guess it's an older version. You need to change your package to read its config from a database table, not a dtsConfig file. This is a change made in the package. But really, this is very old technology now. You should definitely not be building anything new in this version.
– Nick.McDermaid
Nov 20 '18 at 7:27
Oh.. i see110in your bin path so this is SQL 2012? You really need to upgrade to SSISDB and project deployment.
– Nick.McDermaid
Nov 20 '18 at 7:29
yes nick this is sql server 2012 and ssis is also really old ...its been in use since long
– Rehan Haque
Nov 20 '18 at 13:16
Nick as of now i'll be using sql server 2012(or 2017 in near future) only ,can you please suggest what actually has to be updated in ssis packages , so that i can read configuration in dtexec command from table(encrypted), and how big that change would be ? thanks in advance!!
– Rehan Haque
Nov 20 '18 at 13:31
What version of SSIS? From SQL 2012 onwards there is a more sophisticated T-SQL API which means you don't need to resort to insecure
xp_cmdshell hacks.– Nick.McDermaid
Nov 20 '18 at 7:26
What version of SSIS? From SQL 2012 onwards there is a more sophisticated T-SQL API which means you don't need to resort to insecure
xp_cmdshell hacks.– Nick.McDermaid
Nov 20 '18 at 7:26
If you're still using
.dtsConfig then I guess it's an older version. You need to change your package to read its config from a database table, not a dtsConfig file. This is a change made in the package. But really, this is very old technology now. You should definitely not be building anything new in this version.– Nick.McDermaid
Nov 20 '18 at 7:27
If you're still using
.dtsConfig then I guess it's an older version. You need to change your package to read its config from a database table, not a dtsConfig file. This is a change made in the package. But really, this is very old technology now. You should definitely not be building anything new in this version.– Nick.McDermaid
Nov 20 '18 at 7:27
Oh.. i see
110 in your bin path so this is SQL 2012? You really need to upgrade to SSISDB and project deployment.– Nick.McDermaid
Nov 20 '18 at 7:29
Oh.. i see
110 in your bin path so this is SQL 2012? You really need to upgrade to SSISDB and project deployment.– Nick.McDermaid
Nov 20 '18 at 7:29
yes nick this is sql server 2012 and ssis is also really old ...its been in use since long
– Rehan Haque
Nov 20 '18 at 13:16
yes nick this is sql server 2012 and ssis is also really old ...its been in use since long
– Rehan Haque
Nov 20 '18 at 13:16
Nick as of now i'll be using sql server 2012(or 2017 in near future) only ,can you please suggest what actually has to be updated in ssis packages , so that i can read configuration in dtexec command from table(encrypted), and how big that change would be ? thanks in advance!!
– Rehan Haque
Nov 20 '18 at 13:31
Nick as of now i'll be using sql server 2012(or 2017 in near future) only ,can you please suggest what actually has to be updated in ssis packages , so that i can read configuration in dtexec command from table(encrypted), and how big that change would be ? thanks in advance!!
– Rehan Haque
Nov 20 '18 at 13:31
|
show 4 more comments
1 Answer
1
active
oldest
votes
I'm assuming you are not going to upgrade to project deployment for these package and stick with package deployment.
There is no DTEXEC command line switch that lets you define a SQL Server table to load a config from.
The easiest way to use package configuration from a SQL Server table is to open the package up in SSDT, go the SSIS/Package Configurations and set it up. Then you don't need to pass anything on the DTEXEC command line at all.
https://docs.microsoft.com/en-us/sql/integration-services/package-configurations?view=sql-server-2014#sql-server
If you don't want to make a package change and you insist on passing it through DTEXEC then I suppose you could write a wrapper that pulls the config data out of a table and one by one applies those configs using the /SET switch.
But I really encourage and recommend you to upgrade these packages to the project deployment model. Don't waste your time with this old method. You're just introducing technical debt.
You should also know that xp_cmdshell is generally considered to be a security issue.
answered Nov 21 '18 at 12:10
Nick.McDermaidNick.McDermaid
12k32756
Thanks for the info . One more thing i wanted to know was if i choose to use package configuration from sql server table, can i keep the connection string in table as encrypted ?
– Rehan Haque
Nov 21 '18 at 13:18
Not sure - you’d need to experiment. I always try to use windows authentication so nothing needs to be encrypted.
– Nick.McDermaid
Nov 21 '18 at 13:30
and we can set the authentication type while setting up configuration for packge ?
– Rehan Haque
Nov 21 '18 at 13:36
Authentication type is defined in the connection string. Which is defined at design time in SSDT and optionally in a config.
– Nick.McDermaid
Nov 21 '18 at 13:53
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53332530%2fhow-can-i-use-dtsexec-command-in-stored-procedure-to-execute-ssis-package-by-pas%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
I'm assuming you are not going to upgrade to project deployment for these package and stick with package deployment.
There is no DTEXEC command line switch that lets you define a SQL Server table to load a config from.
The easiest way to use package configuration from a SQL Server table is to open the package up in SSDT, go the SSIS/Package Configurations and set it up. Then you don't need to pass anything on the DTEXEC command line at all.
https://docs.microsoft.com/en-us/sql/integration-services/package-configurations?view=sql-server-2014#sql-server
If you don't want to make a package change and you insist on passing it through DTEXEC then I suppose you could write a wrapper that pulls the config data out of a table and one by one applies those configs using the /SET switch.
But I really encourage and recommend you to upgrade these packages to the project deployment model. Don't waste your time with this old method. You're just introducing technical debt.
You should also know that xp_cmdshell is generally considered to be a security issue.
answered Nov 21 '18 at 12:10
Nick.McDermaidNick.McDermaid
12k32756
Thanks for the info . One more thing i wanted to know was if i choose to use package configuration from sql server table, can i keep the connection string in table as encrypted ?
– Rehan Haque
Nov 21 '18 at 13:18
Not sure - you’d need to experiment. I always try to use windows authentication so nothing needs to be encrypted.
– Nick.McDermaid
Nov 21 '18 at 13:30
and we can set the authentication type while setting up configuration for packge ?
– Rehan Haque
Nov 21 '18 at 13:36
Authentication type is defined in the connection string. Which is defined at design time in SSDT and optionally in a config.
– Nick.McDermaid
Nov 21 '18 at 13:53
add a comment |
I'm assuming you are not going to upgrade to project deployment for these package and stick with package deployment.
There is no DTEXEC command line switch that lets you define a SQL Server table to load a config from.
The easiest way to use package configuration from a SQL Server table is to open the package up in SSDT, go the SSIS/Package Configurations and set it up. Then you don't need to pass anything on the DTEXEC command line at all.
https://docs.microsoft.com/en-us/sql/integration-services/package-configurations?view=sql-server-2014#sql-server
If you don't want to make a package change and you insist on passing it through DTEXEC then I suppose you could write a wrapper that pulls the config data out of a table and one by one applies those configs using the /SET switch.
But I really encourage and recommend you to upgrade these packages to the project deployment model. Don't waste your time with this old method. You're just introducing technical debt.
You should also know that xp_cmdshell is generally considered to be a security issue.
answered Nov 21 '18 at 12:10
Nick.McDermaidNick.McDermaid
12k32756
Thanks for the info . One more thing i wanted to know was if i choose to use package configuration from sql server table, can i keep the connection string in table as encrypted ?
– Rehan Haque
Nov 21 '18 at 13:18
Not sure - you’d need to experiment. I always try to use windows authentication so nothing needs to be encrypted.
– Nick.McDermaid
Nov 21 '18 at 13:30
and we can set the authentication type while setting up configuration for packge ?
– Rehan Haque
Nov 21 '18 at 13:36
Authentication type is defined in the connection string. Which is defined at design time in SSDT and optionally in a config.
– Nick.McDermaid
Nov 21 '18 at 13:53
add a comment |
I'm assuming you are not going to upgrade to project deployment for these package and stick with package deployment.
There is no DTEXEC command line switch that lets you define a SQL Server table to load a config from.
The easiest way to use package configuration from a SQL Server table is to open the package up in SSDT, go the SSIS/Package Configurations and set it up. Then you don't need to pass anything on the DTEXEC command line at all.
https://docs.microsoft.com/en-us/sql/integration-services/package-configurations?view=sql-server-2014#sql-server
If you don't want to make a package change and you insist on passing it through DTEXEC then I suppose you could write a wrapper that pulls the config data out of a table and one by one applies those configs using the /SET switch.
But I really encourage and recommend you to upgrade these packages to the project deployment model. Don't waste your time with this old method. You're just introducing technical debt.
You should also know that xp_cmdshell is generally considered to be a security issue.
answered Nov 21 '18 at 12:10
Nick.McDermaidNick.McDermaid
12k32756
I'm assuming you are not going to upgrade to project deployment for these package and stick with package deployment.
There is no DTEXEC command line switch that lets you define a SQL Server table to load a config from.
The easiest way to use package configuration from a SQL Server table is to open the package up in SSDT, go the SSIS/Package Configurations and set it up. Then you don't need to pass anything on the DTEXEC command line at all.
https://docs.microsoft.com/en-us/sql/integration-services/package-configurations?view=sql-server-2014#sql-server
If you don't want to make a package change and you insist on passing it through DTEXEC then I suppose you could write a wrapper that pulls the config data out of a table and one by one applies those configs using the /SET switch.
But I really encourage and recommend you to upgrade these packages to the project deployment model. Don't waste your time with this old method. You're just introducing technical debt.
You should also know that xp_cmdshell is generally considered to be a security issue.
answered Nov 21 '18 at 12:10
Nick.McDermaidNick.McDermaid
12k32756
answered Nov 21 '18 at 12:10
Nick.McDermaidNick.McDermaid
12k32756
answered Nov 21 '18 at 12:10
Nick.McDermaidNick.McDermaid
12k32756
answered Nov 21 '18 at 12:10
Nick.McDermaidNick.McDermaid
12k32756
12k32756
Thanks for the info . One more thing i wanted to know was if i choose to use package configuration from sql server table, can i keep the connection string in table as encrypted ?
– Rehan Haque
Nov 21 '18 at 13:18
Not sure - you’d need to experiment. I always try to use windows authentication so nothing needs to be encrypted.
– Nick.McDermaid
Nov 21 '18 at 13:30
and we can set the authentication type while setting up configuration for packge ?
– Rehan Haque
Nov 21 '18 at 13:36
Authentication type is defined in the connection string. Which is defined at design time in SSDT and optionally in a config.
– Nick.McDermaid
Nov 21 '18 at 13:53
add a comment |
Thanks for the info . One more thing i wanted to know was if i choose to use package configuration from sql server table, can i keep the connection string in table as encrypted ?
– Rehan Haque
Nov 21 '18 at 13:18
Not sure - you’d need to experiment. I always try to use windows authentication so nothing needs to be encrypted.
– Nick.McDermaid
Nov 21 '18 at 13:30
and we can set the authentication type while setting up configuration for packge ?
– Rehan Haque
Nov 21 '18 at 13:36
Authentication type is defined in the connection string. Which is defined at design time in SSDT and optionally in a config.
– Nick.McDermaid
Nov 21 '18 at 13:53
Thanks for the info . One more thing i wanted to know was if i choose to use package configuration from sql server table, can i keep the connection string in table as encrypted ?
– Rehan Haque
Nov 21 '18 at 13:18
Thanks for the info . One more thing i wanted to know was if i choose to use package configuration from sql server table, can i keep the connection string in table as encrypted ?
– Rehan Haque
Nov 21 '18 at 13:18
Not sure - you’d need to experiment. I always try to use windows authentication so nothing needs to be encrypted.
– Nick.McDermaid
Nov 21 '18 at 13:30
Not sure - you’d need to experiment. I always try to use windows authentication so nothing needs to be encrypted.
– Nick.McDermaid
Nov 21 '18 at 13:30
and we can set the authentication type while setting up configuration for packge ?
– Rehan Haque
Nov 21 '18 at 13:36
and we can set the authentication type while setting up configuration for packge ?
– Rehan Haque
Nov 21 '18 at 13:36
Authentication type is defined in the connection string. Which is defined at design time in SSDT and optionally in a config.
– Nick.McDermaid
Nov 21 '18 at 13:53
Authentication type is defined in the connection string. Which is defined at design time in SSDT and optionally in a config.
– Nick.McDermaid
Nov 21 '18 at 13:53
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53332530%2fhow-can-i-use-dtsexec-command-in-stored-procedure-to-execute-ssis-package-by-pas%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
What version of SSIS? From SQL 2012 onwards there is a more sophisticated T-SQL API which means you don't need to resort to insecure
xp_cmdshellhacks.– Nick.McDermaid
Nov 20 '18 at 7:26
If you're still using
.dtsConfigthen I guess it's an older version. You need to change your package to read its config from a database table, not a dtsConfig file. This is a change made in the package. But really, this is very old technology now. You should definitely not be building anything new in this version.– Nick.McDermaid
Nov 20 '18 at 7:27
Oh.. i see
110in your bin path so this is SQL 2012? You really need to upgrade to SSISDB and project deployment.– Nick.McDermaid
Nov 20 '18 at 7:29
yes nick this is sql server 2012 and ssis is also really old ...its been in use since long
– Rehan Haque
Nov 20 '18 at 13:16
Nick as of now i'll be using sql server 2012(or 2017 in near future) only ,can you please suggest what actually has to be updated in ssis packages , so that i can read configuration in dtexec command from table(encrypted), and how big that change would be ? thanks in advance!!
– Rehan Haque
Nov 20 '18 at 13:31