Building a query into a SSIS package
I have a simple package that imports data from an excel file to a 2008 R2 database.
here is a sample of the excel file
54 Siobhan Kennedy Health
22 Peter OChahan Education
31 Ryan Harkins Finance
My table in the database looks like this:
CREATE TABLE [dbo].[Employee](
[EmployeeID] [int] NULL,
[FirstName] [nchar](50) NULL,
[LastName] [nchar](50) NULL,
[Department] [nchar](50) NULL)
I want to import the data into the database, splitting the name column into firstname and lastname using the SSIS package.
So far (on the data control tab) I have an 'Excel Source' Box and an 'OLE destination' Box.
I think i need to pick an option from the 'Data Flow Transformations' menu in the toolbox. Is this correct?
Thanks,
Zoe
October 26th, 2010 6:36am
Yes you are correct. But what is the logic of splitting the name into first and last name. If logic is clear (like occurance of space) then a derived column can be used and appropriate expression can be written to create 2 new columns.Nitesh Rai- Please mark the post as answered if it answers your question
Free Windows Admin Tool Kit Click here and download it now
October 26th, 2010 6:41am
Thanks Nitesh,
Yes thats what I mean - Ive been trying to use the Derived column dialog box, but I cant make sense of it.
Ive put two derived column names FirstName and LastName - selected 'add as a new column' - but what should I enter under 'Expression'?
Thanks for your help
October 26th, 2010 6:51am
FName: SUBSTRING(Name,1,FINDSTRING(Name," ",1))
LName: RIGHT(Name,LEN(Name) - FINDSTRING(Name," ",1))Nitesh Rai- Please mark the post as answered if it answers your question
Free Windows Admin Tool Kit Click here and download it now
October 26th, 2010 7:14am
Thanks Nitesh, that worked great!
Zoe
x
October 26th, 2010 7:20am