I have built a few SSIS packages over the years, most of which contain a design which will fit within the VS window with little scrolling (i.e. not hugely complex). Although I have largely got my head round logging and configuration, I have never really fully settled on a standard for error handling.
I often handle errors via control flow (i.e. the red connectors) but have dabbled once or twice with the event handlers. While I have got the latter to work the implementation of them never seemed quite right (apologies for vagueness).
Does anyone have any thoughts on, or real world experience of, which route is better?
...or does it depend on the nature of the error? i.e. data-related errors via flow but critical failures via handlers.
Hoping this is not another one of those bun-fight topics...
I wrote this years ago for a report on total items sold, and I'm trying to convert it to a SQL Linq Statement in VB, but I think I'm way off here. Plus I'm not sure if I got the joins right, pretty sure that part should work.
, SUM(coH.Qty) as TotalQty
, CAST(SUM(coH.Qty * coh.Cost) AS Decimal(10,2)) as TotalCost
, CAST(SUM(coH.Qty * coh.Price) AS Decimal(10,2)) as TotalPrice
there must be a shorter way of writing the sum statement, and also I can't figure out how to sum the column within the select new. My original thought was to just think sql, but on the SUM, I think I need throw that idea out the window.
From cohc In context.Order_History_Cart
Join pi In context.ProductInfo On pi.PartNumber Equals cohc.PartNumber
Join vi In context.ProductInfo_Vendors On vi.VendorID Equals pi.VendorID
Where cohc.OrderDate >= m_startDate _
And cohc.OrderDate <= m_stopDate _
And cohc.PartNumber = p_PartNumber
Select New productItem With
.m_partNumber = cohc.PartNumber,
.m_manPartNumber = cohc.ManPartNumber,
.m_totalQty = (From x In context.Order_History_Cart Where x.OrderDate >= m_startDate And x.OrderDate <= m_stopDate And x.PartNumber = p_PartNumber Select x.Qty).Sum(),
.m_totalCost = (From y In context.Order_History_Cart Where y.OrderDate >= m_startDate And y.OrderDate <= m_stopDate And y.PartNumber = p_PartNumber Select (y.Qty * y.Cost)).Sum(),
.m_totalPrice = (From z In context.Order_History_Cart Where z.OrderDate >= m_startDate And z.OrderDate <= m_stopDate And z.PartNumber = p_PartNumber Select (z.Qty * z.Price)).Sum(),
.m_cost = pi.Cost,
.m_price = pi.Price,
Why do you have SQL embedded in your VB code? As a DBA that is a no no. You can get SUM in T-SQL, but you can also SUM in SSRS and Crystal Reports. As for your new I have never seen that. I am not a very experienced VB developer so it may be something I have not seen there. I would do it in a stored proc.
I got thtis to work, but I can't figure out how to insert my 2 joins, and get those values all in 1 result set
Dim pResults = _
From cohc In context.Order_History_Cart
Where cohc.PartNumber = "24-FLP44"
Group By cohc = New With
} Into group
Select New With
.totalQty = group.Sum(function(q) q.Qty),
.totalCost = group.Sum(function(c) c.Qty * c.Cost),
.TotalPrice = group.Sum(function(p) p.Qty * p.Price)<br>
Been there done that, but in WCF.
In design time you're apparently not using the app.config you think you are.
There's a description and kind of solution to one of the ways this problem can occur here[^].
1/ How can you add transaction functionality to your stored procedure without locking anything ( I know there is different isolation level, but I'm not sure which one to use )
2/ I need to use transaction just for the sake of rolling back the changes if there was an exception in the middle of the procedure. so I really don't care about locking tables. which isolation level should I use.
How can you add transaction functionality to your stored procedure without
locking anything ( I know there is different isolation level, but I'm not sure
which one to use )
Locking is done when writing. The isolation levels are for specifying what you want to read.
I need to use transaction just for the sake of rolling back the changes if there
was an exception in the middle of the procedure. so I really don't care about
locking tables. which isolation level should I use.
Sounds like the procedure consists of multiple steps that can fail individually, and those updates might lock multiple tables while processing.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
READ UNCOMMITTED is the isolation level you are looking for I think. This will allow dirty reads, but almost eliminate all locking. Writing will lock the affected tables, but if others READ UNCOMMITTED they should be fine. If you have to lock the table in order the rollback the process then you really have no choice but to lock it.
It's a Navigator fly out tab, in which if your working on a product in the editor, you can activate the tab, and the tab will populate with lets say 4 products before and 4 products after the selected product in the database table. This is so you don't have to go back to the index to load the next or previous product.
Since SQL Linq doesn't support the custom SQL Server Function, I need to think or create an alternative way to do this.
I guess I can create a List(Of with a blank column, and then go back and fill in the row numbers
get the row number of the item and ask for -3 and +3 of the row.
Just looking for some suggestions.
Here's the old TSQL
WITH CTE AS
, ROW_NUMBER() OVER(ORDER BY PartNumber DESC) AS RowId
FROM PRODUCTINFO p
), CTE1 AS (
SELECT RowId FROM CTE WHERE PartNumber = @PartNumber
SELECT * FROM CTE WHERE RowId BETWEEN (SELECT RowId-4 FROM CTE1) AND (SELECT RowId+3 FROM CTE1) ORDER BY PartNumber
This is what I was building in Linq Pad. It's pretty weak now, but I'm getting an idea of what I can do. But before I go off in some strange direction that will lead nowhere or be super slow, I thought I would just try and get a general consensus on this.
I tried to mark it as code but it gets chopped off, end of the day for me, going home now.
Dim p_partNumber as string = "06-SM4"
Dim context as new DBcontext
Dim ordersAsList = _
From pi in context.ProductInfo
Dim orders = _
From pi in ordersAsList
I didn't know you can do that.
I know how slow it can get when the record count get high. That's why I didn't want to do the list.
I'll give that a try for now, and then redesign the whole thing later. I knew that using special DB function would take a tool in the future when I implemented it.
Works like a charm. Took awhile to figure out how to implement it.
Did you write that? it's pretty elegant and wild in how it works.
It would of taken me months to figure that out! But that should be the most complex TSQL remaining in my program.
Oh, this conversion I'm making is mentally draining on me every day, but I'm 1/2 way there now.
The reward will pay off for me when done. Thanks again for taking the time to write that, that was beyond my current knowledge level.
I have this report for sales rep margin in Account Mate for DOS, that uses FoxPro for database table file, .dbx
The item tables or DB files does not have a column for sales rep and order dates, so I can't go to the table directly to get my data.
So without knowing or even where to start to write this in pure SQL, I wrote 2 functions
1. Get Invoices that match the sales rep and start and stop dates, and store it in a List(of invoices) 2. loop a function that targets items by invoice number, and get the cost, price, qty, etc.
When you have 500 invoices, it takes forever to run, slowing down fixing my other problems, which may be fixed now.
Do you think it's possible to merge these statements into a single statement. Plus if so, a nudge in the right direction. I hate this old Fox Pro stuff, it was before my time.
FROM ARINV01H.dbf h
h.FSALESPN = @FSALESPN
h.FSHIPDATE >= @startDate AND h.FSHIPDATE <= @stopDate
FROM ARINV01.dbf v
v.FSALESPN = @FSALESPN
v.FSHIPDATE >= @startDate AND v.FSHIPDATE <= @stopDate
, SUM(FCOST * FSHIPQTY)
, SUM(FPRICE * FSHIPQTY)
, (SELECT FDESCRIPT FROM ICITM01.dbf i WHERE i.FITEMNO = h.FITEMNO) AS FREALDESC
FROM ARTRS01H.dbf h
h.FINVNO = @FINVNO
FROM ARTRS01.dbf v
v.FINVNO = @FINVNO
GROUP BY FITEMNO "