SSIS: Check -&&>Update -&&>Insert

Sep 7, 2007

I have two tables from two different Databases

DB1.dbo.Table1 and DB2.dbo.Table2

eX:

Table 1
KEY LName FName Updated
1 GYM ABC Y
1 TIM ABC N
1 PIN ABC N
2 QWE SAD Y
......
....


Table 2
KEY LName FName Updated
1 JIM ABC Y
2 QWE SAM Y

....
....


1) Table 1 and Table 2 are of same structure.
2) In table2, as in above example, few changes have beeen done for KEY1 AND Update =Y, Similarly KEY= 2 AND UPDATED=Y, like for KEY= 1 LName was changed to JIM instead of GYM and for KEY= 2 FName has been changed to SAM instead of SAD.

3) Now I want to do this in SSIS where
a) Its going to process rows of Table2 and check in table1 according to KEY and UPDATE=Y and update the Table1 with Updated = N and Insert that particulra process row of Table2 into Table1

and hence Resultant of Table1 must be like this

Table 1
KEY LName FName Updated
1 GYM ABC N
1 TIM ABC N
1 PIN ABC N
1 JIM ABC Y

2 QWE SAD N
2 QWE SAM Y
......
....

Can somebody help me how to do this in SSIS. Thnaks a lot in advance

View 1 Replies


ADVERTISEMENT

Package For Update/insert And Check For New Record

Apr 21, 2008

hi,

i'm total newbee on SSIS packages and therefore need guidance.

I want to make a ssis package that (in order):

- check in table (tbl_orders) if there is any new order made
- if new order is made, update column (time_last_change)
- if this order has geography ID (ID_geography) inserted, insert name of geography.

Thank you in advance,

View 2 Replies View Related

Create Trigger To Check Values At Insert/update

Feb 10, 2004

I have never used triggers before and I have tried to solve one problem. If I have the column "currency" in a table and want to make sure that the entered value i valid in relation to another table that contains valid currency formats, I did it like this:

---------------------------------
CREATE TRIGGER [trigger_checkCurrency] ON [dbo].[Client]
FOR INSERT, UPDATE
AS
declare @currency as char(50)
declare @country as char(50)

declare cur cursor for SELECT currency, country
FROMinserted

OPEN cur
fetch cur into @currency, @country
WHILE @@FETCH_STATUS = 0
BEGIN
if not exists(select * from listinfoid where listname = 'currency' and listid = @currency)
begin
set @currency = (cast(@currency as varchar (3)) + ' is not a valid currency')
CLOSE cur
DEALLOCATE cur
RAISERROR (@currency,16,-1) with log
return
end
if not exists(select * from listinfoid where listname = 'country' and listid = @country)
begin
set @country = (cast(@country as varchar (3)) + ' is not a valid contry')
CLOSE cur
DEALLOCATE cur
RAISERROR (@country,16,-1) with log
return
end
else
fetch cur into @currency, @country

END
CLOSE cur
DEALLOCATE cur
update Client set currency = UPPER(currency), country = UPPER(country)
---------------------------------



I use a cursor to handle multiple rows in an update query.
(SQL2000-server)

Is there an easier och better way to do this?
I´m a bit unsure of this code.

Thanx!

/Erik

View 2 Replies View Related

Insert And Update With SSIS

Nov 16, 2007

I use SSIS for data's integration with a flat file to a table in sql server 2005.
In the table, i've a primary key [ArtID]. In the flat file, ArtID is duplicate and i've a date that i already sort to have a historic.
I use a script to know if the row must be inserted or be updated.

I use two sql command to insert and update data in server.

I test this in the script
count = select count(*) from table where primary key = value.
If (count = 0) then insert else update.

When i've a duplicate row in flat file i've a error with the primary key. I don't understand why i've it.

Perhaps the SQL Command commit all the row at the end of the flow.

If it's true, can i change the commit each time the line in flat file is readed !! In this case, how can i do that ??
Or i use a OLE Destination to insert with option table or view (no fast) to have a commit all the line of flat file ??

What do thing about it ??

Thanks a lot

View 3 Replies View Related

SSIS - Insert & Update Packages

