This post is part of a 6 part series. Jump to [part 1][part 2][part 3][part 5][part 6]

To sum up how we got here:

  1. We used PowerShell to gather some specific session stats from Citrix MFCom and output them to a text file. [part 1] [part 2]
  2. We then created a database and table to hold this data. [part 3]
  3. Following this, we created a job in Visual Studio to parse the text file and insert it into the above table. [part 3]

Now, I will show how to save the Visual Studio job directly to an MS SQL server and have that server run a job to insert new data on a periodic basis.

With your Visual Studio package open, go to File and select Save Copy of Package As…

Select SQL Server for package location, enter the server name, set the authentication type and credentials for the DB owner.

vs-1

Choose and name a location for the Package Path and hit OK.

vs-2

Change the protection level to “Rely on server storage and roles for acces control” and click OK to save.

Now login the SQL server with Management Studio and open SQL Server Agent and Jobs.

vs-31

Right-click on jobs and select New Job.  Select the Steps page and click new.

vs-4

Hit the Type drop down and select SQL Server Integration Services Package.  Enter the server name and then look for the Package.  It will have the same name you gave it when you exported it to the SQL server.  Give the step a name and click OK.

vs-51

Now click on schedules to run this job every 5 minutes.

vs-6

Click OK, name the job and open the Job Activity Monitor to confirm the job kicks off.  If you get a Succeeded result, you can query the table to ensure it has got new data.  So now the SQL server is kicking off the Visual Studio data flow job to read a flat text file and enter the data into a table every 5 minutes.  Next post, we’ll pull the data out of the table in some meaningful ways and place it in MS SQL Reporting Services.

This post is part of a 6 part series. Jump to [part 1][part 2][part 3][part 5][part 6]

Value for Value

If you received any value from reading this post, please help by becoming a supporter.

Thanks for reading,
Alain