SSIS Logic For Storedprocedures
Nov 6, 2006Hi,
Do you have any suggestions of how to transform the below code in SSIS?
I guess we should use foreach loop container...... also i would appreciate if you can post some related articles.
ADVERTISEMENT
Issue With SSIS Logic
May 16, 2008
I have a registration plate. It has an expiry date. And if I do not renew it within the 60 days it should go inactive else it should be active.
How can I use SSIS/SQL to implement this logic.
Please let me know Il really appreciate
Thanks
Little Urgent.....Advice On Logic Flow In SSIS Transformation......!
May 19, 2008Dear All,
I have a table A with a KEY column and SSN column.
KEY = 12 digits ( first 3 digits are Department Id , and last 9 digits are SSN)
I have a table B with SSN column only.
both KEY and SSN columns are Primary keys so duplicate entries must be Avoided.
Table A is intended to be popluated weekly from TXT file (SSIS package RUN). I want to achieve somethign like this..!
P-Code sample:
for each Row in TXT file
if TXTfile.KEY = TableA.KEY then
skip and Read/Go to next Row in TXT file
else
INSERT TXTfile.KEY into TABLEA.KEY
SSN_Var = EXTRACT the SSN part (SSNpart.READ)
if SSN_VAR.Exists In TableB.AnyRow then
skip
else
Insert into TableB
End If.
End If
End For Loop.
-----------------------------------------------------------------
Using SSIS controls, what will be best flow and logic to achieve this.....?
any sample scripting code ????
Many Thanks.
Errors In Storedprocedures
Feb 3, 2005Is there a function that will indicate if there is an error in your stored procedure? Like for example, I want to create a stored procedure and if there is an error I want it to print out an error. How can I do this?
View 4 Replies View RelatedQUERIES VS STOREDPROCEDURES
Mar 22, 2007I use visual studio 2005 standard edition and sql express 2005.I would like to know if there is any difference using queries(designed in dataset xsd) or stored procedures.
Also I am having the following problem :Whenever I make a change on a table in my database (add columns etc) and use the wizard to retrieve the changes i loose all the queries for that table (I have to write them again).Am i doing something wrong?
Thanks in advance!!!
Getting Storedprocedures Results Into Cursors
Sep 5, 2001hi,
How can i get system stored procedure resultset into cursors.
example:
I want to get sp_validatelogin result set into curosors.
How??????????
thanks
keerthi
Using Text Datatype In Storedprocedures
Jan 23, 2005Hello all ,I tried to insert text datatype by using storedprocedures but I couldn't do it ,anyone has any clue or idea ,sincerely youres
View 3 Replies View RelatedMigrate SQL Server StoredProcedures
Jun 11, 2004Dear Users
Can any body tell me that how can I migrate SQL Server Stored Procedures to Oracle using DTS. I want to do it in VB.Net.
Thanks
Osman
Could Not Find Type 'StoredProcedures' In Assembly 'HelloWorld'
Aug 13, 2007Why would you get this error?
CLR Class
Code Snippet
Partial Public Class StoredProcedures
_
Public Shared Sub HelloWorldStoredProcedure()
SqlContext.Pipe.Send("Hello World")
End Sub
End Class
SQL Code To Test
Code Snippet
USE CMM
GO
-- Drop Pervious Version
DROP ASSEMBLY HelloWorld
GO
--Add Assebly
CREATE ASSEMBLY HelloWorld
FROM 'C:My DevelopmentSQL2005 CLRHelloWorldHelloWorldobjDebugHelloWorld.dll'
WITH PERMISSION_SET = SAFE;
GO
--Check that Assembly worked
SELECT * from CMM.sys.assemblies
--Build Procedure
CREATE PROCEDURE HelloWorldSP
AS
EXTERNAL NAME HelloWorld.StoredProcedures.HelloWorld
GO
--Test
EXEC HelloWorldSP
GO
SQL Error
Code Snippet
Msg 6505, Level 16, State 1, Procedure HelloWorldSP, Line 2
Could not find Type 'StoredProcedures' in assembly 'HelloWorld'.
What have I done wrong?
Need Some Logic Help
May 10, 2008Can anyone see where my logic may have gone a stray?
A user would be attempting to update a gridview row, I'm storing the amount of an item and the qty they purchased. If the order was for $150 and the grid has two item 1 x $50 and 2 x $50 the order already totals $150, so if the user tries to change the first item to a qty of 2 x $50 the total would be $200 and this would be more than the total payment or $150.
I want to then tell them they cannot do this.
@AP_ID Int,@AI_ID int,@PurchaseAmount money,@PurchaseQTY int,@LastUpdate datetime,@LastUpdateBy nvarchar(50),@Receipt_ID intASdeclare @current_item_total decimal(18,2)declare @new_item_total decimal(18,2)declare @total_payments decimal(18,2)declare @current_purchase table(receipt_id int,purchase_amt money)INSERT INTO @current_purchase SELECT tblPurchase.Receipt_ID,(tblPurchase.PurchaseAmount * tblPurchase.PurchaseQty) as purchase_amtFROM tblPurchase INNER JOIN tblReceipts ON tblPurchase.Receipt_ID = tblReceipts.Receipt_IDWHERE tblReceipts.Receipt_ID=@receipt_id--Get total already saved to recordselect @current_item_total = sum(purchase_amt) from @current_purchase--Get total amount of payment saved to recordselect @total_payments = sum(tblReceipts.AmountPaid) from tblReceipts where tblReceipts.receipt_id=@receipt_id--Get total amount user is trying to save to recordset @new_item_total = (@PurchaseAmount * @PurchaseQTY)--If current total plus new total is greater than total payment, tell user they cannot do this.IF ((@current_item_total + @new_item_total) > @total_payments)BEGINSELECT 'You are attempting to add more than your total payment.' AS MESSAGEENDELSEBEGIN--UPDATE ROWUPDATE tblPurchase SET [AI_ID]=@AI_ID,[PurchaseAmount]=@PurchaseAmount,PurchaseQTY=@PurchaseQTY,LastUpdate=@LastUpdate,LastUpdateBy=@LastUpdateBy WHERE [AP_ID]=@AP_ID SELECT 'Item was updated.' AS MESSAGEEND
Need Some Logic Help
Jan 6, 2004Trying to bind some data to a datalist for a report.
User selects an Industry from a dropdown list and then I dump all records for that industry. However in order to parse some of the record field values into names (I.E. from a 1 to the actual company name) for some records I have to read TABLE_ONE and for other records I might have to read TABLE_TWO depending on the value of FIELD_ONE.
If FIELD_ONE = "A" then I get the NAME from TABLE_ONE.
If FIELD_ONE = "B" then I get the NAME from TABLE_TWO.
If FIELD_ONE = "C" then I get the NAME from TABLE_THREE.
I'm lost at how to get started on this. I thought about adding IF statements to my query but these won't work because I'm not passing in the value of FIELD_ONE ahead of time - it's part of the query. So I thought maybe I could do a pre-read and store all FIELD_ONE values in an ArrayList and pass these in as parameters, but the stored proc is only being called once - so that won't work.
Any thoughts on how I can do this?
Help With Logic...
Apr 27, 2006tbl_one hv 8mil rows, tbl_2 have 8k rows...
if
select count(*) from tbl_one
where sub_col1 = 2
return 3mil rows
and
select count(*) from tbl_2
where ad_col1 = '000009'
return 4k rows
HOW COME..
select count(*) from tbl_one,tbl_2
where (sub_col1 = 2 and ad_col1 = '1234')
return more than 12 billion rowss?? helpp..
Help The Logic
May 3, 2006I have to write trigger to relate two table.
If I have made changes like insert, update and modify in one table1 automatically have to change the table2 and vice versa.
How this can be done, do we need to point the common key fields in both table while inserting
Is This Right Logic
Jun 12, 2007What i'm trying to do is if this column dt.IN_DIV_NO is populated take that value first if it's null than rr.AIQ_R_DIVISION_NO and if that column is null than rr.F_DIVISION_NO.
This is what i came up with, will it evaluate the way i need it to?
case When dt.IN_DIV_NO is not null then dt.IN_DIV_NO else case when rr.AIQ_R_DIVISION_NO is null then rr.F_DIVISION_NO end end as Current_DIV
Thank-you
SQL Logic
Mar 13, 2008I have a logic problem,
I am selecting from a table
Select * from TAB_A
where state not in (1,4)
and create_date < '2008-01-01'
and
(
(x != 2 and y != 1 and z != 4) or
(x != 6 and y != 3 and z != 1) or
(x != 8 and y != 0 and z != 9)
)
then for example i am getting results where x,y and z is equal to one
or more of above combinations ?
Now i vaguely remember that using or with a != messes up the logic ?
if so can i use an NOR ? does it exist ?
Logic To Use
Mar 25, 2008
I am looking for the best approach to update a table's column based on the results of two other different tables. My tables structure is as follows.
Table 1 has columns A and B (tblemployee has location and employeenumber )
Table 2 has columns C and A (tblocation has locationID (identity) and location)
Table 3 has coumns C and B (tblcountry locationID (foreign key) and employeenumber.)
I want to update Table 3 (tblcountry) with the new locationID if and employee changes location or gets miscoded using an SSIS package.
Thanks in advance.
Logic
Nov 14, 2007I am having a little problem with my logic.
i have a table simplehoursassignment that has a field named br.
i need to get the value from simplehoursassigment & say if @mybit AND br > 0
set each day.
--CREATE PROCEDURE rpt_siteMealList(
-- (@cmb1 AS VARCHAR(100)) WITH ENCRYPTION
--)
--
--AS
DECLARE @dtm1 AS DATETIME
DECLARE @mybit AS INTEGER
SET @mybit=10
SELECT @mybit = CASE datepart(dw,@dtm1)
WHEN 1 THEN 1 -- 'Sunday'
WHEN 2 THEN 2 -- 'Monday'
WHEN 3 THEN 4 -- 'Tuesday'
WHEN 4 THEN 8 -- 'Wednesday'
WHEN 5 THEN 16 -- 'Thursday'
WHEN 6 THEN 32 -- 'Friday'
WHEN 7 THEN 64 -- 'Saturday'
END
SELECT br FROM simplehoursassignment
IF @mybit AND br > 0
BEGIN
CASE WHEN @mybit = 1 THEN 'Sunday'
WHEN @mybit = 2 THEN 'Monday'
WHEN @mybit = 3 THEN 'Tuesday'
WHEN @mybit = 4 THEN 'Wednesday'
WHEN @mybit = 5 THEN 'Thursday'
WHEN @mybit = 6 THEN 'Friday'
WHEN @mybit = 7 THEN 'Saturday'
END
END
Logic In T-SQL
Apr 30, 2008I have this query that is returning the same result twice and i cannot find why. I only have one record in Subquote.
Does anyone know what the problem is?
Code Snippet
SELECT s.*
FROM Quote q
INNER JOIN SubQuote s
ON q.id = s.quoteID
INNER JOIN TakeOffSheetItem t
ON t.quoteID_takeoffitem = q.id AND t.subQuoteID_takeoffitem = s.subquoteid
INNER JOIN PipeGroup p
ON p.quoteID_PipeGroup = q.id AND p.subQuoteID_PipeGroup = s.subQuoteID
WHERE (q.id = 1
AND q.deleted = 0
AND s.deleted = 0
AND t.deleted_takeoffitem = 0
AND p.deleted_PipeGroup = 0)
ORDER BY s.subquoteid
Thanks
K
Database Logic
Jul 31, 2006I am trying to figure out how to set up this database.
Basically, there are products with their associated fields. Each product can belong to multiple categories, and each category also has subcategories.So far I have the following, but not sure if this is the best way to set it up...TABLE Products:product_id (int) (1-many relationship to product_id in Table Product_Category)sku (int)descriptionpriceTABLE Categorycategory_id (int) (1-many relationship to category_id in Table Product_Category)nameTABLE SubCategorysubcategory_id (int) (1-many relationship to subcategory_id in Table Product_Category)category_id (int)nameTABLE Product_Categoryprodcat_id (int) product_id (int) (many-1 relationship to product_id in Table Products)category_id (int) (many-1 relationship to category_id in Table Category)subcategory_id (int) (many-1 relationship to subcategory_id in Table SubCategory)
Thanks,Mick
Need The Logic To Get This Done Without Cursors
Mar 20, 2007Suppose in the table below the left column is called intNumber and right column is called strItem.I want to select the rows where the intNumber number appears for the first time(the ones marked with arrows), how do I do it?
View 4 Replies View RelatedSQL Logic Problem
May 21, 2007I'm trying to figure out how to track what messages have been read by what users in a message board program we wrote. The users are identified by an ID number and each message has a unique number.
Let's say there are 10000 users and 200,000 messages. What would be the database setup/logic to track every user as to what messages they had read or not read? Maybe I'm missing something but it does not seem to be a trivial task.
Db Logic - Can't Put It Back Together
Feb 27, 2006Hi there!
Hope somebody got some better insight into this problem than i have. I'm struggling with some db logic.
Overview:
I have a client who is a publisher. They would like to post all their advert-size specs for each magazine to their web site. My first thought was that ill be able to build one table and populate it with the information for each magazine. I was wrong. Each magazine comes in a different size. Thus each magazine will have different advert-sizes as well.
Layout for a magazine will look something like this:
Size Trim Type Bleed
Full Page 280x440 270x430 290x450
Half Page 140x220 130x210 150x230
etc...
Some mags will not have values for Half pages since they dont print half pages and others will not have specs for Bleed.
Because of this - as an easy way out I created a table per magazine. It works but i dont think its very smart.
Break it down!
Ok so what im trying as a solution is to have three tables. The 1st table will hold the magID and Size values (e.g Half Page). The 2nd table will also have the magID and the Trim, Type, Bleed info. The 3rd table holds magID, sizeID, specID and the actual value (140x220).
I thought that this would be better because within these three tables i can store the information for each magazine regardless of their differences. Brilliant!
The Problem.
The problem comes when i have to put it all back together again. I need to represent this data in a table so i can bind it to a datagrid. I have NO idea how to do this. What i THINK i need to have is some temp table created on the fly. The row names for this temp table will come from the 1st table. The column names will come from the 2nd table and the values for each field will come from the 3rd table bound by foreign keys.
I've somewhat managed to do this with INNER JOINS. But it doesn't give the desired result. I need to set row and column NAMES using tables 1 & 2 then populate the columns with table 3, then bind to a datagrid.
Any ideas on how i could manage this?
If you made it tis far through my question then thanks anyways! I hope you can help me out!
Ta
Views Having Logic? - Please Help!!!
Oct 26, 1999I need to create a view joining 2 tables(1st with data, 2nd is a lookup) but I need to have some
logic(IF/ ELSE,ELSE and an UPDATE).
What is the best way?
-aw
Complex Sql Logic... Need Help
Feb 15, 2007okay;
i have a table called tblSlots
tblSlots is a list of Start datetimes and End datetimes.
every day has the same list of 10 slots.
tblSlots:
PKSlotINDEX
datStartTime
datEndTime
then i have a table called tblPersons
tblPersons:
PKPersonINDEX
txtLast
txtFirst
then i have a table called tblSchedule
tblSchedule:
PKScheduleINDEX
fkSlotINDEX
fkPersonINDEX
i want to write a query that takes any one specific person's schedule for an entire specific day, adds an arbitrary number of days to datStartTime, and finally inserts the PKSlotINDEX corresponding to the calcultated StartTime and fkPersonINDEX.
wow complicated isnt it...
the goal is to take the schedule of one day for one person and copy it to another day; i can scrap my current layout if necessary.
thanks!
Query Logic
Jul 28, 2005I have 2 tables:
First table: empID,PlanID,groupID
Second: PlanID,groupID,EffectiveDate,TerminationDate,DeadlineDate
I need to show only employee with in spesific group who is not
enroll for the current month until deadline passed.
Example:
empID PlanIDgroupID
11012
PlanIDGroupIDEffectiveDate TerminationdateDeadlineDate
101208/01/200508/31/200508/15/2005
111209/01/200509/31/200509/15/2005
91208/01/200508/31/200508/15/2005
If I run it today I should not get any results back. If I run in
on 8/15/2005 I should get back data with palnID11.
Help With JOIN Logic...
Jul 27, 2004Arrrgggg, nothing makes any sence any more. I need to write a select statement that will display results based on one of two things...
Zip Code OR City State
By the way this is a Table of addresses with zip codes, I also have a Lat/Long table associated to all the different zip codes that need to be joined.
The Psuedo code is somethinglike this...
SHOW All People WITH Appropreate Longditude and Latitude
Who Live
In @This ZipCode
OR
(In @State AND @City)
-------------------
Table Dealers
-------------------
dlrID - varchar
dlrName - varchar
dlrAddress - varchar
dlrCity - varchar
dlrState - varchar
dlrZip - varchar
dlrCountry - varchar
-------------------
-------------------
Table ZipCodes
-------------------
zipCode
zipCity
zipState
zipCountrty
zipLat
zipLong
...
-------------------
I have achieved this with Joins but the query takes 2+ minutes to execute
What am I doing wrong??? I am sure there is a better approach to this!
Thanks,
--Lito
Logic Problem
Oct 9, 2006Hi,
This might just be my brain not working after the weekend, but I'm having problems working out just how to do this.
The table:
CREATE TABLE [dbo].[tblQuiz] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[q1] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q2] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q3] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q4] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q5] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q6] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q7] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q8] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[quizdate] [datetime] NULL ,
[ipaddress] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sessionid] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[score] [int] NULL
)
The field [q5] has one of three values in it: "Happy", "Unhappy" or "Neither".
I have a list of about 50 session ID's. If a record in [tblQuiz] has a [sessionid] that matches one in this list, then:
if [q5]= 'Happy' then change it to 'Unhappy'
if [q5]= 'Unhappy' then change it to 'Happy'
The only way I can think of is to change all of one matching record type to some other value, then change all the other type, then change all the original type back to the other type. Which doesn't make much sense even when I've written it down, much less when I'm doing it and have to remember where I'm at. Is there a better way?
Logic Needed
Jan 4, 2007Hi pundits,
I need a logic for solving a realtime issue.
I have series 2^0,2^1,2^2,.......2^n ( i.e 1,2,4,8,16.....2^n)
I want to write a function(algorithm) which returns all the terms used(added) to form the number
e.g fun(7) :-1,2,4
fun(5) :-1,4
fun(257):-1,256
fun(6):-2,4
Ur help is highly appreciated.
Thanks in advance.
Query Logic - SQL Help
Jun 8, 2007I have some table data and know how I want the results but I'm just having a bit of trouble in constructing the SQL logic to obtain the desired results. There's a site where visitors are able to select from a list of parts, and it will return a set of model/products that they can produce with the selected parts. Here's the data ...
tblModel tblPart
ModelId ModelName PartId PartName
---------------------- ----------------------
1 Alpha 1 CHOO1 Stem
2 Bravo 2 BH034 Rod
3 Bravo Pro 3 HRE Seat
tblModelPart
ModelPartId ModelId PartId
---------------------------------
1 1 1
2 2 1
3 2 3
4 3 1
5 3 2
6 3 3
... and here's the logic that I'm trying to implement, assume that the user selects from a form, parts with the PartId 1 and 3 ...
1. Return all models that contain only the parts selected.
ModelId ModelName
---------------------
2 Bravo
2. Return all models that contain the parts selected, and may contain other parts.
ModelId ModelName
---------------------
2 Bravo
3 Bravo Pro
... so do you have any idea on how the SQL would look for either of these queries?
Thanks in advance,
Goran
Select With If/then Logic
Dec 1, 2005I need to do something like the following logic: (What I am trying to do is a weight conversion statement)
select
part,
desc,
net_wt,
net_wt_um,
pack_um,
pack_wt,
@somevariable = net_wt / (if net_wt_um <> pack_um then
(if exist(select conversion)
from table1
where field2 = pack_um)
then pack_wt / conversion
else pack_wt
Any thougts? I am basically just looking for a way to do if/else in select statements.
Thanks,
Scott
Help(query) Need In This Logic.
Mar 20, 2008I have two tables X,Y
X
empno....Sal.....Tax.....Returns...name
1.....4500....1050.... 750.......robert
2.....5750.....1560....900.......john
3.....4000.....900.....600.......keen
4.....6100....1200.....1000......stauton
Y
empno....Sal.....Tax.....Returns...name
1.....4500....1000.... 000.......robert
2.....5750.....1200....900.......john
3.....4000.....900.....600.......keen
4.....6100....1000.....1000.......stauton
If you see the above tables I have data mismatch in X and Y tables for the same empno.
I need to write a query which shows emp no and columns(name of col) where the data mismatch has occured.
I came up with a query which I have to write for every individual column to get the mismatch.
Since there 120 columns it is pretty hard task..i m looking for a logic where I can write a query which shows mismatched data in columns.
Expected Output
table z
col1..col2
1......tax
3......tax
Appreciate your help.
Logic Error
Jul 20, 2005Declare @mx intSet @mx = (select max(score)from ,,,where ,,,,andsubstring(city,1,1)='g')select score,@mxfrom ,,,,where ,,,, andsubstring(city,1,1)='g')group by scoregothis seems to work for small tablesbut when table is big and substring(city,1,1)='g')condition is not met it takes aweful time like goesin an infinit loop.--Sent by 3 from yahoo part of comThis is a spam protected message. Please answer with reference header.Posted via http://www.usenet-replayer.com
View 1 Replies View RelatedNested SQL Or IF END LOGIC ?
Aug 13, 2007Below is a sample data set. Each episode consists of several unique chart_component_key. I need to be able to pull the MAX status_date for the episode, but only if all deficiency_status is equal to 'C', which signifies that the entire episode has been completed. I thought either nested select statements or if/end logic might work but I am stuck.
episode_key
chart_component_key
deficiency_type
deficiency_status
status_date
13789881
173398
408
C
8/4/2007
13789881
173488
409
S
8/4/2007
13789881
173703
409
S
8/6/2007
13789881
174568
1028
S
8/7/2007
13789881
176213
421
S
8/9/2007
13789881
176214
421
S
8/9/2007
13789881
176215
421
S
8/9/2007
13789881
176216
421
S
8/9/2007
13789881
176218
421
S
8/9/2007
13789881
176219
406
D
8/9/2007
Can someone help me here?