Oledb destination commit interval

Hi

How can commit interval for OLE DB destination be set when the data access mode is not "fast load".

What happens in oledb destination in case of a failure in package? How does the roll back happens. I mean how is the commit point set in oledb destination? I know about the transaction options which are at the package level.

Thanks,

Vipul

Vipul123 wrote:

Hi

How can commit interval for OLE DB destination be set when the data access mode is not "fast load".

What happens in oledb destination in case of a failure in package? How does the roll back happens. I mean how is the commit point set in oledb destination? I know about the transaction options which are at the package level.

Thanks,

Vipul

"Commit interval" when you're not using fastload is 1. Each row is an independent insert. If there is a failure in the destination, the row can be redirected, you can ignore it, or the component can fail as determined by the error disposition of the component. If you're using transactions, then the rollback is managed by the server using the transaction log, not the oledb destination. Committing a bulk load batch and committing a transaction are not the same thing.
|||

Vipul123 wrote:

Hi

How can commit interval for OLE DB destination be set when the data access mode is not "fast load".

It can't. Use fast load. And why are you against using fast load?

Vipul123 wrote:

What happens in oledb destination in case of a failure in package? How does the roll back happens. I mean how is the commit point set in oledb destination? I know about the transaction options which are at the package level.

Thanks,

Vipul

When you are not using fast load, your roll back option is limited to ONE row unless you've enrolled the entire data flow in its own transaction (BEGIN TRANSACTION) or are using DTC. When you are not using fast load, if one row fails, that one row gets rolled back, while the others are left untouched -- including future rows depending on how many errors you've configured the package to accept.

OLE DB Source parameters ignored when subquery?

I am using an OLE DB Source with SQL command for the data access mode. I defined parameters, then added a complex query with subqueries. It seems like the parameters for a query are not being filled correctly (because I am getting too many rows returned). At one point I saw an error message the said something to the effect that the parameters would be ignored when there was a subquery in the SQL.

Can anyone shed some light on this?

Thanks,

Laurence

I don't know if that is true or not but regardless of that you should stop using parameters as explained here: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

-Jamie

|||

Jamie,

I have read your blog many times and it is really helpful. Thanks.

In this instance, the query is long and complex, and I might as well go to a script task as a string variable.

I am disappointed that parameters do not seem to be working correctly, thereby making my life more difficult.

Laurence

|||

Yeah, script task is a good option in those circumstances.

N.B. SP1 will have an expression builder attached to variables which will make it easier to build your expressions.

-Jamie

|||Would you mind sharing the long, complex query that didnt work for you?

OLE DB Source & SQL Command

Hi,

I am trying to set the OLE DB Source Editor. I am using the following option for Data Access mode

SQL Command from a variable and uses my sqlQuery variable.

sqlQuery variable contains a sql statement but I am getting the following error

ox80040E0C, Command text was not set for command object,

Additional information HRESULT 0xC0202009

Does it mean I have to give the variable name containing the stored procedure?

Please Guide

Do you get the error at design-time or runtime?

What value is in @.[User::sqlQuery]?

-Jamie

|||

Ignore this, When I pass the name of stored proc it works fine

Thanks for you help

OLE DB Source - Data Access Mode - SQL Command

I got a package with data flow task. Within the data flow task I have flat file with Fiscal Calendar defined. I got another data source within the data flow task, which is OLE DB Source. I want to use SQL Command as data access mode. SQL similar to one the below is in there.

-

DECLARE @.startdate DATETIME
DECLARE @.enddate DATETIME
DECLARE @.date DATETIME
DECLARE @.id INT

SET @.startdate = '1993-09-26' --Change these to
SET @.enddate = '2010-09-25' --whatever you want
SET @.id = 1
SET @.date = DATEADD(dd, @.id, @.startdate)

WHILE @.date <= @.enddate
BEGIN
select @.date CalendarDate,
DATEPART(dd, @.date) CalendarDayMonth,
DATEPART(dy, @.date) CalendarDayYear,
DATEPART(dw, @.date) CalendarDayWeek,
DATENAME(dw, @.date) CalendarDayName

SET @.id = @.id + 1
SET @.date = DATEADD(dd, @.id, @.startdate)

END

-

This SQL works fine in SSMS and returns around 6000 rows. But when I plug the same SQL in OLE DB Source it returns only the first record. It is not going through the WHILE loop.

Has anyone came across this?

Thanks

Sutha

Hey,

OLEDB Source connection to what database?

Brian

|||

I am connecting to my warehouse DB but the source is just the SQL, it doesn't need to extract anything from DB, as the SQL should give the result set.

What I should ideally use is "Execute SQL Task", which is in Control Flow Task.

Maybe I could achieve this by putting into a temp table and source it from the temp table. I am going to check it out.

Thanks

Sutha