Insert statement issue

Question

Insert statement issue

themissnlink

Ten Centuries

Points: 1049
More actions
September 3, 2008 at 1:48 pm

#154691

Is there a way to do an insert statement and an import into a database in the same data flow task?
What I have is a .csv file with an id, emp_social, emp_no.
I want to add the client name and id to these columns, which would be id, emp_social, emp_no, client_id, and client_name.
I need to insert the client_id and client_name to all of these records upon the insert.
Is there a way to do this? Possibly a select * type of insert?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Chinna SSCertifiable Points: 5408 More actions · Answer 1

but where are u getting this id and client name from ...plz explain it clearly so that there would be a better to chance to get the answer...

Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman

themissnlink Ten Centuries Points: 1049 More actions · Answer 2

The client name is just the name of the company it is coming from and the client id is an abbreviation based on that.

For example:

client_name: Jay High School

client_id: JHS

This data is manually inserted on each data flow task that needs it.

Chinna SSCertifiable Points: 5408 More actions · Answer 3

So do u have a table with column names --id, emp_social, emp_no.,cline_id,client_name in the destination

i want to know if u want to match based on name and insert it or insert jsv to all rows ...let me know accord to whchi we can move fwd

Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman

nilesh.dhomse SSC-Addicted Points: 485 More actions · Answer 4

you can use `Execute SQL Task` on control Flow Task and then use data flow task for further process.

hope this helps 😉

thanks

Nilesh

themissnlink Ten Centuries Points: 1049 More actions · Answer 5

The table (destination) is _id (IDENTITY), emp_ssno, emp_no, client_id, client_name.

The only information coming from the .csv file is emp_ssno and emp_no. _id is an IDENTITY that is generated upon insert. Client_id and Client_name are always NULL, which is where I want to insert the client information.

For now I have done a SQL Task, located after the Data Flow Task, that inserts the client information when the columns are NULL.

UPDATE emp

SET client_id = 'id',

client_name = 'name'

WHERE client_id IS NULL

That is the current code I am using to do this task at the moment. I just know there has to be a better way to do it.

Chinna SSCertifiable Points: 5408 More actions · Answer 6

Is the Client name and id same for all the data if so why dont u set default in ur dest table ......

if not where do u get it from ....

anyway i believe u can use derived columns task to add new columns or add column value to a column in ssis ...

u vl have expressions in derived columns which u can try i belive .....

Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman

themissnlink Ten Centuries Points: 1049 More actions · Answer 7

The client_id and client_name are the same for each file (each file has a different client) I import into the database. The database itself is much bigger and has multiple clients.

I will try your suggestion. Thanks for the reply.