package with stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

package with stored procedure

Author

Topic

pippo
Starting Member

1 Post

Posted - 2010-12-21 : 16:49:30

Hi

I am newbie and this is my first package and would like to know
whether my project could be feasible in SQL 2005.

I would like to use an excel file which stores the following
data:
customer last name, customer name and date of birth.

A stored procedure should run on each record and compare
the data between the spreadsheet and a table in SQL 2005.
If the customer last name, customer name and the date of birth match
then I would like to output all the results from sql to a new excel file.

The table in SQL 2005 contains customer data as per below:
customer id, customer name, customer last name, date of birth and address.

Thank you in advance for any suggestions.

Pippo

latch
Yak Posting Veteran

62 Posts

Posted - 2010-12-22 : 13:43:50

Hi pippo,

What i understood from your post is that you need to compare the values in table(cust) with the values in the excel and load the matched columns into new excel file with help of a procedure to compare.

if iam correct,please follow the steps below:

Step 1: create a package with 2 dataflow tasks:
In first data flow task load the data from the excel into a temp table called custTemp.

Step 2: Now,In management studio create a procedure as:
CREATE PROCEDURE [dbo].[usp_NameValidation]

AS
BEGIN

SET NOCOUNT ON;

SELECT a.cname, a.clname,a.dob(your columns)
FROM cust(originalTable) AS a
WHERE a.cname IN
(SELECT b.name
FROM custTemp(TemporaryTable) AS b
WHERE a.clname = b.lastname
AND a.dob = b.dofb);
END

Step 3:In second data flow task ,

(I)take source as oledb
-set connection manager to your server
-Data access mode=SQL Command
-SQl Command Text: dbo.usp_NameValidation(stored procedure)
-map the columns
(II)use data conversion transformation
(III)take a excel destination(new excel file).

Hope it will helpful.

Thanks,
latch

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources