Need your help on SSIS project please.

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 2008 Forums

SSIS and Import/Export (2008)

Need your help on SSIS project please.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-12-30 : 12:22:06

[code]Hi,

I need to import about 20 Excel files into 30 different tables and I need your advices/suggestions what is the best efficient ways to
to these. I was thinking using SSIS loop through all the files but my SSIS experience is limited and need some help on how to do these.

Does anyone knows where can I find a similar project on web sites. I try to google it but no luck.

I looked into the SSIS web site below as well.
http://www.sqlis.com/sqlis/

Thank you in advance.

-- Here is sample of Excel. CustImportFile.xls

CustLoanNum CustContactName ContactId ContactTitle BankName Address PhoneNum
----------- -------------------- ----------- -------------------- -------------------- ------------------------- ------------
120606 Peter Smith 12345 Manager BofA 12345 Brookhurst st 562-598-8942
120606 John2 Doe 89641 Supervisor BofA 12345 Brookhurst st 562-452-7219
120606 User2 Lee 56173 Lead BofA 12345 Brookhurst st 562-452-6347
896475 Lisa Tran 87451 Director Chase 45678 Westminster blvd 310-794-3486
896475 Bill Smith 64757 VP Chase 45678 Westminster blvd 310-872-3945

Happy New Year to all,

Cheers.

------------------------------------------------------------------------------------------------------

I need to accomplish the following tasks and any suggestions would greatly appreciated.
1. Loop through all excel files and insert into staging tables.
2. Once done, insert data into different tables.

IF OBJECT_ID('StagingCustLoan', 'u') IS NOT NULL
DROP TABLE StagingCustLoan
GO

CREATE TABLE StagingCustLoan
(
CustLoanNum VARCHAR(8) NOT NULL,
CustContactName VARCHAR(20) NULL,
ContactId INT NULL,
ContactTitle VARCHAR(20) NULL,
BankName VARCHAR(20) NULL,
Address VARCHAR(25) NULL,
PhoneNum VARCHAR(12) NULL
)
GO

INSERT StagingCustLoan VALUES ('120606', 'Peter Smith', 12345, 'Manager', 'BofA', '12345 Brookhurst st', '562-598-8942'),
('120606', 'John2 Doe', 89641, 'Supervisor', 'BofA', '12345 Brookhurst st', '562-452-7219'),
('120606', 'User2 Lee', 56173, 'Lead', 'BofA', '12345 Brookhurst st', '562-452-6347'),
('896475', 'Lisa Tran', 87451, 'Director', 'Chase', '45678 Westminster blvd', '310-794-3486'),
('896475', 'Bill Smith', 64757, 'VP', 'Chase', '45678 Westminster blvd', '310-872-3945');
GO

IF OBJECT_ID('CustLoan', 'u') IS NOT NULL
DROP TABLE CustLoan
GO
CREATE TABLE CustLoan
(
LoanNum VARCHAR(10) NOT NULL,
LoanId INT IDENTITY (1,1) NOT NULL,
BankName VARCHAR(30) NULL,
CusAddress VARCHAR(30) NULL,
UpdateBy VARCHAR(20) NULL,
InsertDt DATE NULL
)
GO

IF OBJECT_ID('Contact', 'u') IS NOT NULL
DROP TABLE Contact
GO
CREATE TABLE Contact
(
LoanNum VARCHAR(8) NOT NULL,
ContactId INT NOT NULL,
CustContactName VARCHAR(20) NULL,
ContactTitle VARCHAR(20) NULL,
PhoneNum VARCHAR(12) NULL,
UpdateBy VARCHAR(10) NULL,
InsertDt DATE NULL
)
GO

SELECT *
FROM StagingCustLoan;
GO

CustLoanNum CustContactName ContactId ContactTitle BankName Address PhoneNum
----------- -------------------- ----------- -------------------- -------------------- ------------------------- ------------
120606 Peter Smith 12345 Manager BofA 12345 Brookhurst st 562-598-8942
120606 John2 Doe 89641 Supervisor BofA 12345 Brookhurst st 562-452-7219
120606 User2 Lee 56173 Lead BofA 12345 Brookhurst st 562-452-6347

896475 Lisa Tran 87451 Director Chase 45678 Westminster blvd 310-794-3486
896475 Bill Smith 64757 VP Chase 45678 Westminster blvd 310-872-3945

SELECT *
FROM CustLoan;
GO

-------------------------------------------------------------------------------

-- step1.

INSERT CustLoan (LoanNum, BankName, CusAddress, UpdateBy, InsertDt)
SELECT DISTINCT
CustLoanNum
,BankName
,[Address]
,'User1' AS UpdateBy
,GETDATE()
FROM StagingCustLoan;
go

SELECT *
FROM CustLoan;
GO

LoanNum LoanId BankName CusAddress UpdateBy InsertDt
---------- ----------- ------------------------------ ------------------------------ -------------------- ----------
120606 1 BofA 12345 Brookhurst st User1 2010-12-30
896475 2 Chase 45678 Westminster blvd User1 2010-12-30

--step2

INSERT Contact (LoanNum, ContactId, CustContactName, ContactTitle, PhoneNum, UpdateBy, InsertDt)
SELECT DISTINCT
CustLoanNum
,ContactId
,CustContactName
,ContactTitle
,PhoneNum
,'User1' AS UpdateBy
,GETDATE()
FROM StagingCustLoan;
go

SELECT *
FROM Contact;
GO

LoanNum ContactId CustContactName ContactTitle PhoneNum UpdateBy InsertDt
-------- ----------- -------------------- -------------------- ------------ ---------- ----------
120606 12345 Peter Smith Manager 562-598-8942 User1 2010-12-30
120606 56173 User2 Lee Lead 562-452-6347 User1 2010-12-30
120606 89641 John2 Doe Supervisor 562-452-7219 User1 2010-12-30

896475 64757 Bill Smith VP 310-872-3945 User1 2010-12-30
896475 87451 Lisa Tran Director 310-794-3486 User1 2010-12-30[/code]

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-08 : 14:59:26

Create one dataflow that links to the excel file and specify the column information for the source.

Use the excel source and the two staging tables as your destinations. when you connect source to destination, specify only the columns you need to insert into staging (or let the column names map themselves).

A second data flow can be used to insert the staging data into the final tables.

When you get this sequence working for one file, you can drag the dataflow into a For each container (add a preceding step to the dataflows to truncate the staging tables).

To make the filename for the connection manager to be dynamic instead of what you predefined,
You will declare a variable called strFolderName and a variable called strFileName. At first, just type in the folder name and a valid file name for testing.

In the previously set up excel file connection, you will go to properties>expressions and specify that the file name will be populated by the variable [User:strFileName]

In the for each container (do a for each file in folder ), you will can point the folder name, or populate it using the variable strFolderName

The filename property can be set inside this for each container to repopulate the strFilename with the actual file name in the loop.

I have done this many times with daily text files that would sometimes have to be reloaded retroactively

There is a good arcticle also here: http://msdn.microsoft.com/en-us/library/ms345182.aspx

Poor planning on your part does not constitute an emergency on my part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-09 : 01:22:27

this is loop you want for iterating through files. Rest is as datagurus suggestion. Add a DFT inside

http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources