?
Solved

SSIS Data Massage/Concatenation??

Posted on 2010-01-12
8
Medium Priority
?
685 Views
Last Modified: 2013-11-10
Hi,

I am working with SQL 2008. I have two databases on the same server

I am pretty new to SSIS packages and I'm trying to do the following:

Extract data from a table on database "prod", massage the data, and the insert it into a table in database "dev"

I don't know which type of task or data flow option to choose to do the massaging of the data.  The source data has columns like "First_Name" and "Last_Name", by the target table just has a "Display_Name" column so I want to concatenate the two and then insert it if that makes sense.

Also I am using OLE DB Source and OLE DB Destination.. its the tasks in the middle that are giving me problems.

Any help appreciated

-ws



0
Comment
Question by:Westside2004
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 4

Expert Comment

by:Ram4020
ID: 26301219
If it is only about concatenate of first and last name then you dont need a SSIS package. You can create a sql job as both source and destination dbs are in the same instance.
0
 
LVL 12

Expert Comment

by:Chris M
ID: 26301221
Choose source server as the current database server name. Also make it the destination server too (the current database server name).
Then choose source database as "prod" and destination database as "dev".

Another way of achieving this is also by using the Import/Export wizard started by right-clicking on the source database, choose "Tasks", choose "Export data" and follow the wizard.

The third option you have is to issue this select statement:

Insert into dev.schema.MyDestinationTable
Select * from Prod.schema.MySourceTable;

All the best.
0
 
LVL 1

Author Comment

by:Westside2004
ID: 26301237
The schemas are different somewhat, different column names, it's more than just that one column that I need to concatenate, I guess it's really just re-mapping the fields I need to the fields that exist in the target database.    I also am trying to learn to use SSIS so I thought maybe this would be an opportunity.

-ws
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 12

Expert Comment

by:Chris M
ID: 26301251
I had left out the concatenation bit:
To concatenate two or more fields in SQL server, simply use the plus (+) operator.

E.g is your query is like "SELECT FIRST_NAME, LAST_NAME FROM TABLE;" and you want first name and last name concatenated, then your query should look like:

SELECT FIRST_NAME + LAST_NAME FROM TABLE;

If you want a space between the concatenated data, then the query should be like:
SELECT FIRST_NAME + '  ' + LAST_NAME FROM TABLE;

To give the new output field a name "NAMES" then your query should be like:
SELECT FIRST_NAME + '  ' + LAST_NAME As NAMES FROM TABLE;

All the best.
0
 
LVL 1

Author Comment

by:Westside2004
ID: 26301256
I know how to do that via SQL, I was trying to figure how to do that in the context of an SSIS package.
0
 
LVL 12

Accepted Solution

by:
Chris M earned 2000 total points
ID: 26301266
If the schemas are different, then use my earlier script and change the schema names as you wish but maintain it as follows:

[database].[schema].[table_name]

E.g
INSERT INTO [DEV].[DEV_SCHEMA].[DESTINATION_TABLE]
SELECT FIRST_NAME + '  ' + LAST_NAME As NAMES FROM [PROD].[MySchema].[TABLE];
0
 
LVL 12

Expert Comment

by:Chris M
ID: 26301293
SSIS is meant to be even much easier bro.
Just do it graphically by placing the different tasks into your solution to create the desired flow.
0
 
LVL 1

Author Comment

by:Westside2004
ID: 26301429
I don't mind writing SQL at all, but what I am trying to do that via SSIS but I'm not which task to use for the SQL that's all.

I've defined my data source, my destination, but I need the part in between..

-ws
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question