On demand SSIS Agent Job Updates

Afternoon all,

This should be brief, hopefully. Every morning all our SSIS updates run on a schedule(s) by SSMS Agent Jobs. Most of the time this is acceptable, however, during the month end accounting process it would be desirable for accounts to be able to run one of the jobs on demand.

Is there a way that this can be done without having to install and use SQL Server Management Studio on account machines? The potential damage that can be done, even inadvertently, with this utility is too great to even consider giving them this package.

Thanks in advance,

Paul

sp_start_job - Write a small program to call that procedure to kick off the sql agent job.

OLe DB Command and Using the value of an input column more than once

Hi there,

I have an OLE DB Command which updates a table. However, the command needs to use the value of an input column more than once.

For example I want to update TableA only if either ColumnA or ColumnB have actually changed: -

update tableA

set columnA = ?,

column B = ?

where columnC = ?

AND (columnA != ? OR columnB != ?)

I can't map the Input column to more than one parameter so I've been forced to create a copy of columnA and ColumnB as input columns so I can map to the extra paramters that the Command shape expects.

I also attempted to modify the command syntax so it set up variables for the 3 values required and then set the values to parameters - but I get a very unhelpful syntax error message: -

declare @.ValueA varchar(50),

@.ValueB varchar(50),

@.ValueC varchar(50)

select @.ValueA = ?,

@.ValueB = ?,

@.ValueC = ?,

update tableA

set columnA = @.ValueA ,

column B = @.ValueB

where columnC = @.ValueC

AND (columnA != @.ValueA OR columnB != @.ValueB)

Any suggestions?

anydobbo wrote:

Hi there,

I have an OLE DB Command which updates a table. However, the command needs to use the value of an input column more than once.

For example I want to update TableA only if either ColumnA or ColumnB have actually changed: -

update tableA

set columnA = ?,

column B = ?

where columnC = ?

AND (columnA != ? OR columnB != ?)

I can't map the Input column to more than one parameter so I've been forced to create a copy of columnA and ColumnB as input columns so I can map to the extra paramters that the Command shape expects.

Yeah, that's what you have to do!

anydobbo wrote:

I also attempted to modify the command syntax so it set up variables for the 3 values required and then set the values to parameters - but I get a very unhelpful syntax error message: -

declare @.ValueA varchar(50),

@.ValueB varchar(50),

@.ValueC varchar(50)

select @.ValueA = ?,

@.ValueB = ?,

@.ValueC = ?,

update tableA

set columnA = @.ValueA ,

column B = @.ValueB

where columnC = @.ValueC

AND (columnA != @.ValueA OR columnB != @.ValueB)

Any suggestions?

I may be wrong but I don't think you can do that. The OLE DB Command accepts a DML statement but not a statement block (as far as I know).

-Jamie

|||Thanks Jamie

OLE DB Command and Destination writing to the same table

Hi,

I have a data flow task that performs an "upsert" by directing successful rows from a Lookup to an OLE DB Command that updates rows and unsuccessful rows (Lookup error output) to an OLE DB Destination for insertion.

The problem is that execution hangs when both tasks update/insert into the same table (execution is still hung after 20 minutes). Modifying the OLE DB Destination to insert into a different table succeeds (execution completese within 2 minutes). Replacing the OLE DB Destination with a Row Count transformation also works.

Could this be due to a table-locking issue? Any suggestions?

Thanks
ray

Just to confirm you haven't set the table lock check box on the oledb destination?

|||

You might look at the slowly changing dimension task... I'm not that familiar witrh it.

A quick and easy solution would send one of the outputs, probably the updates to a raw file destination. Then read that raw file in a different data flow task and perform the update there.

Doing a lot of updates in a sqlCommand - row by row - can be time consuming and I usually try to find another way to do it using some kind of set based operation. For example, if I can identify unique properties of from the source data, a date range or a specific field value; I would use that to construct a sqlCommand that would update all of the records at one time.

|||

Here is 2 links to pages that talk about different techniques for doing an "upsert". This is the

first time I have heard that term. I like it!

This is my web site:
http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm

This is Jamie Thompson's cool

blog:
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

Thanks,

Greg Van

Mullem

|||

rlee1003 wrote:

Hi,

I have a data flow task that performs an "upsert" by directing successful rows from a Lookup to an OLE DB Command that updates rows and unsuccessful rows (Lookup error output) to an OLE DB Destination for insertion.

The problem is that execution hangs when both tasks update/insert into the same table (execution is still hung after 20 minutes). Modifying the OLE DB Destination to insert into a different table succeeds (execution completese within 2 minutes). Replacing the OLE DB Destination with a Row Count transformation also works.

Could this be due to a table-locking issue? Any suggestions?

Thanks
ray

Yes, this is absolutely a locking (actually a blocking) issue. Execute sp_lock or sp_who2 to confirm it.

You can get around this problem by executing the insert and update operations in different data-flows. Use a raw file to pass data from one data-flow to another. This technique is covered (albeit for use in a differrent context) here:

Splitting order detail and order header information from one file into multiple tables
(http://blogs.conchango.com/jamiethomson/archive/2006/05/22/3974.aspx)

-Jamie

|||Oho, I get the question now. My previous reply was useful but a little off topic. Are you use the "Fast Load" option in your OLE DB Destination? Turning this off might help.

You could also uses 2 "OLE DB Command" components (running insert and update commands). That's what I'm doing so I know it works quite well.

Thanks,
Greg