SSIS Load data

Question

SSIS Load data

parthi-1705

SSCrazy Eights

Points: 9277
More actions
November 17, 2010 at 9:11 am

#229338

Hi,
Pls have a look on the below
Client:1 is having data as empid,empname,address,pin,phone,city,mobile,gender
Client:2 is having data as empid,empname,address,phone,city,pin,mobile,gender
Client:3 is having data as empid,empname,address,phone,city,pin,mobile
Client:4 is having data as empid,empname,address,phone,city,pin,country
.......
Client:65 is having data as empid,empname,address,phone,city,,state,country,pin
The above are the input for me from different clients they will be giving data in textfile with their own delimiters some as ,;"<TAB> what i have to do is i need to push data from this file to my table having similar columns for those
columns not present in files i want to insert NULL and i need to harcode clientid(1,2,3...etc) and finaly to table
In a package Source i have choosen is Flatfile staring from 1 to 65 and i have choosen only one destination [OLE DB Destination]
How to Process this what transformation i need to do inorder to achive this.
Whether the flat file for the above choosen by me is correct or any other way
All the files are in FTP path for each file there is folder [Client1,Client2..etc] there the text files will be there i will
be taking from there File names will be as Client1_todaysdate , Client2_todaysdate... this week on monday i need to load and next week there will be new file with different data i need to load
One thing is sure here once the client has given the format[delimiter] they will not change
Advice me on this since it is my first implimentation.
Any other way it can be done(simple).
Dont say it is cross post of
http://www.sqlservercentral.com/Forums/Topic1021535-391-1.aspxsince it is BI form i have posted here so that i can get solution
and i am using 2005 and 2008 so if i can solution i can implement in any one
Thanks
Parthi
Thanks
Parthi

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

parthi-1705 (11/17/2010)
Dont say it is cross post of
http://www.sqlservercentral.com/Forums/Topic1021535-391-1.aspxsince it is BI form i have posted here so that i can get solution
and i am using 2005 and 2008 so if i can solution i can implement in any one
Thanks
Parthi

Parthi

It's a cross-post, no matter how you try to justify it. Nobody wants to waste their time replying to this thread, only to find somebody has already said a similar thing on the other thread.

John

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 2

John Mitchell-245523 (11/17/2010)
Parthi
It's a cross-post, no matter how you try to justify it. Nobody wants to waste their time replying to this thread, only to find somebody has already said a similar thing on the other thread.
John

Hi

Since i did not get the solution for the above in 2008 post i have done here nothing wrong in how we get the solution.only thing is to get the correct solution.Some may be coming here (Home » SQL Server 2005 » Business Intelligence ) alone so that they might have solution thats why i have posted here.Goal is to get solution

Thanks

Parthi

Thanks
Parthi

steveb. SSC-Forever Points: 46733 More actions · Answer 3

It seems like you could use 65 seperate flat file connections and then use a derived column to add an ID to differnetiate the files in the destination table.

or you could group together the files that are the same and do for-each loop and loop through these files.

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 4

steveb. (11/17/2010)
It seems like you could use 65 seperate flat file connections and then use a derived column to add an ID to differnetiate the files in the destination table.
or you could group together the files that are the same and do for-each loop and loop through these files.

How to use derived column here

i cant group files since each one will have more or less columns so that is not possiable here

Find the sample attachement of 4 Client files and destination

Thanks

Parthi

Thanks
Parthi

Pravasis Default port Points: 1455 More actions · Answer 5

You can use a script component to read a file which needs to be used as a source. Basically you can read the first line as per the commas. Find out the column names fron there. Then read the next lines accordingly. There only you can put the null values in the missing columns. Finally you can add the rows to the output buffer. From there onwards you can use your regular transformation and finally your destination. You can try the following link to read the data from a file.

Regards,
Pravasis

steveb. SSC-Forever Points: 46733 More actions · Answer 6

parthi-1705 (11/17/2010)
steveb. (11/17/2010)
It seems like you could use 65 seperate flat file connections and then use a derived column to add an ID to differnetiate the files in the destination table.
or you could group together the files that are the same and do for-each loop and loop through these files.
How to use derived column here
i cant group files since each one will have more or less columns so that is not possiable here
Find the sample attachement of 4 Client files and destination
Thanks
Parthi

a derived column is fairly simple, it just adds a column to the data flow

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 7

steveb. (11/18/2010)

a derived column is fairly simple, it just adds a column to the data flow

Derived column cant have multiple input

/*

Cannot create connector.

The destination component does not have any available inputs for use in creating a path.

*/

this is it just adds a column to the data flow fine but not able to have multiple input to derived column

Thanks

Parthi

Thanks
Parthi

steveb. SSC-Forever Points: 46733 More actions · Answer 8

well you would need a seperate one for each data source...

sorry i didn't make that clear

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 9

steveb. (11/18/2010)
well you would need a seperate one for each data source...
sorry i didn't make that clear

It means that i have to create 60+data set correct for each dataset each files

Thanks

Parthi

Thanks
Parthi

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 10

Hi All

I think i has got the solution (not for sure) UNION ALL transformation under split and join transformations will be doing this type of work

The Union All transformation combines multiple inputs into one output. For example, the outputs from five different Flat File sources can be inputs to the Union All transformation and combined into one output.

Just now see and now i going to try with this

Thanks

Parthi

Thanks
Parthi

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 11

parthi-1705 (11/18/2010)
Hi All
I think i has got the solution (not for sure) UNION ALL transformation under split and join transformations will be doing this type of work
The Union All transformation combines multiple inputs into one output. For example, the outputs from five different Flat File sources can be inputs to the Union All transformation and combined into one output.
Just now see and now i going to try with this

The Union All can indeed combine multiple paths together. But, you have to have the same number of columns on each patch and the matching columns must have compatible data types. If you are missing a column, just add one with a derived column.

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

Pravasis Default port Points: 1455 More actions · Answer 12

Yes, either you can use 60 data sources and 60 derived columns then finally a union all or you can use a script component as source and put the DFT in a for each loop container.

Regards,
Pravasis

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 13

In SSIS, there are many ways to get to your goal. Choose the one you feel comfortable with (and preferably the most performant and scalable one). Don't forget that a data flow can't be configured dynamically out-of-the-box.

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

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 14

hi

I have used the Source ---->derived column and unionall ---->Destination it has run sucessfully.i need to check duplicates in this ,that is i need to find the duplicate record in the table with the incoming record

for eg: if client1 emp1 record is already in the table i should not insert into table rest of the data need to be inserted which transformation i need to use. whether i can use Fuzzy grouping transformation if so how(how does the fuzzy grouping can be implimented here)

Thanks

Parthi

Thanks
Parthi