Jun 15, 2008

Hi,

I have written one SSIS script for Initial data load. Then everyday, there will be an incremental insert and/or update; which means there needs to be a separate script for Incremental data load.

Currently, script for initial data load has scripts containing SELECT queries. it just copies the data from source table to the destination. How should I go about putting both initial & incremental scripts together? Is there any way or do I need to build 2 separate packages ?
Thanks :)

View 2 Replies View Related

SSIS Delete/Insert/Update

Mar 20, 2006

I am still learning SSIS.

What is the best way to do delete/insert/update in a database in SSIS. I am looking to achieve something similar to what was there in a Data Driven Query in SQL Server 2000. I would like to delete/insert/update the destination table based on the condition that my lookuptable returns

For ex

If "the user in my source table has changed the status" Then

Update the user row in the destination table

Else skip the row

If "the user in my source table has delete flag =1 " Then

Delete the user in the destination table

Else skip the row

If "the user in my source table is new(has a new ID) " Then

Insert the user in the destination table

Else skip the row







Any suggestions ....

Cheers

Siaj

View 6 Replies View Related

Insert Or Update SSIS For Composite Primary Key

Sep 1, 2006

Hi ,

We have scenario like this .the source table have composite primary key columns c1,c2,c3,c4.c5,c6 .when we move the records to destination .we have to check columns (c1+ c2 + c3 + c4 + c5 + c6) combination exist in the destination. if the combination exist then we should do a update else we need to do a Insert . how to achive this .we have tryed useing conditional split which is working only for a single Primary key . can any one help us .

Jegan.T

View 8 Replies View Related

Integration Services :: Insert Update Delete Through Ssis?

Jun 20, 2015

I want only last yesterday data that's why i put the condition at oledb source and it working fine.It fetch previous day of data but at the time of lookup , it lookup all data from the beginning and provide the error of insufficient space.

1.how lookup contain only yesterday data.

2.What to do for lookup all data  (adding space is the solution or something else to do)

3.I want to transfer 100 of tables data everyday. this article is only for transferring one table data.For transferring the data of another table add dataflow task below to Apply stages update or add another sequence container.

View 3 Replies View Related

Integration Services :: SSIS Execute Perfectly But Not Insert / Update In Destination Table?

Nov 25, 2015

I am using SSIS integration between two database. Both databases are sql server 2008.  using many integration but getting problem in two only only two integration giving problem, both are executing perfectly and out put also not showing any error.

but destination table not inserted/updated anything.

first issue integration is using data flow task with oledb source and destination. 
second one is using execute task with for-eachloop container.

View 12 Replies View Related

Need Help For SSIS Package Creation With INSERT,UPDATE Large Amount Of Records Through Business Intelligence Studio

Jun 1, 2006

Hi ,

How to INsert and Update Large Amount of Records (4 Lacs) into Destination Table Through Business Intelligence Studio Using SSIS Pacakge .How to Achieve this .i tryed with left outer join & conditional split but the problem its not able to insert & update records simultaneously . can any one give me a sample .
Thanks & Regards
Jeyakumar.M

View 3 Replies View Related

Check Before INSERT

Aug 6, 2007

I have a pretty standard form that inserts users name, office, and team. It generates a random 10 digit ID for them. How would i got about checking the table to make sure that ID doesn't exist?
Here's my insert code.
        string strConnection = ConfigurationManager.ConnectionStrings["TimeAccountingConnectionString"].ConnectionString;        SqlConnection myConnection = new SqlConnection(strConnection);
        string usercode = GenPassWithCap(9);
        String insertCmd = "INSERT into users (ID, firstname, lastname, office, team) values (@id, @firstname, @lastname, @office, @team)";        SqlCommand myCommand = new SqlCommand(insertCmd, myConnection);
        myCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.VarChar, 10));        myCommand.Parameters["@id"].Value = usercode;
        myCommand.Parameters.Add(new SqlParameter("@firstname", SqlDbType.VarChar, 50));        myCommand.Parameters["@firstname"].Value = txtFirstName.Text;
        myCommand.Parameters.Add(new SqlParameter("@lastname", SqlDbType.VarChar, 50));        myCommand.Parameters["@lastname"].Value = txtLastName.Text;
        myCommand.Parameters.Add(new SqlParameter("@office", SqlDbType.VarChar, 75));        myCommand.Parameters["@office"].Value = dwnOffice.SelectedValue;
        myCommand.Parameters.Add(new SqlParameter("@team", SqlDbType.VarChar, 20));        myCommand.Parameters["@team"].Value = dwnTeam.SelectedValue;
        myCommand.Connection.Open();
            myCommand.ExecuteNonQuery();
 Do I run a completey different select command before hand and try to match that field?

View 1 Replies View Related

Check Data Before Update

Jul 20, 2005

Hello Everybody!I have a POLINE table on a SQL Server 2000 DB. Before I update therecord I need to check that either field, STORELOC or WONUM has dataon it. If both fields are NULL I would like to send a message lettingthe user know that either fields needs data before they can save therecord. If any of the fields have data then, it is OK to save therecord.Could you please let me know how to accomplish this? An example willbe really helpful, I can do this in Access but I do not know how to doit in SQLServer. I was thinking using trigger but there are not reallygood examples.Thanks in Advance!Martin

View 1 Replies View Related

SQL Server 2008 :: Update Null Enabled Field Without Interfering With Rest Of INSERT / UPDATE

Apr 16, 2015

If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?

EXAMPLE:

CREATE TABLE dbo.MYTABLE(
ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,

[Code] ....

If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.

INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
VALUES('John','Smith',NULL)

INSERT INTO dbo.MYTABLE( FirstName, LastName)
VALUES('John','Smith')

INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
SELECT FirstName, LastName, NULL
FROM MYOTHERTABLE

View 9 Replies View Related

Check Box Insert Question

Dec 10, 2003

I have 3 tables - Data, Facility and FacilityKey

The FacilityKey table will hold the Data ID and Facility ID based on a series of check boxes on a form.

My question is what is the best way to do the insert into the FacilityKey table from the form? If 5 facilities are checked I don't want to do 5 separate calls to the database for inserts, but I'm a bit confused on what the best method would be.

Thank you!

View 1 Replies View Related

Check Data Before Insert

Aug 15, 2007

I need help thinking about this problem :-)

I have an SSIS pkg that automatically downloads financial extracts from an ftp site. Once the files are downloaded, I load the extract to a table. The table is first deleted before the insert, so that each time the table has "fresh" data (whatever was in the extract for that day).

Once the extract data is in the table, I load the data into yet another table that combines data from many tables. Simple enough.

Problem is, sometimes when I download the extract, it hasn't been updated yet, so I'm downloading an OLD extract. This old data then gets loaded into the first table. That's ok, because it doesn't really hurt anything. I can always delete the table and reload it if necessary.

The problem occurs when the old data goes from this table into the OTHER table. We don't want old data in this other table!

I need a way to check that I'm not loading the same data 2 days in a row into the OTHER table.

I realize that I might be able to solve this problem without using SSIS, but the solutions I've come up with so far aren't 100% satisfactory. I can use a query to check dates and that sort of thing, but it isn't foolproof, and would create problems if I need to manually force the process though, that is, if I need to override the date logic.

Anyways, I'm wondering if there's an SSIS approach to this problem... I can't rely on timestamps on the data files either. They're not accurate.

This is has been very perplexing to me.

Thanks

View 8 Replies View Related

Check For Column && Insert

Jan 7, 2007

Can you tell me if this is possible? (and how to do it!!)

The application is VS2005, with sql database.

I want to check if a specific column exists in a specific table in the database and if not then add it, all via my application.

I'm happy knowing how to connect to the database & pass sql commands (as I'm doing that anyway to set off backups), but not the actual queries I'd need.

View 3 Replies View Related

Can I Insert/Update Large Text Field To Database Without Bulk Insert?

Nov 14, 2007

I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind.  I've tried using the new .write() method in my update statement, but it cuts off the text after a while.  Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.

View 6 Replies View Related

Help...pls Check What's Wrong....not Able To Do Insert With Sqldatasource

May 9, 2007

Hi frdz,         I m the new user of ASP.NET WEB APPLICATIONS WITH C# LANGUAGE.         I m using SQL SERVER 2005 and SQLDATASOURCE to get or retrieve the data from the database.         I have created the stored procedure for insert and update.         The stored procedure is executing fine when i m running it from sqlserver2005.         My problem is with the web-application page.         I m not able to insert or update data thru that...         Pls check the code and tell me what's missing out ..........SQLDATASOURCE<asp:SqlDataSource ID="srcemp" runat="server" ConnectionString="<%$ ConnectionStrings:empmaster  %>"     InsertCommand="empStoredProcedure" InsertCommandType="StoredProcedure"     UpdateCommand="empStoredProcedure" UpdateCommandType="StoredProcedure"      DeleteCommand="DELETE FROM empmaster          WHERE empid = @empid" DeleteCommandType="Text"     SelectCommand="select * from empmaster " SelectCommandType="Text">     <InsertParameters><asp:Parameter Name="empname" Type="String" /><asp:Parameter Name="address" Type="String" /><asp:Parameter Name="city"  /><asp:Parameter Name="pincode" /><asp:Parameter Name="state" /></InsertParameters> <UpdateParameters><asp:Parameter Name="empname" Type="String" /><asp:Parameter Name="address" Type="String" /><asp:Parameter Name="city"  /><asp:Parameter Name="pincode" /><asp:Parameter Name="state" /></UpdateParameters>     </asp:SqlDataSource>GRIDVIEW  <asp:GridView ID="empGridView" runat="server" AutoGenerateColumns="False"         DataKeyNames="empid" DataSourceID="srcemp"         Width="56px" >             <Columns>    <asp:TemplateField>    <ItemTemplate>    <asp:LinkButton ID="btnDelete" runat="server" CommandName="Delete" OnClientClick="return confirm('Are you sure you want to delete this row ?');">Delete</asp:LinkButton>    </ItemTemplate>    </asp:TemplateField>                                                  <asp:boundfield datafield="empname"            headertext="emp Name"/>           <asp:boundfield datafield="address"            headertext="Address"/>          <asp:boundfield datafield="city"            headertext="City"/>         <asp:boundfield datafield="state"            headertext="State"/>                                     <asp:CheckBoxField            DataField="deleted"            HeaderText="In Existance" />            </Columns>          </asp:GridView>C# code protected void cmdsubmit_Click(object sender, EventArgs e)    {        srcemp.InsertParameters["empname"].DefaultValue = tbcompanyname.Text;        srcemp.InsertParameters["address"].DefaultValue = tbaddress.Text;         srcemp.InsertParameters["city"].DefaultValue = tbcity.Text;        srcemp.InsertParameters["pincode"].DefaultValue = tbpincode.Text;        srcemp.InsertParameters["state"].DefaultValue = cmbstate.SelectedItem.ToString();         srcemp.Insert();}Note :I m not getting a single error msg for the above code in web-page or stored procedure but it does not insert,update or delete the record from the database....Thanxs in adv...pls reply at earliest if possible...What's missing ??can anyone check out..what's wrong ??

View 1 Replies View Related

How To Check If Sql Insert Command Was Successful

Jan 14, 2008

I'm running a pretty standard insert command on a button (myCommand.ExecuteNonQuery();)
 I have a few other methods that run, but I only want them to proceed if the above was inserted successfully, how would i check that?

View 8 Replies View Related

Performing Insert Query With Check

Feb 29, 2008

hii,,i am using asp.net 2005 and sql server 2005.i have a web page in which i can enter details and it gets stored in a table in a database..in the table thrs a column called as sme_id,,what i want is when one inserts a new sme_id from the page,,it should check in the table so tht no duplicate sme_id wil b generated..,,this code is workin fine,,i just want to implement the above condition...here is the insert code which i have used along with sql datasource:::__________________________
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"
ConnectionString="<%$ ConnectionStrings:sme_trackerConnectionString %>"
InsertCommand="INSERT INTO SME_Master(SME_Id, FirstName, LastName, Type_of_SME, Agency_id, Agency_Name, Email, Address, Phone, Mobile, Fax, TimeZone_Id, Experience, City, State, Status, Level_Of_Exam, Other_Comments, Certificate, Expertise)
 VALUES (@SME_Id, @FirstName, @LastName, @Type_of_SME, @Agency_id, @Agency_Name, @Email, @Address, @Phone, @Mobile, @Fax, @TimeZone_Id, @Experience, @City, @State, @Status, @Level_Of_Exam, @Other_Comments, @Certificate, @Expertise)"
OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [SME_Master]">
_______hope u got my problem,,,,,reply asap....thnks in advance

View 11 Replies View Related

Query Question - Check Before Insert

Dec 1, 2003

I have a table, emailaddresses with an emailaddress field.

before i do an insert from a stored proc, i want to check if the emailaddress is already in the database.

pseudo-code:

if emailaddresssparameter is IN emailaddress then
do not insert
else
insert into table
end

i've got the insert statement and the stored proc, but how do i write the check to see if it's already there? I mean i could do a select * from emailaddress wehre emailaddress=emailaddressparam

but how to i test it? if the count=1 then skip?

here's my proc now:

ALTER PROCEDURE dbo.AddOneEmailAddress

(
@emailAddress varchar(255),
@emailID int=0 OUTPUT
)

AS
/* SET NOCOUNT ON */
insert into EmailAddresses
(email_address)
values
(@emailAddress)
set @emailID=@@identity

RETURN @@identity

View 2 Replies View Related

T-SQL (SS2K8) :: Insert / Update Triggers When Insert Run Via Script

Oct 23, 2014

I'm working on inserting data into a table in a database. The table has two separate triggers, one for insert and one for update (I don't like it this way, but that's how it's been for years). When there is a normal insert, done via a program, it looks like the triggers work fine. When I run an insert manually via a script, the first insert trigger will run, but the update trigger will fail. I narrowed down the issue to a root cause.

This root issue is due to both triggers using the same temporary table name. When the second trigger runs, there's an error stating that a few columns don't exist. I went to my test server and test db and changed the update trigger so that the temporary table is different than the insert trigger temporary table, the triggers work fine. The weird thing is that if the temporary table already exists, when the second trigger tries to create the temporary table, I would expect it to fail and say that it already exists.I'm probably just going to update the trigger tonight and change the temporary table name.

View 1 Replies View Related

How To Check If An Insert Stored Proc Succeeded

Mar 5, 2007

Hi,
How would I check if an insert via stored proc succeeded? Here's the proc I'm using:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[showtube_addNewSUser]
-- Add the parameters for the stored procedure here
@UserId uniqueidentifier,
@FirstName nvarchar(32),
@LastName nvarchar(32),
@DescShort nvarchar(256),
@DescLong ntext,
@ClanID uniqueidentifier
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
Insert into dbo.Users (UserId, FirstName, LastName, DescShort, DescLong, ClanID)
values (@UserId, @FirstName, @LastName, @DescShort, @DescLong, @ClanID);
END


 
 
I tried adding a Return @@RowCount before the END statement, but it always seems to return -1. Could someone tell me what I'm doing wrong? Thanks.

View 3 Replies View Related

The INSERT Statement Conflicted With The CHECK Constraint

Jan 8, 2008

Hi, I am new to MS SQL Server; as I know Access, MYSQL. I made a form though which I want to insert data to SQL SERVER 2005 Database but i during submission I get the below problem, can any one help.


Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]The INSERT statement conflicted with the CHECK constraint "SSMA_CC$Bcast$msgHTML$disallow_zero_length". The conflict occurred in database "x485i", table "dbo.Bcast", column 'msgHTML'.

/html/n_.asp, line 193

Best Regards,
Imran

View 3 Replies View Related

Help Fixing The UPDATE Statement Conflicted With The CHECK Constraint

Apr 8, 2008

