How to Insert or Update Records from Source to Destination using Oracle 8i DB

Hi !

I need help for SSIS Pacakge. using condtional Split How to insert One records with Multiple time depending on Source column value .Is there possible to wrtie the condition in Conditional split.

For Exmaple :

Source Table Name : tbl_source

following Column Name: col_Name1,Col_Name2,Col_Name3, col_Id,Col_Descrip

table contain only one records:GRD1,SRD1,FRD1,100,Product

I want Insert the Destiantion table the Follwing Condition. using Conditional Split.

1)Cond1 (!(ISNULL(GRD1))

2)Cond2 !(ISNULL(SRD1))

3)Cond3 !(ISNULL(FRD1))

I need the Following output

Destination Table Name : tbl _Dest(for One record in source table i need following records in destination)

Coulmn Name , Column Value , ID

Row 1 GRD GRD1 100

Row 2 SRD SRD1 100

Row 3 FRD FRD1 100

How to achieve this result. can u anyone help me.using Conditional split iam getting only first condition Result.

Thanks & regards

M.Jeyakumar

Hi,

You can not do it thru Conditional split. Use script component to do this.

|||Have you try using the Multicast task?|||

Hi , thanks for your repley. can you give samples control flow for this one .

Thanks & regards

Jeyakumar

|||

Maybe something like this...

Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1

Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2

Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3

One Multicast Task that split into 3 transformations.

|||

Hi HienPhilly,

Thank you for your kind of Help. Now its working fine. i designed the above dataflow diagram.now its working fine.i need another doubt in using Lookup in SSIS.

There are two Tables .

1) Product_ tbl

2) Master_Prod_tbl

Using Lookup i want all the Product_tbl column values(i.e Source table) only if Matching the Poduct_tbl_ProductID=Master_Prod_tbl

How to achieve this one ?

the following data flow i am using

1) Product_tbl -> Lookup-->Dstination table

in the Lookup the follwing sql query i wrote:

Select p.GDR,p.CUSIP,p.ISING,p.SEDUL FROM Product_tbl p,Master_Prod_tbl m

WHERE p.CODE=m.CODE

but it didn't work? pls give me your suggestion.

Thanks & Regards

Jeyakumar.M

|||

I don't think you are using it correctly. Under the Reference Table tab, you should have your master_prod_tbl table. Under the Column tab is where you map your lookup column. And if you only want the matched records, you have to Configure Error Output...

|||

Hienphilly wrote:

Maybe something like this...

Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1

Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2

Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3

One Multicast Task that split into 3 transformations.

i think you mean "transformation" instead of "task".|||

Hi Very thankful for your Help. The Problem was i am not configure the Error OutPut. Now its working fine.

I need another Help using Oracle 8i Database

i have two sql statement :

1) Source Sql Statement(select)

2) Update or Insert

How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.

Thanks & Regards,

M.Jeyakumar

|||

Jkumar wrote:

i have two sql statement :

1) Source Sql Statement(select)

2) Update or Insert

How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.

Sorry, I'm not really understanding. Do you want to update some data using data in the SSIS pipeline? If so, use the OLE DB Command component.

-Jamie

|||

Hi ,

Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.

Using BI How to INSERT Or UPdate Records from Source to Destination.

Condition.:

1) The source records are New Records t hen The Destination should b e INSERTED

2) Already is there then it should be UPDATED

.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.

can i use the Sql command for Insert or Update with Where Condition.

Thanks & regards,

M.Jeyakumar

|||

Jkumar wrote:

Hi ,

Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.

Using BI How to INSERT Or UPdate Records from Source to Destination.

Condition.:

1) The source records are New Records t hen The Destination should b e INSERTED

2) Already is there then it should be UPDATED

.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.

can i use the Sql command for Insert or Update with Where Condition.

Thanks & regards,

M.Jeyakumar

I presume by "SQL Command" you mean "OLE DB Command". The answer is yes, you can use it. And it SHOULD work against Oracle.

-Jamie

How to Insert One Row with Multiple time depding on Source Column value in SSIS Pacakge

Hi !

I need help for SSIS Pacakge. using condtional Split How to insert One records with Multiple time depending on Source column value .Is there possible to wrtie the condition in Conditional split.

For Exmaple :

Source Table Name : tbl_source

following Column Name: col_Name1,Col_Name2,Col_Name3, col_Id,Col_Descrip

table contain only one records:GRD1,SRD1,FRD1,100,Product

I want Insert the Destiantion table the Follwing Condition. using Conditional Split.

1)Cond1 (!(ISNULL(GRD1))

2)Cond2 !(ISNULL(SRD1))

3)Cond3 !(ISNULL(FRD1))

I need the Following output

Destination Table Name : tbl _Dest(for One record in source table i need following records in destination)

Coulmn Name , Column Value , ID

Row 1 GRD GRD1 100

Row 2 SRD SRD1 100

Row 3 FRD FRD1 100

How to achieve this result. can u anyone help me.using Conditional split iam getting only first condition Result.

Thanks & regards

M.Jeyakumar

Hi,

You can not do it thru Conditional split. Use script component to do this.

|||Have you try using the Multicast task?|||

Hi , thanks for your repley. can you give samples control flow for this one .

Thanks & regards

Jeyakumar

|||

Maybe something like this...

Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1

Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2

Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3

One Multicast Task that split into 3 transformations.

|||

Hi HienPhilly,

Thank you for your kind of Help. Now its working fine. i designed the above dataflow diagram.now its working fine.i need another doubt in using Lookup in SSIS.

There are two Tables .

1) Product_ tbl

2) Master_Prod_tbl

Using Lookup i want all the Product_tbl column values(i.e Source table) only if Matching the Poduct_tbl_ProductID=Master_Prod_tbl

How to achieve this one ?

the following data flow i am using

1) Product_tbl -> Lookup-->Dstination table

in the Lookup the follwing sql query i wrote:

Select p.GDR,p.CUSIP,p.ISING,p.SEDUL FROM Product_tbl p,Master_Prod_tbl m

WHERE p.CODE=m.CODE

but it didn't work? pls give me your suggestion.

Thanks & Regards

Jeyakumar.M

|||

I don't think you are using it correctly. Under the Reference Table tab, you should have your master_prod_tbl table. Under the Column tab is where you map your lookup column. And if you only want the matched records, you have to Configure Error Output...

|||

Hienphilly wrote:

Maybe something like this...

Multicast Task A -> Conditional Split Task 1 -> Derived Column Task 1 -> Destination 1

Multicast Task A -> Conditional Split Task 2 -> Derived Column Task 2 -> Destination 2

Multicast Task A -> Conditional Split Task 3 -> Derived Column Task 3 -> Destination 3

One Multicast Task that split into 3 transformations.

i think you mean "transformation" instead of "task".|||

Hi Very thankful for your Help. The Problem was i am not configure the Error OutPut. Now its working fine.

I need another Help using Oracle 8i Database

i have two sql statement :

1) Source Sql Statement(select)

2) Update or Insert

How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.

Thanks & Regards,

M.Jeyakumar

|||

Jkumar wrote:

i have two sql statement :

1) Source Sql Statement(select)

2) Update or Insert

How to achive in SSIS Package .is it possible to use Sql Command in SCD Transfermation ? pls give me ur suggestion.

Sorry, I'm not really understanding. Do you want to update some data using data in the SSIS pipeline? If so, use the OLE DB Command component.

-Jamie

|||

Hi ,

Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.

Using BI How to INSERT Or UPdate Records from Source to Destination.

Condition.:

1) The source records are New Records t hen The Destination should b e INSERTED

2) Already is there then it should be UPDATED

.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.

can i use the Sql command for Insert or Update with Where Condition.

Thanks & regards,

M.Jeyakumar

|||

Jkumar wrote:

Hi ,

Previously i was tried using Sql Server DB .Now i am using Oracle 8i DB.

Using BI How to INSERT Or UPdate Records from Source to Destination.

Condition.:

1) The source records are New Records t hen The Destination should b e INSERTED

2) Already is there then it should be UPDATED

.How to Achieve this using ORAcle 8i. Whatever u already gave the sloution using SCD Transformation .its working in SQL DB.But now ia musing Oracle 8i it throwing Error.

can i use the Sql command for Insert or Update with Where Condition.

Thanks & regards,

M.Jeyakumar

I presume by "SQL Command" you mean "OLE DB Command". The answer is yes, you can use it. And it SHOULD work against Oracle.

-Jamie

How to increase SSIS performance

Hello again,

I'll just throw my question: how could I increase SSIS-performance?

I have a really heavy job with thousands of records my base selection, then I perform some lookups (I replaced most of them by sql) and derived columns (again, I replaced as much as possible by sql). Finally, after a slowly changing dimension task, I do update/insert on a given table. Is there a trick to speed up lookups and inserts (something like manipulating the buffer sizes - just asking).
Fact is that I replaced a script task by pure sql-joins and gained 6 of the 12 hours this job took.

Any ideas?

Greets,
Tom

Tom De Cort wrote:

Hello again,

I'll just throw my question: how could I increase SSIS-performance?

I have a really heavy job with thousands of records my base selection, then I perform some lookups (I replaced most of them by sql) and derived columns (again, I replaced as much as possible by sql). Finally, after a slowly changing dimension task, I do update/insert on a given table. Is there a trick to speed up lookups and inserts (something like manipulating the buffer sizes - just asking).
Fact is that I replaced a script task by pure sql-joins and gained 6 of the 12 hours this job took.

Any ideas?

Greets,
Tom

Speeding up by replacing the script task doesnt surprise me. If you have a high-performant database engine at your disposal, why not use it!

Some links for you that may help:

Whitepaper on Performance Tuning Techniques
http://blogs.conchango.com/jamiethomson/archive/2006/04/09/3594.aspx

Donald Farmer's Technet webcast (highly recommended)
http://blogs.conchango.com/jamiethomson/archive/2006/06/14/4076.aspx

-Jamie

|||

Hi Jamie,

Great, I've read the whitepaper and discovered a lot!

Greets,
-Tom

How to increase SSIS performance

Hello again,

I'll just throw my question: how could I increase SSIS-performance?

I have a really heavy job with thousands of records my base selection, then I perform some lookups (I replaced most of them by sql) and derived columns (again, I replaced as much as possible by sql). Finally, after a slowly changing dimension task, I do update/insert on a given table. Is there a trick to speed up lookups and inserts (something like manipulating the buffer sizes - just asking).
Fact is that I replaced a script task by pure sql-joins and gained 6 of the 12 hours this job took.

Any ideas?

Greets,
Tom

Tom De Cort wrote:

Hello again,

I'll just throw my question: how could I increase SSIS-performance?

I have a really heavy job with thousands of records my base selection, then I perform some lookups (I replaced most of them by sql) and derived columns (again, I replaced as much as possible by sql). Finally, after a slowly changing dimension task, I do update/insert on a given table. Is there a trick to speed up lookups and inserts (something like manipulating the buffer sizes - just asking).
Fact is that I replaced a script task by pure sql-joins and gained 6 of the 12 hours this job took.

Any ideas?

Greets,
Tom

Speeding up by replacing the script task doesnt surprise me. If you have a high-performant database engine at your disposal, why not use it!

Some links for you that may help:

Whitepaper on Performance Tuning Techniques
http://blogs.conchango.com/jamiethomson/archive/2006/04/09/3594.aspx

Donald Farmer's Technet webcast (highly recommended)
http://blogs.conchango.com/jamiethomson/archive/2006/06/14/4076.aspx

-Jamie

|||

Hi Jamie,

Great, I've read the whitepaper and discovered a lot!

Greets,
-Tom

How to include SSIS into install file?

I've already read many topics that was asked about SSIS runtime, but for my purposes I didn't find satisfied answer

Our clients don't use SQL Server for their data. But we anyway used DTS packages for transfering data between client DBes. Only thing that we needed to install was DTS dlls.

What will I need to do now to use old DTS and new SSIS packages on these clients?

Can someone to help me?|||

See my reply to your post on the SSC forum

-Jamie

|||Unlike DTS, SSIS is not redistributable. You need SQL license for every machine where SSIS runs (you may develop and debug SSIS packages using just Tools install).|||

Michael Entin SSIS wrote:

Unlike DTS, SSIS is not redistributable. You need SQL license for every machine where SSIS runs (you may develop and debug SSIS packages using just Tools install).

So, Do I have to install SSIS runtime on each 500-1000 single computers of our company's clients?

And what type of license do we need for this?

Or maybe I don't understand something how it works. What we need that every user of our clients will be able to run SSIS packages to copy/backup data from Server on his own computer or remote(Server) computer itself. It depends on where this user want to see this copied data. Maybe for this puporses there is some feather of SSIS that I still don't know. Another thing - type of DBes may be diffrent on Server and on client computers.

|||

In an ideal world you would have a single package. You would loop 500 times (or however many you need) changing the connection string each time appropriately.

If you have a different RDBMS on each source system though this will probably not be possible because the metadata will most likely change. If every one of the 500 clients has the same schema on the same RDBMS then it is theoretically possible. And certainly alot easier than installing SSIS on 500 machines (and having to do it all over again when machines are upgraded).

Might I suggest you change your architecture to a proper client/server one? Have a centralised server that contains all the data and the clients connect to get their own data.

-Jamie

|||Company clients are real estate agencies. Each agency has many agents in the field who need copied DB on their computers which most of the time disconnected from the Server. So, they need the smart client on their computers. After they update data about properties they will copy updated data from their client to the Server using DTS. Usually local computer has Access DB, and MySql or MSDE (now we want instead it SQL Express) on Server. So, how exactly do we have to change architecture? More easier I think still to work with DTS and wait for distributable SSIS runtime or something like this.|||

Instead of pushing data up to the server have you considered pulling data from the clients instead?

That way the package(s) fire centrally so you only need 1 license.

-Jamie

|||

Jamie Thomson wrote:

Instead of pushing data up to the server have you considered pulling data from the clients instead?

That way the package(s) fire centrally so you only need 1 license.

Do you meen that each client will send request to Server to start pulling or pushing data on this client computer DB using SSIS or DTS packages which are located on Server itself?

May be it will be effective solution. If we don't find easier way without changing architecture to use SSIS we'll use this solution.

Thank you.

|||

Yep. That's exactly what I mean.

Strictly speaking you can't execute a SSIS package sitting on another server. There is a way around it though. You can set up a SQL Agent job on that remote server to run teh package and then execute that job from wherever you like.

-Jamie

|||

Jamie Thomson wrote:

Yep. That's exactly what I mean.

Strictly speaking you can't execute a SSIS package sitting on another server. There is a way around it though. You can set up a SQL Agent job on that remote server to run teh package and then execute that job from wherever you like.

Yes, I have already read about it. I hope we'll find something else, not so complicated as changing architecture.

But anyway thank you for your answers.

sql

How to include SSIS into install file?

I've already read many topics that was asked about SSIS runtime, but for my purposes I didn't find satisfied answer

Our clients don't use SQL Server for their data. But we anyway used DTS packages for transfering data between client DBes. Only thing that we needed to install was DTS dlls.

What will I need to do now to use old DTS and new SSIS packages on these clients?

Can someone to help me?|||

See my reply to your post on the SSC forum

-Jamie

