Integration Services Execute Package Options Tips

One of the Junior SQL Server Developers in my company approached me yesterday with a dilemma. He was developing an SSIS Package which imports data from a comma separated text file and he wanted to know the different ways in which one can execute an SSIS Package in SQL Server 2005 and higher versions. At first I started to tell him, but figured it would be smarter to document the options and share the information.

I use DTEXEC to execute my SSIS packages. I make extensive use of the various command line options available with DTEXEC. The problem I have is that the command lines are unwieldy; they are just too long and it creates a maintenance headache as it is so easy to edit a command line and mess it up. I have read through a number of your tips about how to utilize the various configuration options for SSIS. Everything I have read about the built-in configuration options is that you can retrieve values from a SQL Server table, an XML file, etc. but I really prefer to just use the command line options. I am currently specifying 10 or more command line options on average. Do you have any ideas on a better way to do this?

With the many changes from SQL Server 2000 to 2005, the Extraction, Transform, and Load process in many respects has changed the most. Even the name changed from Data Transformation Services in SQL 2000 to SQL Server Integration Services in SQL 2005. Not only did the product change, but so did the method for executing packages.

There are several ways that you can run an SSIS package and sometimes there may be a need to run a package directly from a query window or from within a stored procedure which can be done using xp_cmdshell. Enabling xp_cmdshell does come with a price; that is why it is disabled for a reason. This tip is not about the pros and cons of xp_cmdshell, I just issued this bit of background to give you fair warning. What this tip is about however is how to use xp_cmdshell to run SSIS packages from within a stored procedure: how to pass parameters to the SSIS package, and in-turn, what security steps you need to take to ensure you (a) allow use of xp_cmdshell with as low-level access as possible to the SQL Server instance and (b) what consideratio

One of the Junior SQL Server Developers in my company approached me yesterday with a dilemma. He was developing an SSIS Package which imports data from a comma separated text file and he wanted to know the different ways in which one can execute an SSIS Package in SQL Server 2005 and higher versions. At first I started to tell him, but figured it would be smarter to document the options and share the information.

I use DTEXEC to execute my SSIS packages. I make extensive use of the various command line options available with DTEXEC. The problem I have is that the command lines are unwieldy; they are just too long and it creates a maintenance headache as it is so easy to edit a command line and mess it up. I have read through a number of your tips about how to utilize the various configuration options for SSIS. Everything I have read about the built-in configuration options is that you can retrieve values from a SQL Server table, an XML file, etc. but I really prefer to just use the command line options. I am currently specifying 10 or more command line options on average. Do you have any ideas on a better way to do this?

With the many changes from SQL Server 2000 to 2005, the Extraction, Transform, and Load process in many respects has changed the most. Even the name changed from Data Transformation Services in SQL 2000 to SQL Server Integration Services in SQL 2005. Not only did the product change, but so did the method for executing packages.

There are several ways that you can run an SSIS package and sometimes there may be a need to run a package directly from a query window or from within a stored procedure which can be done using xp_cmdshell. Enabling xp_cmdshell does come with a price; that is why it is disabled for a reason. This tip is not about the pros and cons of xp_cmdshell, I just issued this bit of background to give you fair warning. What this tip is about however is how to use xp_cmdshell to run SSIS packages from within a stored procedure: how to pass parameters to the SSIS package, and in-turn, what security steps you need to take to ensure you (a) allow use of xp_cmdshell with as low-level access as possible to the SQL Server instance and (b) what consideratio

There are several ways that you can run an SSIS package and sometimes there may be a need to run a package directly from a query window or from within a stored procedure which can be done using xp_cmdshell. Enabling xp_cmdshell does come with a price; that is why it is disabled for a reason. This tip is not about the pros and cons of xp_cmdshell, I just issued this bit of background to give you fair warning. What this tip is about however is how to use xp_cmdshell to run SSIS packages from within a stored procedure: how to pass parameters to the SSIS package, and in-turn, what security steps you need to take to ensure you (a) allow use of xp_cmdshell with as low-level access as possible to the SQL Server instance and (b) what consideratio

I use DTEXEC to execute my SSIS packages. I make extensive use of the various command line options available with DTEXEC. The problem I have is that the command lines are unwieldy; they are just too long and it creates a maintenance headache as it is so easy to edit a command line and mess it up. I have read through a number of your tips about how to utilize the various configuration options for SSIS. Everything I have read about the built-in configuration options is that you can retrieve values from a SQL Server table, an XML file, etc. but I really prefer to just use the command line options. I am currently specifying 10 or more command line options on average. Do you have any ideas on a better way to do this?

One of the Junior SQL Server Developers in my company approached me yesterday with a dilemma. He was developing an SSIS Package which imports data from a comma separated text file and he wanted to know the different ways in which one can execute an SSIS Package in SQL Server 2005 and higher versions. At first I started to tell him, but figured it would be smarter to document the options and share the information.

With the many changes from SQL Server 2000 to 2005, the Extraction, Transform, and Load process in many respects has changed the most. Even the name changed from Data Transformation Services in SQL 2000 to SQL Server Integration Services in SQL 2005. Not only did the product change, but so did the method for executing packages.