December 14, 2020 at 8:13 pm
I've purchased a couple of books, worked through a number of SSIS tutorials, googled a bunch, and spent a lot of hours learning about the various SSIS widgets that are available for use. However, I'm unable to find a way to pass a database connection string via the dtexec.exe command line. (This link https://www.dutchdatadude.com/passing-command-line-settings-to-sql-server-integration-services-ssis-packages-using-dtexec-on-linux/ is the closest I've found, but it only describes the dtexec.exe part of it. I need to know what needs to be configured in the SSIS package to accept the parameter.) I'm just looking for a simple "Hello World" tutorial. For someone who is seasoned in SSIS, I feel sure this is a pretty simple task, but I'm essentially an SSIS newbie with limited Visual Studio skills.
I'm looking for something as simple as an SSIS package that would execute "select @@version" on whichever instance, based on a command line parameter passed to dtexec.exe.
All help is appreciated.
December 14, 2020 at 9:07 pm
Does this help?
https://www.sqlshack.com/an-overview-of-dtexec-utility-in-ssis/
It would probably be much easier to deploy to the Integration Services Catalog - where the connections are exposed and can be modified as needed when you setup an agent job or even in the configuration in the catalog.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 14, 2020 at 9:42 pm
Thanks for the post. I ran across the first link before I posted here. Unless I'm missing something, neither of the links explains how the command line connects to whatever has to be present in the Package. (That's probably my real problem. Parameter and variables are just mixed up in my mind. I've followed a number of tutorials, not related to passing database connection strings, and I often have glitches when dealing with variables and parameters.)
I'd like to avoid the Catalog because (to me) that brings more complexity (another database, a different deployment model, etc.), and I'm already having problems getting this to work. I'm a command line guy, so I am hoping to just pass some database connection strings from a PowerShell script.
December 14, 2020 at 9:53 pm
Well - the integration services catalog provides a location to store and manage your projects, as well as the ability to schedule them using SQL Server agent. This also gives you the ability to define Proxy accounts and use those proxy accounts to run the packages - providing only the necessary permissions needed for that process.
Either way...to set those on the command line is documented, you just need to use /par (or /parameter) and specify the parameter or variable to be modified. You cannot modify project parameters unless you are using the catalog and the /ISServer parameter of dtexec though...so you will be limited to package parameters and variables.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 15, 2020 at 11:29 am
We avoid using SQL Server agent (it is unreliable in our shop, stopping on a larger server farm without warning). We use PowerShell-based Scheduled Tasks instead. Scheduled Tasks give us fewer places (one per server) to look for execution logs (instead of potentially one per instance).
>Either way...to set those on the command line is documented, you just need to use /par (or /parameter)
I would agree with that. What I'm having trouble with is configuring the *Package* to handle a parameter for a database connection string, i.e., the "plumbing" that connects the command line to the Package.
December 15, 2020 at 7:02 pm
You don't need to 'configure' anything...this article shows how to set the connection at run time: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/5d8644d3-3235-41dc-9e16-bbe98fceac6a/dtexecexe-set-projectparams-in-project-deployment-model?forum=sqlintegrationservices
In that example - they are executing the project by referencing the .ispac file.
SQL Server Agent should not be unreliable...it is just a service that runs on the server. If that service is failing - or not starting - or something else then you have a bigger issue with your environment that should be addressed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 16, 2020 at 4:48 pm
Thanks for the link, but it looks as if .ispac is a binary file and is part of the Project Deployment Model, if I am not mistaken. I would like to use the Package Deployment Model, if at all possible.
>SQL Server Agent should not be unreliable
I would agree, SQL Server Agent *should* not be unreliable. However, Windows is Windows, and it's been my experience that the most unreliable part of SQL Server is Windows (quite often due to patching and/or Cyber Security software). The database engine itself tends to be "rock solid," and I've enjoyed working with it for a number of years. I've seen unreliable SQL Server Agent services in multiple shops. For the processing in my current shop, we just don't need SQL Server Agent because Scheduled Tasks will meet our needs. (We leave SQL Server Agent "turned on," and if it happens to go down, no critical processing is dependent upon it, and the "on call" phone doesn't ring.) It's great that SQL Server Agent works for you. I wish I could say the same, but we got tired of chasing it. The mandated Cyber Security software is often the culprit for us. So, whatever we "fix" today will likely break again "tomorrow" whenever a new version of the Cyber Security software is released. The Cyber Security software attacks the database engine as well, from time to time, but the mandate prevents removal of the software.
I've tried working through this tutorial (https://docs.microsoft.com/en-us/sql/integration-services/lesson-5-2-enabling-and-configuring-package-configurations?view=sql-server-ver15), and it seems to point me in a workable direction (although not exactly the solution I had hoped for) but I'm not getting the database connection string to work when using an XML file, rather than a command line parameter.
December 16, 2020 at 5:12 pm
I would recommend that you switch over to project deployment - build out the integration services catalog - and deploy your packages to the catalog. You can then use the /ISServer parameter on your command line and have access to modifying the parameters and connections.
Sorry - but it seems to me that accepting that your security platform breaks a required component of SQL Server is not going to be a good thing in the long run. Patching should not affect SQL Server agent...unless it is set to manual in the service applet (which - for some unknown reason is the default). Any security software should have filters applied so it does not affect database files - and it seems your security software has not been configured that way. Most likely then - it isn't configured to ignore mdf/ldf/ndf files and that is going to cause performance issues (at a minimum) and potentially corrupt your databases.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 16, 2020 at 8:20 pm
I do not understand why you having issues with the link you provided. It is quite clear.
SSIS Package (call it myfile.DTSX) - you create a connection manager to your database - Name it MasterSQL and set it to "Data Source=myserver;User ID=myuser;Initial Catalog=mydb;Password=mypassword"
now on your command line you wish to change this connection string to point to another server - and using a different user
your command line (in package deployment mode) becomes
dtexec /F myfile.dtsx /Conn "MasterSQL";"Data Source=MyProductionServer;User ID=PRODUSER;Initial Catalog=master;Password=PRODPassword"
you could also do it using a variable instead.
same SSIS package.
create variable type string - name it mydbconnstr and set it to set it to "Data Source=myserver;User ID=myuser;Initial Catalog=mydb;Password=mypassword"
on the properties tab of the connection you have a expressions line - by selecting the "..." on it you get a pop-up where you can assign particular properties of to variables.
select the one named "connectionstring" and assign the variable you created to it.
your command line now becomes
dtexec /F myfile.dtsx /SET "\\package.variables[mydbconnstr].Value";"Data Source=MyProductionServer;User ID=PRODUSER;Initial Catalog=master;Password=PRODPassword"
note that in either case you may need to escape the ";" and the quotes - again the link you supplied show how to do it.
December 21, 2020 at 7:46 pm
I would recommend that you switch over to project deployment - build out the integration services catalog - and deploy your packages to the catalog. You can then use the /ISServer parameter on your command line and have access to modifying the parameters and connections.
Sorry - but it seems to me that accepting that your security platform breaks a required component of SQL Server is not going to be a good thing in the long run. Patching should not affect SQL Server agent...unless it is set to manual in the service applet (which - for some unknown reason is the default). Any security software should have filters applied so it does not affect database files - and it seems your security software has not been configured that way. Most likely then - it isn't configured to ignore mdf/ldf/ndf files and that is going to cause performance issues (at a minimum) and potentially corrupt your databases.
Still no luck. I created a SSISDB Catalog in a Lab environment, I converted my Package to the Project Deployment Model, and I'm still having similar issues.
>Sorry - but it seems to me that accepting that your security platform breaks a required component of SQL Server is not going to be a good thing in the long run.
This is a government mandate at my site. There is no option to be noncompliant.
December 21, 2020 at 8:09 pm
I don't know how we can help you any further - I have provided several links on how to setup the command line for dtexec. The examples are very clear on how to set it up to modify the connections.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 21, 2020 at 8:12 pm
Thanks for your response. I'll try working through your suggestions. I appreciate the help.
December 27, 2020 at 2:41 am
This was removed by the editor as SPAM
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply