Using SSIS

Question

Post reply

Using SSIS

kishoremania

Ten Centuries

Points: 1059
More actions
August 4, 2011 at 9:59 pm

#241191

I am migrating data from one database to another database,
I am showing you the Input data, and the output data how it should looks like...
In my tables I need to take the UserID's from the Users_output2 table and assign it to the Names_Output1, where my input data is from tbl_Input. How it need to be done with SSIS. Can anyone tell me the sql script for it.
tblInput
users FirstnameLastName
mary michelle robert
Nancy Chappell Greg
Names_Output1
FirstnameLastName UserID
michelleRobert2
ChappellGreg 1
Users_Output2
UserId UserName
1 Nancy
2 Mary

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Sriram.RM Hall of Fame Points: 3368 More actions · Answer 1

I believe you are trying to join 2 tables and insert values to the third table...

Insert into Names_Output1 (Firstname, LastName,UserId)

select a.Firstname, a.LastName,b.UserId

from

tblInput a join Users_Output2 b

on a.UserName=b.users

I have not tested the above code as you have not given any DDL/DML statements to create and insert values into the tables. Take a look at the link in my signature to get best help in the forum

Also, if you are doing this operation i dont see a reason for a SQL script. you can do the same with the merge join component thats readily available. Any specific reason for the t-SQL approach?

[font="Times New Roman"]For better assistance in answering your questions
Click Here[/url][/font]

kishoremania Ten Centuries Points: 1059 More actions · Answer 2

I think I didn't presented the question clearly.

These are not my real tables. I gave an example of my situation.

Input table is from my source database, output tables are my destination database. When I need to separate the Users and Names from the Source database as Destination database is normalized. And I want to keep the UserID into Names table. So I need to retrieve the UserID's from the User table and insert those UserID's into Names table for relative Persons.

Sriram.RM Hall of Fame Points: 3368 More actions · Answer 3

u can use the conditional split component if im not wrong... try searchin BOL on Conditional Split

[font="Times New Roman"]For better assistance in answering your questions
Click Here[/url][/font]

1Ankit1 Ten Centuries Points: 1253 More actions · Answer 4

It would help you if you could post zombie DDLs for your tables. Here it seems that the input table does not contain the UserID. So if you need to populate values for this UserID, there are more than one ways you can do it.

So tables strucs here would help.

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 5

ankit.shukla1105 (8/6/2011)
It would help you if you could post zombie DDLs for your tables. Here it seems that the input table does not contain the UserID. So if you need to populate values for this UserID, there are more than one ways you can do it.
So tables strucs here would help.

Ok... I know I'm going to hate myself for asking but what's a "zombie DDL"?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kishoremania Ten Centuries Points: 1059 More actions · Answer 6

kishoremania

Ten Centuries

Points: 1059

August 8, 2011 at 11:00 am

#1365385

what is Zombie DDL's Script....

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

I'm sorry but the tables in the destination database do not appear to be normalized.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 8

kishoremania (8/4/2011)
I am migrating data from one database to another database,
I am showing you the Input data, and the output data how it should looks like...
In my tables I need to take the UserID's from the Users_output2 table and assign it to the Names_Output1, where my input data is from tbl_Input. How it need to be done with SSIS. Can anyone tell me the sql script for it.
tblInput
users FirstnameLastName
mary michelle robert
Nancy Chappell Greg
Names_Output1
FirstnameLastName UserID
michelleRobert2
ChappellGreg 1
Users_Output2
UserId UserName
1 Nancy
2 Mary

If I understood you correctly , the query will be

Assuming you have to insert ID into Users_Output2 and select the Name from tblInput and pick the related ID from Names_Output1

Insert into Users_Output2(UserID,UserName)
Select B.UserID,A.Users from tblInput A inner join Names_Output1 B
On A.FirstName=B.FirstName and A.lastname=B.LastName

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 9

Insert into Users_Output2(UserID,UserName)

Select B.UserID,A.Users from tblInput A inner join Names_Output1 B

On A.FirstName=B.FirstName and A.lastname=B.LastName

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

kishoremania Ten Centuries Points: 1059 More actions · Answer 10

I reformed the question for understanding easily. Here it is..

I have 2 databases in different sql servers. One is my source database with plain tables(staging), other is my destination database with relational tables. I need to create a package for migrating data from plain tables into relational tables. I will present you one of my issue i am facing.

Plain table (Source): Address

col1: LocationName

Col2: City

Col3: State

Col4: OfficeType

Relational Tables(Destination):

Locations

Col1: LocationID

Col2: LocationName

Col3: LocationCity

Col4: LocationState

OfficeTypes:

Col1: OfficeTypeID

Col2: OfficeTypeName

LocationXOfficeTypes:

Col1:LocationXOfficeTypeID

Col2:LocationID

Col3:OfficeTypeID

I need to take the data from the source tables and populate 3 destination tables. This is very similar scenario in Normalized databases. I need to insert Unique Locations into Locations table and Unique OfficeTypes into OfficeTypes Table. And finally need to populate LocationsXOfficeTypes table with the ID's of Locations and OfficeTypes tables.

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 11

Invalid Number of columns in Source and destination.

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

kishoremania Ten Centuries Points: 1059 More actions · Answer 12

Can you be more descriptive in your comment. Where you see invalid number of columns?

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 13

kishoremania (8/8/2011)
I reformed the question for understanding easily. Here it is..

This helps.

I have 2 databases in different sql servers. One is my source database with plain tables(staging), other is my destination database with relational tables. I need to create a package for migrating data from plain tables into relational tables. I will present you one of my issue i am facing.

Okay, the generic problem is the same as when dealing with fact and dimension tables, and you've got a new dimension coming in which you're translating off the fact table. This gets ugly.

First you need to determine if you've got new dimensions, so you get two distinct lists, LocName/City/State, and OfficeTypes.

You confirm they exist in your Relational tables Locations and OfficeTypes. If not, Insert them. Now, how?

Create a dataflow with two sources, once for each of the SELECT DISTINCT col FROM foreigntablename that you need to doublecheck. Then, do a lookup off the core tables in the target database and determine existance. Now, error check the rows on the lookup, and redirect on failure. Now send only the FAILED rows to your OLEDB destination of these tables.

Once that Dataflow is completed, you'll move on to the dataflow that handles this component:

LocationXOfficeTypes:
Col1:LocationXOfficeTypeID
Col2:LocationID
Col3:OfficeTypeID

Bring in the entire source table, and then do a pair of lookup components for the necessary IDs. Those ID fields are all you're going to care about from that point forward. Now, again, do a lookup against the LocationXOfficeTypes, and find failed row existances. Bulk insert these.

You don't need to worry about updates here because the lookup tables don't hold extraneous information or additional attributes that might change outside of the business key structures. If you need to be conerned about Deletes, however, you'll need to use a staging table on the target database for all the combinations for LocationXOfficeTypes, and then do a two way compare, looking for missing information in both directions, deleting and inserting as appropriate.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

In the thread at http://www.sqlservercentral.com/Forums/Topic1156433-148-1.aspx#bm1156467

you said:

kishoremania (8/8/2011)
I didnot started creating package (Analyzing Stage). I think we can use 2 OLEDB command transformations (sql queries with distinct clause) in populating Locations and OfficeTypes. But how about LocationsXOfficeTypes? I am not good in SSIS

So, my question is this:

How do you rate yourself on TSQL? Think about how you would solve this problem in TSQL. Have you done that?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events