After creating and deploying your SSIS package, you might want to schedule it.
1. First under SQL Agent, Right Click Jobs and select “New Job”
2. Fill in the blanks make sure you set owner to your user
3. Select the Steps page on the left hand side, Name the step (I use, Run SSIS Package or Run SSIS PackageName), Set the Type to SQL Server Integration Services Package, set Run as: to a privileged account (I use SQL Server Agent Service Account)
4. On the general tab set the package source to SQL Server and choose the servername (assuming you used my Deploying SSIS Package instructions), select your package.
5. Set the schedule (easy stuff)
I don’t have to tell you to click “OK”…do I…???
Right Click your project, choose properties. Under “Deployment Utility” set the CreateDeploymentUtility to “True”
Next set the below paths.
Build -> OutputPath: D:Program FilesMicrosoft SQL Server SSIS PackagesBin
Deployment Utility -> DeploymentOutputPath: D:Program FilesMicrosoft SQL Server SSIS PackagesBinDeployment
In explorer navigate to your deployment utility folder and double click the “somepackagenameManifest” file.
The wizard will pop and and you can take it from there.
Also, don’t forget, If you modify and update the package, you will need to run the manifest file again.
The problem: Create a SSIS package that will update a table with only new records
The quick and dirty:
1. Drop a Data Flow Task
2. Inside 1, Drop (in this order) an OLE DB Source, Lookup, and SQL Server Destination
3. The OLE DB Source is your select statement from data source, for example mine is:
SELECT data1, data2, data3, etc, from sourceTable
4. The lookup contains a query to compare, for example mine is:
SELECT data1, data2, data3, etc, from destinationTable
5. Configure the Error Output on the Lookup to Redirect Row
6. Connect the Red Arrow from the Lookup to your SQL Server Destination