SSIS HELP.

Question

SSIS HELP.

rocky_498

SSCertifiable

Points: 6532
More actions
October 11, 2010 at 8:43 pm

#145632

I have three questions, I hope some 1 definite gonna help me.
1) How i can fix this problem in SSIS.
I am trying to update few fields from flat file source to OLE DB "Target" The package didn't work because i.e From ten fields 4 fields are blank" May be that's why package giving me red color. Any suggestion.
2) How i can Map three different columns from one column.
For Example:-
Source Column:-
BPRDFT CHAR(2)
BPRFPT CHAR(4)
Target Column:-
CODEVARCHAR(12)
3) How i can grab data from 4 different table and populate one table.
For Example:-
Source, T1, T2, T3, T4.
Target:- Z1
Note:- Where should i use join.
Please help me out. How i can fix these problems in SSIS. and sorry for two many questions in one request. Thanks in advance.

Viewing 12 posts - 1 through 11 (of 11 total)

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

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 1

Hi,

For Question 1: Missing data can virtually mean anything. Creatre a data staging platform where you would add meaning to all data anomolies

For Question 2: Please be specific. I did not understand

For Question 3: Use a join based on some key column which persists in all four tables.

Raunak J

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

In order to help people help you, you'll want to ask your question more better. You can check out the link in my sig to a detailed article on how to ask question on these forums.

Regarding your questions:

1. Check the nullability of your columns in SQL Server and make sure that when missing data is imported, it is shown as NULL and not as an empty string. (NULL and '' are not the same)

2. It depends on how you want to combine those columns. If they contain the same data, use a UNION (with 2 sources). If it is a calculation, use a derived column. If else, use a (un)pivot transformation. You need to be more specific here.

3. Again, it depends on how you want to combine those tables. If they contain the same type of data, use 4 sources and a UNION. If you need to combine data of different types from those tables, create a join (preferably directly in T-SQL).

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

rocky_498 SSCertifiable Points: 6532 More actions · Answer 3

Thanks for your reply guys! and sorry for not be so specific.

1) Flat file has missing columns. and OLE DB Target don't accept Null value. All of my projects doing through SSIS. * Which transformation should i use b/t Flat file(Source) and OLE DB (Destination) to fix this problem.

2) In documentation showing i have to map two different columns (Source File) with one column in (Target OLE DB). Control Flow -> Flat File Source -> Data Conversion -> OLE DB Destination (Right here i m doing mapping one by one).

3) soruce (OLE DB right here i m using sql command to join all table statement works fine in SSMS but not in SSIS. I will post error message exactly. ) Let me try with what you said use Transformation (Merge or Join). Thanks.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

1. Use a derived column with the following expression:

ISNULL(myCol) ? "Dummy value" : myCol

2. But how can you put two columns into one column? Do you have to perform some sort of calculation, or are you going to multiply the number of rows by 2? E.g.

myCol1 myCol2 measure

"Test" "Test2" 15

becomes

Col1 measure

"Test" 15

"Test2" 15

If this is the case, you need to use the Unpivot transformation.

3. Normally you should be able to join tables in your OLE DB Source. Post the error message and we'll take a look at it.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

rocky_498 SSCertifiable Points: 6532 More actions · Answer 5

Thanks Man your reply help Alot. This is my second week at new work that's y i m so excited/nerves!!

1) We want to keep those blank entry if next time if client want to enter something so he can. I really appreciate if you explain me in detail answer of my first question. If you can send any screen that would be awesome.

2) In documentation two fields are mapping with one target table. For Example.

OWNER_FIRST || OWNER_LAST Varchar(30) "Mapping With" OWNER_NAMEVARCHAR(30)

Note:- So client on the front end can enter both first name and last name in one column.

3) I will post "Error Message "

I am sorry to ask to many questions i don't know y google didn't help me alot in this case.

Thanks for your time. or you can e.mail me rocky_498@hotmail.com

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

1. I already answered your question: use a derived column with the expression I gave you. Of course, adapt the expression to your situation.

2. Also use a derived column for this one: OWNER_FIRST + " " + OWNER_LAST

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

rocky_498 SSCertifiable Points: 6532 More actions · Answer 7

rocky_498

SSCertifiable

Points: 6532

October 14, 2010 at 10:55 pm

#1237297

You are the man! you solve my issue. Thanks.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 8

No problem. Glad to help.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

rocky_498 SSCertifiable Points: 6532 More actions · Answer 9

Bro Here is one problem:-

Source Table:-

Owner_Address || Owner_Street

Cont_Address || Cont_Street

Emp_Address || Emp_Street

Target Table:-

1) Address

2) Address_Type

Questions:-

1) I want to Mapping Owner_Address || Owner_Street , Cont_Address || Cont_Street and Emp_Address || Emp_Street with Address. I think I can't use in "derived column" Transformation "[Owner_Address]+[Owner_Street]+[Cont_Address]+[Cont_Street]+[Emp_Address]+[Emp_Street] and map new "Derived Column" with "Address

Note:- Please tell me am i right or wrong?

2) Second question, in target table the field Address_type. If the data comming from "Owner" Address_Type should be "Owner" and if the data coming from "Cont" Address_Type should be "Cont" or/if the data coming from "Emp" Address_Type should be "Emp". Please tell me how i can do this one IN SSIS.

Thanks in advance.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 10

Hi.

You can do this in SSIS by using 3 OLE DB Source components and then merge them together with a UNION ALL component. The address type can be determined with a derived column.

Or you can use this simple T-SQL statement in one OLE DB Source:

SELECT Owner_Address + Owner_Street AS [Address], 'Owner' AS Address_Type

FROM source_table

UNION

SELECT Cont_Address + Cont_Street AS [Address], 'Cont' AS Address_Type

FROM source_table

UNION

SELECT Emp_Address + Emp_Street AS [Address], 'Emp' AS Address_Type

FROM source_table

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

rocky_498 SSCertifiable Points: 6532 More actions · Answer 11

Thanks. I used derived column transformation and then i used multicast. Thanks for your reply and ur time:-D:-D