XEROF

 

Sample : scheduling a SQL Server Integration Services job


- Start the SQL Server Management Studio

- Expand the SQL Server Agent folder on the left, right-click on Jobs and choose New Job.





- In the New Job dialog, give a Name to the job :





- Click on the Steps page from the list on the left. Then click on the New button to create a new step :





- In the New Job Step dialog, give a name to the step, then choose the SQL Server Integration Services Package Package-type from the Type drop-down :





- In the General tab, select File System from the Package source drop-down : (this is a simple case, packages may also come from the SSIS catalog)





- As the Package field at the bottom gets enabled, click on the [...] button and browse the file system for finding Package.dtsx :





- Click OK to quit the New Job Step dialog.

- If you would like to test the running of this job right away, you can click OK again to quit the New Job dialog. In SQL Server Management Studio, the new job is listed :





- If however you are willing to schedule the running of this job, in the New Job dialog, click on the Schedules item and click on New in order to create a new schedule (or select one of the existing schedules if any) :





- In the following we are create a schedule such that the job will run every 5 minutes. Enter a name for the schedule in the Name field. In the Occurs dropdown, choose Daily. Click on Occurs every, enter 5 in the field next to it and choose minutes in the dropdown. Click OK to quit the dialog :





- Click OK to end the New Job dialog and return to the main SQL Server Management Studio dialog.

- After 5 minutes, the first run occurs automatically. You can view the outcome by right-clicking on the job item and choosing View history.





- At any moment, you may run the job by right-clicking on the job item and choosing Start Job.

- The View history action lets you see previous job runs and act upon them :





This concludes a simple job scheduling and barely scratches the surface of what can be done by introducing query-based parameters and so on, for instance you could schedule the packaging and delivering of Excel files to individuals whose names and email addresses are known from a SQL query statement.

Posted on 06-December-2014 11:58 | Category: xlsgen, Excel generator | comment[0] | trackback[0]

 

Sample : using SQL Server Integration Services


- Start SQL Server Business Intelligence development studio.

- Select File / New / Project, pick Integration Services Project and click OK.





- The project is created and the Business Intelligence development studio shows up :





- If you don't see the Variables window, click on SSIS in the menu bar and choose Variables. This will be used later on.

- The project creates a file called a SSIS package, which is a file with a DTSX suffix. SSIS packages are designed, built, run, debugged and scheduled. Scheduling occurs in the SQL Server Management Studio. Everything else occurs right within the Business Intelligence development studio.

- Drag and drop the Script Task item from the Toolbox window onto the Package main surface area.





- Click elsewhere in the Package main surface area so that the Script Task object is unselected.

- In the Variables window, click the Add Variable icon in order to create a Variable. Name it ExcelFilepath. Note that spaces are not allowed. Change the Data Type value with the dropdown so it becomes a String. Enter c:\Temp\test.xls in the Value column. As expected, this variable will let know the Script Task where to store the Excel file that xlsgen is going to create.





- Start a Windows explorer instance and make sure the C:\Temp folder exists.

- Back to the SQL Server Business Intelligence development studio, double-click on the Script Task object in order to edit it.

- The Script Task Editor dialog shows up :





- Edit the Name so it becomes Generate Excel file.

- Then click on the Script tab on the left





- Click on the ReadOnlyVariables field and enter ExcelFilepath, which is the name of the Variable that was created earlier. We are telling the environment the names of the variables we are going to refer to in the script.

- Click on the Design Script button in order to start the actual source code editor.





- The source code editor window is filled with a template source code that we are going to edit in order to work with xlsgen. But before we do that, we must let know that we are going to use xlsgen by adding it as a .NET reference. So right-click on References in the Project Explorer window on the left and choose Add Reference.





- When you do this, the .NET assemblies dialog shows up and lists the known .NET assemblies. If you can't see Interop.xlsgen in there, you must follow the steps to Setup xlsgen for use in SSIS. And because SSIS caches the list of known .NET assemblies, you must also quit SSIS and start it again once the setup is done.

- Click on Interop.xlsgen, then on the Add button and click OK.

- Interop.xlsgen appears in the References tree in the Project Explorer window.

- Select the whole existing source code in the source code window, and replace it with the following :



VB.NET code


' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports xlsgen = Interop.xlsgen

Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()

Dim engine As xlsgen.CoXlsEngine = New xlsgen.CoXlsEngine

Dim wbk As xlsgen.IXlsWorkbook
wbk = engine.[New](Dts.Variables("ExcelFilepath").Value.ToString)

Dim wksht As xlsgen.IXlsWorksheet
wksht = wbk.AddWorksheet("Sheet1")

wksht.Label(1, 1) = "hello world!"

wbk.Close()

System.Runtime.InteropServices.Marshal.ReleaseComObject(engine)

Dts.TaskResult = Dts.Results.Success
End Sub

End Class



- This source code creates a trivial Excel file. Notice the use of the ExcelFilepath variable that we created earlier.

- Hit Ctrl+S in order to save the source code.

- At this point, if any part of the source code is underlined, it is because it is a compile error. This must be fixed before going any further.

- Then from the menu bar, click on Debug / Build. This does nothing visually.

- Quit the source code editor. Click OK to quit the Script Task Editor dialog.

- Hit Ctrl+S in order to save the package on the hard drive (or the SSIS catalog).

- Our package is now ready to run. From the menu bar, click on Debug / Start Debugging, or just click on the green triangle in the main toolbar.





- The Script Task should become yellow when the package is running. And then, depending on the outcome, it will become either red, which means there is an error somewhere, and an exception dialog will show up, or it becomes green, which means the package successfully run.





- Regardless how the execution went well or not, you are still in debugging mode and can end it whenever you want by using the menu bar and choosing Debug / Stop debugging.

- If the Script Task was green during execution, you can open up a Windows explorer window and verify that the Excel file exists in the C:\Temp folder.

- The SSIS package is ready.

- You can now create a SQL Agent job from the SQL Server management studio, where this package will be referred to, and schedule it.


Posted on 06-December-2014 00:34 | Category: xlsgen, Excel generator | comment[0] | trackback[0]

 

 

<-- previous page

< December >
0102030405
0607080910
1112131415
1617181920
2122232425
2627282930
31



 

 

This site
Home
Articles

DevTools
CPU-Z
EditPlus
ExplorerXP
Kill.exe
OllyDbg
DependencyWalker
Process Explorer
autoruns.exe
Araxis
COM Trace injection
CodeStats
NetBrute
FileMon/Regmon
BoundsChecker
AQTime profiler
Source monitor
GDI leaks tracking
Rootkit revealer
Rootkit removal
RunAsLimitedUser(1)
RunAsLimitedUser(2)

 

 

Liens
Le Plan B
Un jour à Paris
Meneame
Rezo.net (aggr)
Reseau voltaire
Cuba solidarity project
Le grand soir
L'autre journal
Le courrier suisse
L'Orient, le jour
Agoravox (aggr)