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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics