0

Using Microsoft products, I have a collection of SQL scripts and a VS project that I use to refresh a DW on a weekly basis. I know the process can be automated, but the documentation for VS is so vast I don't know where to start. The HIGH LEVEL process is outlined below:

  • Open SSMS and MANUALLY run scripts to truncate tables and drop indexes
  • Open VS project and in dev mode MANUALLY press START to extract the data from application to the truncated tables in the DW
  • Open SSMS and MANUALLY run transformation scripts to create analysis cubes end users can access

I am trying to get to a point where I can just schedule this process to run every X period so I don't have to press any buttons.

hansolo
  • 903
  • 4
  • 12
  • 28
  • 1
    create a SSIS package and schedule it through windows scheduler. – FLICKER Jan 29 '19 at 17:52
  • @flicker - I am guessing I can put the SQL scripts inside the VS project, create the SSIS package from VS and then schedule the SSIS file through windows scheduler ... am I right? – hansolo Jan 29 '19 at 17:57
  • Yes, you can easily create SSIS package containing course of script – FLICKER Jan 29 '19 at 19:30

2 Answers2

1

I think, you can use jobs. Descriptions is here

Mixim
  • 972
  • 1
  • 11
  • 37
  • Im using SQL Server Express ... so the SQL Server Agent isn't available. The post below answers how to execute SQL commands with the windows task scheduler, but the part about integrating VS is where I am having issues https://stackoverflow.com/questions/7201061/how-to-create-jobs-in-sql-server-express-edition – hansolo Jan 29 '19 at 16:32
1

From what you described it seems like SSIS is something that can cover the tasks you listed. SSDT will need to be installed to use this. Since you're using scripts I'm assuming you have SQL script files saved that you execute. These can definitely be run in SSIS using an Execute SQL Task, with a file connection as the SQL source. With a collection of scripts, I'd suggest looking into using a Foreach Loop to run these via an Execute SQL Task while iterating through the folder(s) that contain the scripts. As far as running the Visual Studio project in development mode, configurations in Visual Studio can used to accomplish this for SSIS. SSIS has both an Analysis Services Processing Task and Analysis Services Execute DDL Task, and sounds like you're looking for the latter. Both XMLA and TMSL commands can be executed from an SSAS Execute DDL Task. Below are some links to get you started. The Data Flow Task may help with what you're doing for your data extraction.

userfl89
  • 4,610
  • 1
  • 9
  • 17
  • this is really helpful. I have most of the DDL tasks built already and I can add the scripts with the execute SQL task. I guess my main question is ... would it be as simple as saving the .dtsx file and then scheduling it with windows task scheduler – hansolo Jan 29 '19 at 19:29
  • A package can be executed from the command line using the dtexec utility. After you have the command to run the package, save it in a batch file and use the windows task scheduler to run this file. I updated my answer with a link to the dtexec documentation. – userfl89 Jan 29 '19 at 19:43