There are a few methods to execute a SQL Server Integration Services (SSIS) package from T-SQL. Very often the use of xp_cmdshell is the first choice to accomplish this task. Xp_cmdshell has primarily been a system administration extended stored procedure. Many types of extended stored procedures such as this one are meant for tasks that are either manual or very refined and controlled tasks. This is all due to the requirements of the levels of sysadmin roles – or CONTROL SERVER to be exact. Further on this topic, xp_cmdshell is disabled by default because it has been a known attack method. Having the ability to execute xp_cmdshell exposes operating system level access. In worst case scenarios, the SQL Server Service account is also a domain account with either Domain Admin rights or rights to other resources on the domain that are sensitive or open to damaging effects to the business. To expose xp_cmdshell then opens one of the highest security risks relating to SQL Server.
Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.
Browsing "sql server integration services"
SQL Server provides database professionals with several paths in order to import and export data in and out of many data sources and file services. One main task, the Foreach Loop Container, allows for processing large (or small) groups of files with one contained and manageable process. Tasks that once were difficult and required larger scale development efforts have now moved into the realm of the rapid development methodology. Rapid Development is defined as requiring a much lower amount of resources in order to push a process into a production environment. Resources can contain any one of the main components in developing strategies such as management, developers, infrastructure changes and so on. The resources take time in planning stages, developing stages and testing stages. Accomplishing this methodology is done by placing each of these on top of each other in a sense. Planning is done alongside developing and testing.
SQL Server has taken on many levels of growth over the years as a relational database management system. SQL Server has taken on roughly twelve major releases; code names were adopted 10 major releases ago. During the time of these releases, Microsoft has enriched the features that ship with the SQL Server Engine itself. These features started with SQL Server 7.0 and the release of OLAP; code name, Plato. With Plato, came a direction to fulfill the goal of bringing SQL Server to an enterprise level. Also packaged with SQL Server 7.0 was Data Transformations Services (DTS). DTS was a milestone as it provided the ability to broaden the range of abilities to work natively with SQL Server and Bulk type operations. Job scheduling capabilities were also increased in the ability to work more freely with more complex tasks. These tasks were housed directly in SQL Server (with respect to outside binaries and data stores). SQL Server 7.0 made a foundation for the years to come.
The title of this article is one that is asked on many occasions around the forums and SQL Server community. SQL Server Integration Services (SSIS) is the Extract, Transform and Load (ETL) platform behind SQL Server. There aren’t many arguments against SSIS as a great tool, and it has the ability to get the job done as an ETL platform. With the added complexity of any product, pain is involved while becoming familiar with the intricacies of it. One pain that comes with any development practice that is performed within one set of environment and system variables and later moved to another set (Transport process): a process can execute successfully in one location and fail in another.
Last year I attended SQL Saturday in Iowa and enjoyed it immensely. I was glad to see a follow up for this year coming this Friday, September 18th. This year I’m very happy to announce that I will be attending Iowa’s second SQL Saturday and also contributing as a speaker. If you have never been to a SQL Saturday event, I highly recommend attending this one or another in your area. These events are free and organized by people just like you and I.
I’m jumping into the T-SQL Tuesday fun this week. It is a busy week at that with SQL University writing and everything going on in the SQL Community. The SQL Server 2008 (R2) hottest, most favorite new feature topic had me wanting to throw SSIS out there once more and show off the Data Flow Engine changes.
This post will illustrate two methods for removing old files from directories using SSIS. This task is often used to delete old backup files and other ETL files that are not required any longer. We’ll step through two methods. First method uses a script task entirely for the removal and the logging events. This method will also have some comments in for logging and using the FireInformation method to mimic the normal logging abilities of SSIS. The FireInformation method didn’t provide much more of a performance boost so it wasn’t used here. Second method uses a Foreach Loop Container, Script Task for logic and a File System Task for the delete event. SSIS Logging will be utilized with the OnPreExecute and OnPostExecute events in the second method over the System.IO method of AppendText.
I will be speaking tomorrow night on SQL Server Integration Services basics for the SQL Connections group in the Chicago suburbs. Yes, the Wisconsin guy is going to go farther than Hwy 60 once again. The session, “SSIS 2008 Basics – Get up to Speed in 90 Minutes”, will be going over the fundamentals of SSIS and getting started with your own installation and development of packages. There is a lot to cover when considering SSIS basics when starting from opening the installation media, to opening BIDS for the first time. I hope everyone that attends leaves the session excited and ready to jump into SSIS.