|||Unlike DTS, SSIS is not redistributable. You need SQL license for every machine where SSIS runs (you may develop and debug SSIS packages using just Tools install).|||

Michael Entin SSIS wrote:

Unlike DTS, SSIS is not redistributable. You need SQL license for every machine where SSIS runs (you may develop and debug SSIS packages using just Tools install).

So, Do I have to install SSIS runtime on each 500-1000 single computers of our company's clients?

And what type of license do we need for this?

Or maybe I don't understand something how it works. What we need that every user of our clients will be able to run SSIS packages to copy/backup data from Server on his own computer or remote(Server) computer itself. It depends on where this user want to see this copied data. Maybe for this puporses there is some feather of SSIS that I still don't know. Another thing - type of DBes may be diffrent on Server and on client computers.

|||

In an ideal world you would have a single package. You would loop 500 times (or however many you need) changing the connection string each time appropriately.

If you have a different RDBMS on each source system though this will probably not be possible because the metadata will most likely change. If every one of the 500 clients has the same schema on the same RDBMS then it is theoretically possible. And certainly alot easier than installing SSIS on 500 machines (and having to do it all over again when machines are upgraded).

Might I suggest you change your architecture to a proper client/server one? Have a centralised server that contains all the data and the clients connect to get their own data.

-Jamie

|||Company clients are real estate agencies. Each agency has many agents in the field who need copied DB on their computers which most of the time disconnected from the Server. So, they need the smart client on their computers. After they update data about properties they will copy updated data from their client to the Server using DTS. Usually local computer has Access DB, and MySql or MSDE (now we want instead it SQL Express) on Server. So, how exactly do we have to change architecture? More easier I think still to work with DTS and wait for distributable SSIS runtime or something like this.|||

Instead of pushing data up to the server have you considered pulling data from the clients instead?

That way the package(s) fire centrally so you only need 1 license.

-Jamie

|||

Jamie Thomson wrote:

Instead of pushing data up to the server have you considered pulling data from the clients instead?

That way the package(s) fire centrally so you only need 1 license.

Do you meen that each client will send request to Server to start pulling or pushing data on this client computer DB using SSIS or DTS packages which are located on Server itself?

May be it will be effective solution. If we don't find easier way without changing architecture to use SSIS we'll use this solution.

Thank you.

|||

Yep. That's exactly what I mean.

Strictly speaking you can't execute a SSIS package sitting on another server. There is a way around it though. You can set up a SQL Agent job on that remote server to run teh package and then execute that job from wherever you like.

-Jamie

|||

Jamie Thomson wrote:

Yep. That's exactly what I mean.

Strictly speaking you can't execute a SSIS package sitting on another server. There is a way around it though. You can set up a SQL Agent job on that remote server to run teh package and then execute that job from wherever you like.

Yes, I have already read about it. I hope we'll find something else, not so complicated as changing architecture.

But anyway thank you for your answers.

How to import in special character delimited text file by using SSIS ?

Hi,

I would like to know how to import in the custom delimited text file by using SSIS.

For example, instead by using tab or comma delimited, I use this character : '?'

The reason is the delimited format that SSIS provided is too common such as colon, semi colon, tab, comma and pipeline.

I have the data that the user also key in the pipeline there. So I am thinking to separate the field by using this special character, but cannot see if there is anyway to import in by using SSIS.

Please help to share the solution on this :

A?B?C
1?2?3

thanks

best regards,

Tanipar

You can import this as a single column and then use a script transform to cycle through your row and break it down into the appropriate columns. There are various examples of doing this relating to uneven / unbalanced / dynamic number of columns...

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/07/handling-flat-files-with-varying-numbers-of-columns.aspx

|||I am a fan of the approach above , but you can also just use a flat file connection manager. Go to the Columns page in the editor, and put the symbol into the Column Delimiter field. That works fine for me. The approach above is usually only necessary when dealing with flat files with missing columns or delimiters.|||Learn something new every day... I figured since it was a drop down that you could only use the values present, I didn't realize you could type there... Let's just say this approach is MUCH easier :-)

How to import in special character delimited text file by using SSIS ?

Hi,

I would like to know how to import in the custom delimited text file by using SSIS.

For example, instead by using tab or comma delimited, I use this character : '?'

The reason is the delimited format that SSIS provided is too common such as colon, semi colon, tab, comma and pipeline.

I have the data that the user also key in the pipeline there. So I am thinking to separate the field by using this special character, but cannot see if there is anyway to import in by using SSIS.

Please help to share the solution on this :

A?B?C
1?2?3

thanks

best regards,

Tanipar

You can import this as a single column and then use a script transform to cycle through your row and break it down into the appropriate columns. There are various examples of doing this relating to uneven / unbalanced / dynamic number of columns...

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/07/handling-flat-files-with-varying-numbers-of-columns.aspx

|||I am a fan of the approach above , but you can also just use a flat file connection manager. Go to the Columns page in the editor, and put the symbol into the Column Delimiter field. That works fine for me. The approach above is usually only necessary when dealing with flat files with missing columns or delimiters.|||Learn something new every day... I figured since it was a drop down that you could only use the values present, I didn't realize you could type there... Let's just say this approach is MUCH easier :-)

How to import files via HTTP in SSIS

Hello,

How can I import data into SQL server via http server ?

ThanksHi,
Is this any use to you: http://www.sqljunkies.com/WebLog/ashvinis/archive/2005/05/25/15653.aspx

|||Yes i saw it, but i'm novice, i don't know how tu use it ...

If someone can help me|||The code is VB.Net which can be used within a Script Task in your package.
To start, add two variables. Hope fully the names are self-explanatory-

RemoteUri
LocalFileName

Ensure they are type String, and set some suitable values. Add a Script Task, and replace Sub main with Ash's code.

|||thanks a lot, i'll try it !|||

How can i do to specifiy the path of local file with something like

PhysicalApplicationPath in a DTS script task ?

|||

The script references a variable to get the file - Dts.Variables("LocalFileName").Value

Or

Is PhysicalApplicationPath a special path that you expect to be derived? If so what exactly would you expect it to be?

|||I need to specify where my file will be downloaded. My problem is i execute this on shared sql server...

Thanks|||So use the variable and supply the folder you need to that variable. You cannot get th path of the package, as the package may not even have a path, and is being hosted at execution time, so the package path is meaningless. You seem to have started another thread so followup there.

How to impletement this query in SSIS package

Is there component in SSIS Package that I can use SQL Statement to update input dataset column based on a join query with a table?

update <input dataset> set column01 = -1
where column01 not in (select column from dbo.columnlist)
and column01 <> -1

Perhaps a combination of derived columns, conditional statements, and merge joins (perhaps).|||

Hi Phil,

if I can't not write SQL script to impletement this, I have to use condition split (for column01<>-1 ) -> Sort->Merge Join(Join dbo.columnlist table )->Condition Split (column from dbo.columnlist is null), derived column (update column01 with -1) -> Union All.

it looks so complicated.

do you know is there any component that we can write script to update the input column set? I know we can use VBScript component to do this , but I'm wondering if we can use SQL Script.

|||I think you should perform a lookup transformation against "select column from dbo.columnlist".

Using the lookup error output, you can add a derived column that sets column01 to -1. Then, use a union all to join the normal lookup output with its error output.

Don't worry about the "and column01 <> -1" statement. It is worthless.|||

how could I impletement "Not in (select column from columnlist )" in a lookup component?

a lookup component can only use equal join.

I tried with 'enable memeory restriction' and modify the SQL Statement , replacing the '=' with '<>', but it prompts error when I debug, saying 'no match rows', but actually I do have match rows.

|||

Jeff_LIU wrote:

how could I impletement "Not in (select column from columnlist )" in a lookup component?

a lookup component can only use equal join.

Right, which is why I said to use the error output. For every input row that isn't in the lookup, it'll go down the error output (the red arrow).

How to implement this in SSIS

Greetings SQL friends!

I have the following transact SQL code which I want to change to a set of SSIS components.

SELECT blah, blah

FROM PSTAGE..[stage_OFFER_PRICE_DIVIDEND] AS SOPD
LEFT OUTER JOIN PSTAGE..[stage_PRICE_GRP] AS SPG
ON SOPD.PRICE_GRP_ID = SPG.PRICE_GRP_ID
LEFT OUTER JOIN PSTAGE..[stage_type] AS TYP
ON TYP.TYPE_CD=SPG.PRICE_TYPE_TYPE4_CD
and TYP.TYPE_CL_CD = '0017'

I know I can join two data sets using a merge join (left join) but how do I combine a third merge join? Should I be doing this or should I just stick my code in a SQL Task instead?

Your help would be appreciated.

You can do this in SSIS by adding another data source for your third table and adding a second MergeJoin downstream of the first - joining the output of the first merge join to the output of the third data source.

It may be more efficient to push this on to your server using the query you give in a source component, especially as you are performing outer joins.

When doing an outer join in a client such as SSIS, you will pull all the data from the server just to throw some away as unjoined - that's somewhat inefficient use of the server-client transport. It may be more efficient to perform the join in SQL and only pull onto the client data that you will processing further.

Donald

how to implement restartability in individual mappings of SSIS packages?

restartability, is there any other tool option? like informatica

or

whether we have to implement through hand written query.

if query, then tell me how to do by taking scenarios like loading data from source sql table to target table.

suppose, in my target table, i have to load 100 records.

After loading 10 records,due to database downtime,or failure,

the loading stopped.

Either i have to truncate table or load it from 11th record.

Can u give me how to do this inside mapping

1)for table truncation

2) loading it from 11 th record.

1. Another option instead of table truncation is to use transactions if your database supports it. If something goes wrong, the transaction can be rolled back. Look in Books Online on how to set up transactions with SSIS.

2. There is currently no built-in mechanism in the data flow for restartability at a certain row, so you will need to put something together on your own. For example, have a incrementing key in the source table that you can use to track the rows inserted in the destination. Then, when something goes wrong, modify your source query to return only the rows that haven't been inserted yet.

How to implement digital signing on ssis packages?

hi everyone,

I'm just a whole newbie for that. I imagine that at first is needed a certificate commited for a company.

Any link or whatever will be very appreciated.

cheers,

The following MSDN articles details digital signing on SSIS packages.

http://msdn2.microsoft.com/en-us/library/ms141174.aspx
http://msdn2.microsoft.com/en-us/library/ms137947.aspx

Thanks,
Loonysan

|||Hi,

I've read the MSDN articles (referenced above), but still have a problem with digital signing - whenever I open the SSIS/Digital Signing menu item, and click the 'Sign...' button I'm presented with a 'Select Certificate' dialog box that has no entries in it.

How do I add a new certificate to SSIS to allow it to be used for signing packages?

Regards,

Michael

How to implement digital signing on ssis packages?

hi everyone,

I'm just a whole newbie for that. I imagine that at first is needed a certificate commited for a company.

Any link or whatever will be very appreciated.

cheers,

The following MSDN articles details digital signing on SSIS packages.

http://msdn2.microsoft.com/en-us/library/ms141174.aspx
http://msdn2.microsoft.com/en-us/library/ms137947.aspx

Thanks,
Loonysan

|||Hi,

I've read the MSDN articles (referenced above), but still have a problem with digital signing - whenever I open the SSIS/Digital Signing menu item, and click the 'Sign...' button I'm presented with a 'Select Certificate' dialog box that has no entries in it.

How do I add a new certificate to SSIS to allow it to be used for signing packages?

Regards,

Michael