SSIS Data Massage/Concatenation??

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



LVL 1
Westside2004Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Chris MConnect With a Mentor Consulting - Technology ServicesCommented:
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
 
Ram4020Commented:
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
 
Chris MConsulting - Technology ServicesCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

 
Westside2004Author Commented:
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
 
Chris MConsulting - Technology ServicesCommented:
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
 
Westside2004Author Commented:
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
 
Chris MConsulting - Technology ServicesCommented:
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
 
Westside2004Author Commented:
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
All Courses

From novice to tech pro — start learning today.