Hi all,While using ACCESS and asp for years, I have just had to move my site onto a new server and the opportunity to move my 50MB access DB to msSQL was too good an opportunity to pass up! I used the Migration tools from MS and the data migrated nicely, I can do everything that I used to be able to do on the forum, except when a new user signs up or a member tries to edit their profile, the following error message comes up:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]The UPDATE statement conflicted with the CHECK constraint "SSMA_CC$PORTAL_MEMBERS$M_ICQ$disallow_zero_length". The conflict occurred in database "DATABASENAME", table "dbo.PORTAL_MEMBERS", column 'M_ICQ'.


I have checked the database and the dbo.PORTAL_MEMBERS table and except for the autoincrement number, uname and pwrd, everything else is set to accept nulls.


I also went though and filled in everything on the form then I could update.


the same error applies when signing up - the only difference is the name of the table


I then tried to change the data straight in the table by opening it in Server Management Studio Express and got the same error if I did not fill in everything. This tells me that it is not the code, much to my relief!



So I am figuring that there is something somewhere in the DB that I have to change? could someone point me in the right direction - and as I am VERY NEW to this database system, and do not know my way around (it took me about 10 minutes to figure out how to open a table! *LOL*) very clear instructions would be appreciated.



Thank you for your time :-)

View 14 Replies View Related

Trigger To Update A Table On Insert Or Update

Feb 15, 2008



Hello

I've to write an trigger for the following action

When a entry is done in the table Adoscat79 having in the index field Statut_tiers the valeur 1 and a date in data_cloture for a customer xyz

all the entries in the same table where the no_tiers is the same as the one entered (many entriers) should have those both field updated

statut_tiers to 1
and date_cloture to the same date as entered

the same action has to be done when an update is done and the valeur is set to 1 for the statut_tiers and a date entered in the field date_clture

thank you for your help
I've never done a trigger before

View 14 Replies View Related

Single Complex INSERT Or INSERT Plus UPDATE

Jul 23, 2005

Hello,I am writing a stored procedure that will take data from severaldifferent tables and will combine the data into a single table for ourdata warehouse. It is mostly pretty straightforward stuff, but there isone issue that I am not sure how to handle.The resulting table has a column that is an ugly concatenation fromseveral columns in the source. I didn't design this and I can't huntdown and kill the person who did, so that option is out. Here is asimplified version of what I'm trying to do:CREATE TABLE Source (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,ssn CHAR(9) NOT NULL )GOALTER TABLE SourceADD CONSTRAINT PK_SourcePRIMARY KEY CLUSTERED (grp_id, mbr_id)GOCREATE TABLE Destination (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,member_ssn CHAR(9) NOT NULL,subscriber_ssn CHAR(9) NOT NULL )GOALTER TABLE DestinationADD CONSTRAINT PK_DestinationPRIMARY KEY CLUSTERED (grp_id, mbr_id)GOThe member_ssn is the ssn for the row being imported. Each member alsohas a subscriber (think of it as a parent-child kind of relationship)where the first 9 characters of the mbr_id (as a zero-padded string)match and the last two are "00". For example, given the followingmbr_id values:1234567890012345678901123456789021111111110022222222200They would have the following subscribers:mbr_id subscriber mbr_id12345678900 1234567890012345678901 1234567890012345678902 1234567890011111111100 1111111110022222222200 22222222200So, for the subscriber_ssn I need to find the subscriber using theabove rule and fill in that ssn.I have a couple of ideas on how I might do this, but I'm wondering ifanyone has tackled a similar situation and how you solved it.The current system does an insert with an additional column for thesubscriber mbr_id then it updates the table using that column to joinback to the source. I could also join the source to itself in the firstplace to fill it in without the extra update, but I'm not sure if theextra complexity of the insert statement would offset any gains fromputting it all into one statement. I plan to test that on Monday.Thanks for any ideas that you might have.-Tom.

View 4 Replies View Related

Insert Stored Procedure With Error Check And Transaction Function

Jul 21, 2004

Hi, guys
I try to add some error check and transaction and rollback function on my insert stored procedure but I have an error "Error converting data type varchar to smalldatatime" if i don't use /*error check*/ code, everything went well and insert a row into contract table.
could you correct my code, if you know what is the problem?

thanks

My contract table DDL:
************************************************** ***

create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)
);


My insert stored procedure is:
************************************************** *****

create proc sp_insert_new_contract
( @contractDate[smalldatetime],
@tuition [money],
@studentId[char](4),
@contactId[int])
as

if not exists (select studentid
from student
where studentid = @studentId)
begin
print 'studentid is not a valid id'
return -1
end

if not exists (select contactId
from contact
where contactId = @contactId)
begin
print 'contactid is not a valid id'
return -1
end
begin transaction

insert into contract
([contractDate],
[tuition],
[studentId],
[contactId])
values
(@contractDate,
@tuition,
@studentId,
@contactId)

/*Error Check */
if @@error !=0 or @@rowcount !=1
begin
rollback transaction
print ‘Insert is failed’
return -1
end
print ’New contract has been added’

commit transaction
return 0
go

View 1 Replies View Related

How To Check Whether An SSIS Package Is Executing

Aug 23, 2006

I have scheduled an SQL Job to run every 15 mins. This runs an SSIS package. But sometimes my package might execute for more than 15 mins. In this case, a second instance of the package would start. I don't want this to happen. So is there any way I can check whether the package is already running before I continue the execution of the same. Right now I am using a mutex to handle this problem. Are there any problems using the mutex and is there a better way to handle this?

Thanks in advance.

Sumesh

View 11 Replies View Related

How To Check The Connection Through SSIS FTP Task

Dec 19, 2007



Hi All,

I am using a SSIS package to import the .xml data file from FTP location and process them into SQL Server DB by using Stored Procedures. I am using FTP Task to retrieve the file from FTP location but it only works when ever internet is available otherwise it fails, I placed this package as a SQL Server Agent Job. Can anybody tell me the way to check the CONNECTION before FTP Task start its process to avoid the errors???

Please see the error below which I get from SQL Server Agent:

"Description: Failed to decrypt protected XML node "DTSroperty" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2007-12-19 04:00:03.66 Code: 0xC001602A Source: AA_Trans Connection manager "FTP Connection Manager" Description: An error occurred in the requested FTP operation. Detailed error description: The password was not allowed . End Error Error: 2007-12-19 04:00:03.67 Code: 0xC002918F Source: FTP AA Transactions Receive FTP Task Description: Unable to connect to FTP server using "FTP Connection Manag... The package execution fails"

Thanks in advance.
Zeeshan.

View 1 Replies View Related

Can I Roll Back Certain Query(insert/update) Execution In One Page If Query (insert/update) In Other Page Execution Fails In Asp.net

Mar 1, 2007

Can I roll back certain query(insert/update) execution in one page if  query (insert/update) in other page  execution fails in asp.net.( I am using sqlserver 2000 as back end)
 scenario
In a webpage1, I have insert query  into master table and Page2 I have insert query to store data in sub table.
 I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance

View 2 Replies View Related

Check Numeric Data Type In SSIS

Sep 3, 2007

Dear All,

the situation is that i have a column data comes from flat file and all i want to do is to check that the incoming column is numeric(12,3) and if the incoming data exceed that size "12,3" exception or redirect the row is happened.



the problem that i try to apply that with the data conversion or Derived column component but it in case of the scale of the incoming data exceed 3 the component trim until 3 scale.



i also try to perform it with the flat file data source component but i face a problem that if the data in the column is empty then flat file data source component read the numeric column as Zero



i hope someone help me coz i need to handle it soon.


best wishes
Maylo

View 5 Replies View Related

SSIS Script Component Date Check

Oct 24, 2007



All,

I am facing below problems in migrating my DTS packages to SSIS.
Could any one answer this ?

1.How to do Tranformations based on condition.Earlier in DTS we are using Activex.
For ex:

if isDate(DTSSource("Due Date")) then
DTSDestination("Due_Date") = DTSSource("Due Date")
end if

I am using script component with below logic:


If Not Row.Due_Date_IsNull Then



If IsDate(Row.Due_Date) Then


Row.Due_Date= Row.EntryDate

End If

End If

Is that correct ?
2.My flat file is having 37 columns.But I am able to see only 21 columns;First 20 columns are showing correctly
and remaining data is showing in 21st column.delimiter is semicolon; Any guess ?

Thanks,
Ravi

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved