I need to pull data from an excel sheet and load the data into a SQL table. This job should be done as a part of a SP. Because there is a lot of SQL code that needs to execute right after the file is loaded. We get like 20 of these files everymonth. I created a job and executed it using xp_cmdshell and DTSRun from my SP. But then the user needs be in sysadmin role or the account under which MSSQLServer runs should be a part of local administrators group. Both are not a very good from a security standpoint. So what are my options? What if I use opendatasource ???? I would doing some dyanmic SQL, but looks like there is no other option. Looking for ideas.
jeremas - When creating a dts package, you can either use a wizard or create one from scratch. When using the wizard, basically you select a source, destination and any transformations. The source is the file (in this case) - the destination is the database where you want the source loaded - the transformations allow you to map the columns in your source file to columns in your table ... also allowing you to manipulate the data as well before it is stored in the table.