none
Urgent help required

    Question

  • HI,

    I have two tables(t1, t2) of same structure in the source database.I  have used 2 OLE DB data source  and used Union all component. Now I want to give condition for id. In the T1 table(Source) id is of char(5) type. My destination table is D1 and iid is of int type. Now I want to give condition on T1.id. I have another Destination table D2.  Pls. see the below.

    SourceTable  DestinationTable     Condition
    T1.id             D1.IID                   D2.DID where Dc= T1.id

    Please help me how I do this in SSIS.

    As I am new to SSIS.

    Thanks 
    Care Career      
    Wednesday, September 23, 2009 2:59 PM

All replies

  • I think maybe you need to use a lookup component against D2 to connect Dc to T1.id, bringing back D2.DID.

    I'm not sure exactly what you are trying to do.  We'll need to see more details.

    Phil Brammer | http://www.ssistalk.com
    Wednesday, September 23, 2009 3:10 PM
  • Hi
    you mentioned that you need to do redirect some records from T1
    ....
    Now I want to give condition for id. In the T1 table(Source) id is of char(5) type
    and
    I have another Destination table D2
    .......

    Depending how complicated is the defenition of splitting the records and redirecting them
    first i would use LOOKUP (first option) and if its not giving you the results that you want use a SCRIPT COMPONENT ( this must be your last option)


    If you use Script Comp. check http://consultingblogs.emc.com/jamiethomson/archive/2005/09/05/SSIS-Nugget_3A00_-Multiple-outputs-from-a-synchronous-script-transform.aspx
    as a example



                      T1                            T2
                       |                              |
                       |                              |
     D2 <----- LOOKUP                       |
                       |                              |
                       |_____UNION ______|
                                    |
                                    |
                                    |
                                   D1
                                

                          





    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post(s)
    Wednesday, September 23, 2009 3:37 PM
  • Hi Phil,

    sorry I could not reply you. I am explanining in detail. I have two Sourcetables T1 and T2 having same structure as shown in below.ID field of both tables are char(5) type.

    SourceTable(T1)     SourceTable(T2)    
    ID                          ID
    ------------------------------------------
    A021                      ZZZZ
    B007A                    C004A
    ZZZZ                     A002A

    My Desination table name is D1 having DID(int) filed which needs mapping with ID(char) filed of T1 or T2 table.

    I have another table D2 which contains Int value of ID.
    DestinationTable(D2)

    D2ID          ID_Code
    ---------------------------
     1                ZZZZ
     2                A002A
     3                B007A                   


    Now my condition is :select D2.D2ID from D2 WHERE ID_Code in('ZZZZ','A002A' ,'B007A')  

    What i m doing

                      T1                            T2
                       |                              |
                       |                              |
                       |                              |
                       |                              |
                       |_____UNION ______|
                                    |
                                    |
                                    |

      After this I am bit confused where I give this above condition to get the ID integer value in the destination table(D1) .


    Please help asap.

    Thanks
    Care Career
    Friday, September 25, 2009 7:34 AM
  • Hi,

     I have two Sourcetables T1 and T2 having same structure as shown in below.ID field of both tables are char(5) type.

    SourceTable(T1)     SourceTable(T2)    
    ID                          ID
    ------------------------------------------
    A021                      ZZZZ
    B007A                    C004A
    ZZZZ                     A002A

    My Desination table name is D1 having DID(int) filed which needs mapping with ID(char) filed of T1 or T2 table.

    I have another table D2 which contains Int value of ID.
    DestinationTable(D2)

    D2ID          ID_Code
    ---------------------------
     1                ZZZZ
     2                A002A
     3                B007A                   


    Now my condition is :select D2.D2ID from D2 WHERE ID_Code in('ZZZZ','A002A' ,'B007A')  

    What i m doing

                      T1                            T2
                       |                              |
                       |                              |
                       |                              |
                       |                              |
                       |_____UNION ______|
                                    |
                                    |
                                    |
    After this I am bit confused where I give this above condition to get the ID integer value in the destination table(D1) .


    Please help asap.

    Thanks
    Care Career
    Friday, September 25, 2009 7:49 AM
  • Hi
    could you provide a sample output that you want for a given input.

    Hope this helps !!
    Please close the thread once answered

    Sudeep      My Blog
    Friday, September 25, 2009 8:41 AM
  • After Union you will get ID.
    Then take a lookup component with lookup query as:
    select D2ID,ID_Code from D2
    Do equi join based on ID (from Union Component) and ID_Code (from D2 table).
    Select D2ID as lookup column.
    Now you will have D2ID from D2 table where ID_Code in (ID from T1 and T2)
    Nitesh Rai- Please mark the post as answered if it answers your question
    Friday, September 25, 2009 8:44 AM
  • HI Nitesh,


    Thank you so much for your help,I am still getting error in look up component.

    Errors :
    [Lookup [1516]] Error: Row yielded no match during lookup.
    [Lookup [1516]] Error: The "component "Lookup" (1516)" failed because error code 0xC020901E occurred, and the error row disposition on "output "Lookup Output" (1518)" specifies failure on error. An error occurred on the specified object of the specified component.
    [DTS.Pipeline] Error: The ProcessInput method on component "Lookup" (1516) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
    [DTS.Pipeline] Error: Thread "WorkThread1" has exited with error code 0xC0209029.

    Also, I am getting warning as
    [Lookup [1516]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.

    Please help


    Thanks
    Care Career



    Friday, September 25, 2009 9:31 AM
  • Also, I am getting warning as
    [Lookup [1516]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.

    As far as I know, this means that for one ID (comming from UNION component), there are more than one ID_Code in D2 table.
    Is that the case? Could you please check that?
    Have you tried changing the Cache mode?
    Nitesh Rai- Please mark the post as answered if it answers your question
    Friday, September 25, 2009 10:22 AM
  • Hi,

    I have checked and  found that for one id( (comming from UNION component), only one Id_Code exists in D2 table.

    As I am new to SSIS, I do not know from where we can change cache mode?

    Also, in lookup component In refrenece tab I have written query as  "select D2ID,ID_Code from D2 " and in coulmns tab I have mapped ID with ID_Code. and  in Advance Tab

    query is :select * from
            (select D2.D2ID,D2.ID_Code from D2)as refTable
    where [refTable].[ID_Code] = ?

    Please correct me if i m doing wrong anything.

    Thanks
    Care Career


    Friday, September 25, 2009 10:55 AM
  • If there is only one ID_Code for a Id then i think you dont need to change the cahe mode.
    Your lookup query is fine.
    try to use Ltrim(Rtrim(columnname)) inside the source query and lookup query.
    Also, you need to know that lookup is case sensitive.
    So if ID is ZZZ and Id_Code is zzZ or ZZz or... then lookup will fail.
    So you need to use Lower or Upper in both the source query and lookup query.
    Like:
    Source T1: Select Lower(ID) from T1
    Source T2: Select Lower(ID) from T2

    Ssame way you have to use lower function in the lookup query
    Nitesh Rai- Please mark the post as answered if it answers your question
    Friday, September 25, 2009 11:45 AM
  • HI,

    My aplogoies, I found that after union T1 and T2 tables my output result is having duplicate values like.

    SELECT * FROM T1
    UNION ALL
    SELECT * FROM T2


    ID(char)
    ----
    ZZZZZ
    ZZZZZ
    ZZZZZ
    C001A
    C001A

    Now my first step is to fetch the distinct id then based on this distinct id need get ID int value from D2 table.

    Pls. let me know how can I do this?

    Thanks
    Care Career



    Friday, September 25, 2009 12:17 PM
  • You can have a staging table / global temp table and consume the output of union all into the table.
    Then have a different Data Flow task and use this table as source (Select Distinct ID) and continue with the flow.
    Nitesh Rai- Please mark the post as answered if it answers your question
    Friday, September 25, 2009 12:25 PM
  • please tell me in detail , How I create global temp table in SSIS .which component will be used for this?

    Thanks
    Care Career
    Friday, September 25, 2009 12:40 PM
  • I would suggest to create a staging table (physical table) instead of temp table.
    1.Create a staging table.
    2.After Union All component, take an OLEDB destination and select the staging table you have created in step 1
    3.Then get back to control flow and take one more Data Flow Task(DFT).
    4.Inside DFT, take an OLEDB Source and use the staging table as source  table.
    5.Use the lookup component the way you were using earlier
    6.Take the OLEDB Destination and consume the output of lookup

    Create satging table or Temp table using management studio
    Nitesh Rai- Please mark the post as answered if it answers your question
    Friday, September 25, 2009 12:46 PM
  • Hi ,

     According to client requirement I can not create any physical table in staging databse.I would request you to please tell me alternate way.
    I really appreciate your help.


    Thanks
    Care Career
    Friday, September 25, 2009 12:50 PM
  • Care Career,

    A few things - we are all volunteers here - so helping ASAP is not something we do, nor can we provide.  Also, please try to post a descriptive subject when you post a message, rather than "Urgent please help."  These things will help us provide a better service to you.

    With that said, can't you just do all of this in a source query:

    SELECT Source.ID,
           D2.ID_Code
       
      FROM (SELECT ID
              FROM T1
     
             UNION
    
            SELECT ID
              FROM T2) Source
              
      JOIN D2
        ON Source.ID = D2.ID
    I don't understand what table D1 has to do with anything, as you've not provided any details about that table.  Please provide more information.
    Phil Brammer | http://www.ssistalk.com
    Friday, September 25, 2009 1:02 PM
  • So you can craete a global temp table instead of staging table.

    IF

     

    OBJECT_ID('##TempTable', 'U') IS NOT NULL
    DROP TABLE ##TempTable
    CREATE TABLE ##TempTable
    ( ID Varchar(10))
    Open the management studio and create the temp table. (dont close the session).
    Now In package, Take a execute SQL task (it will be first task inside the package ).
    In execute SQL task, select the connection manager for tempdb and write the create temp table script (as above) inside the SQL Statement option.
    Now you can use this temp table in place of staging table and proceed according to my previous post.
    Also, make the Retain Same Connection property of the tempdb connection manager as TRUE


    Nitesh Rai- Please mark the post as answered if it answers your question
    Friday, September 25, 2009 1:02 PM
  • Hi Nitesh,

    I am trying this above query but I am unable to find Ole Db Datasource control, Strnage....


    Friday, September 25, 2009 1:37 PM
  • Hi Phill,

    T1 & T2 tables are in staging database and  D1, D2 tables is mart database.

    D1 is the final table where I want to put data from  UNIONing both tables(T1 and T2).


    Please refer  this again

    I have two Sourcetables T1 and T2 having same structure as shown in below.ID field of both tables are char(5) type.

    SourceTable(T1)     SourceTable(T2)    
    ID                          ID
    ------------------------------------------
    A021                      ZZZZ
    B007A                    C004A
    ZZZZ                     A002A

    My Desination table name is D1 having DID(int) filed which needs mapping with ID(char) filed of T1 or T2 table.

    I have another table D2 which contains Int value of ID.
    DestinationTable(D2)

    D2ID          ID_Code
    ---------------------------
     1                ZZZZ
     2                A002A
     3                B007A                   


    Now my condition is :select D2.D2ID from D2 WHERE ID_Code in('ZZZZ','A002A' ,'B007A')  

    What i m doing

                      T1                            T2
                       |                              |
                       |                              |
                       |                              |
                       |                              |
                       |_____UNION ______|
                                    |
                                    |
                                    |

      After this I am bit confused where I give this above condition to get the ID integer value in the destination table(D1) .

    Regards
    Care Career

    Friday, September 25, 2009 1:46 PM
  • If T1 and T2 are on the same server, write this query in your OLE DB Source:

    SELECT ID
      FROM T1

     UNION

    SELECT ID
      FROM T2

    Then, in your data flow, hook the OLE DB Source component up to a Lookup Component.  In the Lookup Component, use the following SQL: 

    SELECT DISTINCT ID, ID_Code
      FROM D2

    Then the Lookup Component "mappings" tab, hook the input column, ID, up to ID_Code, and click the check box next to the Lookup Column ID.

    Then, hook up your Lookup Component to an OLE DB Destination.  There, select the D1 table, and in the mappings, hook up the Lookup.ID column to D1.ID.

    OLE DB SOURCE
         | 
         |
    Lookup Component
         |
         |
    OLE DB Destination

    If you don't want the package to error out when an incoming row doesn't match a lookup row, set the Lookup Component to ignore errors, or redirect errors to another table for inspection.


    Phil Brammer | http://www.ssistalk.com
    Friday, September 25, 2009 2:13 PM
  • Hi,

    Problem 1

    StagingDatabase(Tables T1, T2)

     
                      T1                            T2
                       |                              |
                       |                              |
                       |                              |
                       |                              |
                       |_____UNION ______|
                                     |
                                     |
    After union result  :        

    LIID               ID
    ---------------------------
     1                ZZZZ
     2                A002A
     3                B007A 
     4                ZZZZ
     5                B007A 
        

    e.g ID(Char(5)) is a field .I want distinct  value of ID.



    Mart Database (Table T3)

    TID               ID
    ---------------------------
     1                ZZZZ
     2                A002A
     3                B007A 

    Compare ID field of union with ID field of Table T3 and select TID of Table T3.





    Problem 2

    Now we have TID integer value From Table T3.

    Now I want  to replace ID  of Union( T1 and T2) with TID in final output.

    LIID               ID
    ---------------------------
     1                1
     2                2
     3                3
     4                1
     5                3


    wnt to implement in SSIS.

    thnx in advance.

    Regrads
    Care Career








    Friday, September 25, 2009 5:49 PM
  • T1    T2
     |      | 
     -------
         |
      Union All
        |
     Lookup
        | 
     Destination

    In the lookup, as I've said earlier, hook up ID to ID, and check the box for TID.

    Then, in your destination (or the next component after the lookup) don't select ID from the Union Component, instead select the ID from the Lookup component.  (It will say something like [Union All].ID and [Lookup].ID.


    Phil Brammer | http://www.ssistalk.com
    Friday, September 25, 2009 6:18 PM
  • The lookup SQL should be:

    SELECT TID, ID
      FROM T3 -- your lookup table

    Then in the mappings tab, hook up ID to ID and check the box next to TID.
    Phil Brammer | http://www.ssistalk.com
    Friday, September 25, 2009 6:18 PM
  • Also, if you need to shrink your T1/T2 union to just the distinct values of ID, and you can't union the two tables together in an OLE DB Source (as I've previously mentioned - perhaps because these are file sources), then you can use a Sort component after the Union All component to sort the data and remove duplicates.


    Phil Brammer | http://www.ssistalk.com
    Friday, September 25, 2009 6:29 PM
  • Hi,

    I am getting error in look up :

    [Lookup [570]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.

    [DTS.Pipeline] Error: The ProcessInput method on component "Lookup" (570) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

    Thnx
    Care Career
    Friday, September 25, 2009 6:35 PM
  • This is because your lookup table does not have distinct values for TID,ID in T3.

    Use this:

    SELECT DISTINCT TID, ID
       FROM T3 -- your lookup table
    Phil Brammer | http://www.ssistalk.com
    Friday, September 25, 2009 6:47 PM
  • There are distinct values in T3 table but when I union T1 and T2  tables then only ID value is duplicate.That's why I want to fetch distinct Id after union T1 and T2.


    Regards
    Care Career
    Friday, September 25, 2009 6:54 PM
  • There are distinct values in T3 table but when I union T1 and T2  tables then only ID value is duplicate.That's why I want to fetch distinct Id after union T1 and T2.


    Regards
    Care Career

    INCORRECT.  Again, the warning (it isn't an error) message you posted, shows that there are duplicate values in your lookup table, not the incoming data.  The incoming data can have duplicates, it's the lookup table that has duplicates.  You need to look again.

    "The Lookup transformation encountered duplicate reference key values when caching reference data."  - "reference key values" means the data in your lookup table.
    Phil Brammer | http://www.ssistalk.com
    Friday, September 25, 2009 6:59 PM
  • correct me if i am wrong you did mention that both tables are in the same source (SQL, ....????) so make a select statment on the first OLE source and recieve the data in SSIS unioned

    SELECT DISTINCT X.*  FROM
    (
          SELECT DISTINCT ........ FROM TBL 1
          UNION
          SELECT DISTINCT ........ FROM TBL 2
    ) AS X


    YES i know that you can write a better query

    my point is use this in the SOURCE OLE then you would only have

    OLE SOURCE ( query in it) --------> Dlookup --------->  DestinationT1

                                                            |
                                                            |---------  > Destination T2

     

     


    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post(s)
    Friday, September 25, 2009 7:05 PM
  • pls. see my Look up table output.
     TID   ID
     -1    _NF
    1    A001
    2    A002
    3    A002A
    4    A003
    5    A004
    6    A005
    7    A006
    8    A006A
    9    A007
    10    A008
    11    A009
    12    A010
    13    A011
    14    A012
    15    A013
    16    A013A
    17    A013B
    18    A013C
    19    A014
    20    A016
    21    A017
    22    A020
    23    A021
    24    z
    25    A023
    26    A024
    27    A024A
    28    A025
    29    A026
    30    A027
    31    A028
    32    B001
    33    B001A
    34    B002
    35    B003
    36    B004
    37    B005
    38    B006
    39    B006A
    40    B007
    41    B007A
    42    B007B
    43    B008
    44    B008A
    45    B009
    46    B010
    47    B011
    48    B011F
    49    C001
    50    C001A
    51    C002
    52    C003
    53    C004A
    54    C007
    55    C008
    56    C009
    57    C010
    58    C013
    59    D001
    60    D002A
    61    D003
    62    D005
    63    D005A
    64    D006
    65    D007
    66    D008
    67    D009
    68    D009B
    69    D010
    70    D011
    71    E001
    72    E002
    73    F001
    74    F002
    75    F003
    76    F004
    77    F005
    78    F006
    79    F007
    80    P002
    81    P004
    82    P008
    83    P013
    84    P014
    85    P020
    86    P024
    87    P025
    88    P026
    89    P028
    90    P032
    91    P041
    92    P043
    93    P044
    94    P048
    95    P049
    96    P051
    97    P053
    98    P054
    99    P056
    100    P069
    101    P131
    102    P073
    103    P076
    104    P079
    105    P082
    106    P087
    107    P092
    108    P071
    109    P132
    110    P150
    111    P203
    112    P221
    113    P224
    114    P225
    115    P018
    116    NULL
    117    NULL
    118    NULL
    119    NULL
    120    NULL
    121    NULL
    122    NULL
    123    NULL
    124    NULL

    Regards
    Care Career
    Friday, September 25, 2009 7:07 PM
  • I've already suggested that, Nik3.  Also, you wouldn't need the outer SELECT DISTINCT X.* statement since you are using a UNION instead of a UNION ALL.
    Phil Brammer | http://www.ssistalk.com
    Friday, September 25, 2009 7:08 PM
  • pls. see my Look up table output.
     TID   ID
     -1    _NF
    1    A001
    2    A002
    3    A002A
    4    A003
    5    A004
    6    A005
    ....
    110    P150
    111    P203
    112    P221
    113    P224
    114    P225
    115    P018
    116    NULL
    117    NULL
    118    NULL
    119    NULL
    120    NULL
    121    NULL
    122    NULL
    123    NULL
    124    NULL

    Regards
    Care Career

    You have to get rid of the NULLs, that's the issue.  If you have an incoming NULL coming in, which value should the lookup component choose?

    SELECT DISTINCT TID, ID
      FROM T3 -- Your lookup table
     WHERE ID IS NOT NULL
    Phil Brammer | http://www.ssistalk.com
    Friday, September 25, 2009 7:11 PM
  • Ok, I have modified the query still some errors exist in look up.



    [Lookup [771]] Error: The "component "Lookup" (771)" failed because error code 0xC020901E occurred, and the error row disposition on "output "Lookup Output" (773)" specifies failure on error. An error occurred on the specified object of the specified component.

    Regards
    Care Career
    Friday, September 25, 2009 7:17 PM
  • Ok, I have modified the query still some errors exist in look up.



    [Lookup [771]] Error: The "component "Lookup" (771)" failed because error code 0xC020901E occurred, and the error row disposition on "output "Lookup Output" (773)" specifies failure on error. An error occurred on the specified object of the specified component.

    Regards
    Care Career

    Always please include all of the error messages when you post them here.  0xC020901E is a result of an incoming row having no match in the lookup table.  To prevent this error, set the error output of the lookup component to either ignore errors or redirect them to another destination.

    At the bottom of the "Columns" tab, there is a button to "Configure Error Output...".  Select that, and then on the row that says "Lookup Output" change the value in the column "Error" to "Ignore Failure" or "Redirect Rows".  Start with ignoring the failure.
    Phil Brammer | http://www.ssistalk.com
    Friday, September 25, 2009 7:23 PM
  • Thank u so much for this.

    My Union result having same Id's which is in T3 table. My point is why its not matching..

    Also still getting below errrors.


    [OLE DB Destination [1187]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Unspecified error".

    [OLE DB Destination [1187]] Error: There was an error with input column "TID" (1236) on input "OLE DB Destination Input" (1200). The column status returned was: "The value violated the integrity constraints for the column.".

    [OLE DB Destination [1187]] Error: The "input "OLE DB Destination Input" (1200)" failed because error code 0xC020907D occurred, and the error row disposition on "input "OLE DB Destination Input" (1200)" specifies failure on error. An error occurred on the specified object of the specified component.

    Regards
    Care Career
    Friday, September 25, 2009 7:44 PM
  • As I wrote earlier, add a sort component between the union all and the lookup components.  DO NOT select the LIID from the Union All component in the sort, and check the box to remove duplicates.
    Phil Brammer | http://www.ssistalk.com
    Friday, September 25, 2009 8:07 PM
  • HI PHill,

    I have already added Sort component between the union all and the lookup components still same error exists.

    [OLE DB Destination [1187]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Unspecified error".

    [OLE DB Destination [1187]] Error: There was an error with input column "TID" (1236) on input "OLE DB Destination Input" (1200). The column status returned was: "The value violated the integrity constraints for the column.".

    Thnx
    Care Career
    Friday, September 25, 2009 8:19 PM
  • HI PHill,

    I have already added Sort component between the union all and the lookup components still same error exists.

    [OLE DB Destination [1187]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Unspecified error".

    [OLE DB Destination [1187]] Error: There was an error with input column "TID" (1236) on input "OLE DB Destination Input" (1200). The column status returned was: "The value violated the integrity constraints for the column.".

    Thnx
    Care Career

    Sounds like you may be trying to insert duplicate records into the TID column in the destination table, to me.   Do you need to update the destination record if the incoming TID already exists there?
    Phil Brammer | http://www.ssistalk.com
    Friday, September 25, 2009 10:15 PM
  • Hi Phil,

    My destination table is empty..

    [OLE DB Destination [1187]] Error: There was an error with input column "TID" (1236) on input "OLE DB Destination Input" (1200). The column status returned was: "The value violated the integrity constraints for the column.

    Regards
    Care Career
    Saturday, September 26, 2009 4:50 AM
  • There was an error with input column "TID" (1236) on input "OLE DB Destination Input" (1200). The column status returned was: "The value violated the integrity constraints for the column"

    Correct me if I am wrong, in T3  table(mart Database) TID is primary key and in destination table I am trying to insert more than one same  TID . may be this is the reason "The value violated the integrity constraints for the column". If it is then pls. let me know how to remove integrityc onstraints in SSIS?


    Thnx
    Care Career


    Saturday, September 26, 2009 5:27 AM
  • There was an error with input column "TID" (1236) on input "OLE DB Destination Input" (1200). The column status returned was: "The value violated the integrity constraints for the column"

    Correct me if I am wrong, in T3  table(mart Database) TID is primary key and in destination table I am trying to insert more than one same  TID . may be this is the reason "The value violated the integrity constraints for the column". If it is then pls. let me know how to remove integrityc onstraints in SSIS?

    No, you can insert more than one TID in destination table. Thats not a problem.
    The problem is that the value you are inserting in the destination table is not present in T3 table.
    Check following link for removing fk constraint
    http://msdn.microsoft.com/en-us/library/ms189579(SQL.90).aspx
    Nitesh Rai- Please mark the post as answered if it answers your question
    Saturday, September 26, 2009 5:45 AM
  • Hi Nitesh,

    In T3 table TID  integer values exists which I have calculated in lookup component based on ID(char)=ID(char).


    Regards
    Care Career
    Saturday, September 26, 2009 5:59 AM
  • You mean by using lookup component all the rows qualifies as matching in the source and T3 table??

    Nitesh Rai- Please mark the post as answered if it answers your question
    Saturday, September 26, 2009 6:06 AM
  • yes.

    Regards
    Care Career
    Saturday, September 26, 2009 6:11 AM
  • Do a quick check:
    Just before destination component add the lookup (lookup table as T3) and do the join based on ID column.
    Take green op to the destination and red to some row count transform.
    Check if it is still failing. How many records are going to row count?

    Nitesh Rai- Please mark the post as answered if it answers your question
    Saturday, September 26, 2009 6:27 AM
  • HI,

    I have checked 863 rows goes to Lookup 1.
    No record is going to row count.


    Thanks
    Care Career


    Saturday, September 26, 2009 7:01 AM
  • So you mean it has agin failed on OLEDB Destination.
    Is there any more foreign key in the destinatin table that is refering to some other table?


    Nitesh Rai- Please mark the post as answered if it answers your question
    Saturday, September 26, 2009 7:06 AM
  • But still getting same issue. its not saving in the destination table.Error is same:

    There was an error with input column "TID" (1236) on input "OLE DB Destination Input" (1200). The column status returned was: "The value violated the integrity constraints for the column"



    Saturday, September 26, 2009 7:07 AM
  • Only One primary key (SID)exists in the Destination table. Input coulmn is ignored corresponding to this colulmn (SID).


    Regards
    Care Career
    Saturday, September 26, 2009 7:10 AM
  • Inpt column TID for OLEDB Destination is int...rt?
    Nitesh Rai- Please mark the post as answered if it answers your question
    Saturday, September 26, 2009 7:11 AM

  • Error is:[OLE DB Destination [1773]] Error: There was an error with input column "TID" (1839) on input "OLE DB Destination Input" (1786). The column status returned was: "The value violated the integrity constraints for the column.".

    Regards
    Care Career
    Saturday, September 26, 2009 7:12 AM
  • yes.

    TID is integer. To fetch this integer value I m matching ID(char)=ID(char).

    Regards
    Care Career
    Saturday, September 26, 2009 7:14 AM
  • Only One primary key (SID)exists in the Destination table. Input coulmn is ignored corresponding to this colulmn (SID).

    I am asking is there any more foreign key apart from ID inside the destination table ?
    Nitesh Rai- Please mark the post as answered if it answers your question
    Saturday, September 26, 2009 7:15 AM
  • Yes, three foreign keys exists in Destination table.

    Regards
    Care Career
    Saturday, September 26, 2009 7:22 AM
  • Is the integrity constraint voilatin only due to the ID column or it is due to some other column?
    Please confirm ..


    Nitesh Rai- Please mark the post as answered if it answers your question
    Saturday, September 26, 2009 7:26 AM
  • Error is:[OLE DB Destination [1773]] Error: There was an error with input column "TID" (1839) on input "OLE DB Destination Input" (1786). The column status returned was: "The value violated the integrity constraints for the column.".


    TID is forign key in destination table  and as per above error it seems that integrity constraint voilatin occuring due to this TID.


    Regards
    Care Career
    Saturday, September 26, 2009 7:29 AM
  • Any luck?

    Regards
    Care Career
    Saturday, September 26, 2009 9:11 AM
  • Error is:[OLE DB Destination [1773]] Error: There was an error with input column "TID" (1839) on input "OLE DB Destination Input" (1786). The column status returned was: "The value violated the integrity constraints for the column.".


    TID is forign key in destination table  and as per above error it seems that integrity constraint voilatin occuring due to this TID.


    Regards
    Care Career

    Care Career,
    You need to do some research and understand what constraints you have on your destination table, and why they are failing.  Are you trying to insert NULLs into TID, where the TID column is defined as NOT NULL?  Or, are you trying to push more than one TID to the destination table, when the TID column is defined as unique/primary key?  These are things you need to investigate on your own, in order to better understand how the whole process works - we can't build your data flows for you.


    Phil Brammer | http://www.ssistalk.com
    Saturday, September 26, 2009 3:19 PM
  • Hi,

    My union result is :

    DSR  ID(char(5)) DName
    1       ZZZZ         LTD
    2       AAAA         PFD
    3       BBBBB       KJI
    4       BBBB         RTV
    5       ZZZZ         HGG
    6       AAAA         MNH

    Now My requirement is to put all above values in destination table.


    My destination output should be:

    ID    IID     INAME
    1        1        LTD
    2        2        PFD
    3        3        KJI
    4        4        RTV
    5        1        HGG
    6        2        MNH

    To get above IID value I am matching ID CHAR(5)) value with ID(CHAR(5)) field of another table using look up component.

    Pls.tell me is look up compnent should be used or any another component.

    Thanks & Regards
    Care Career




    Monday, September 28, 2009 9:37 AM
  • If I am not wrong, you want IID as 3 and 4 for ID(char-5) BBBB as shown in your destination output
    I think Lookup component based on ID (char-5) will not give appropriate result.
    You need a unique value for a match in the lookup.

    Nitesh Rai- Please mark the post as answered if it answers your question
    Monday, September 28, 2009 9:46 AM
  • Hi Nitesh,

    My union result is :

    DSR  ID(char(5)) DName
    1       ZZZZ         LTD
    2       AAAA         PFD
    3       BBBBB       KJI
    4       BBBB         RTV
    5       ZZZZ         HGG
    6       AAAA         MNH

    Now My requirement is to put all above values in destination table.

    My another table structure is:

    DIID    ID(char(5))       
    1         ZZZZ   
    2         AAAA        
    3         BBBBB      
    4         BBBB     

    To get above IID value I am matching ID CHAR(5)) value with ID(CHAR(5)) field of another table using look up component.

    My destination output should be:

    ID    IID     INAME
    1        1        LTD
    2        2        PFD
    3        3        KJI
    4        4        RTV
    5        1        HGG
    6        2        MNH

    Thank you so much.

    Regards

    Monday, September 28, 2009 11:36 AM
  • As far as there is a unique match in the lookup reference set for a specific ID (from your union all), a lookup component can be used to achieve the destination output.
    Yes, a Lookup can be used

    Nitesh Rai- Please mark the post as answered if it answers your question
    Monday, September 28, 2009 11:56 AM
  • Ok, I have used look up compnent. but In my destination table there are 3 forign key constraints exists, and I am getting error as:
    Error is:[OLE DB Destination [1773]] Error: There was an error with input column "IID" (1839) on input "OLE DB Destination Input" (1786). The column status returned was: "The value violated the integrity constraints for the column.".

    Please let me know ho to remove this error?

    Thanks
    Monday, September 28, 2009 1:13 PM
  • Ok, I have used look up compnent. but In my destination table there are 3 forign key constraints exists, and I am getting error as:
    Error is:[OLE DB Destination [1773]] Error: There was an error with input column "IID" (1839) on input "OLE DB Destination Input" (1786). The column status returned was: "The value violated the integrity constraints for the column.".

    Please let me know ho to remove this error?

    Thanks
    Monday, September 28, 2009 1:16 PM
  • Ok, I have used look up compnent. but In my destination table there are 3 forign key constraints exists, and I am getting error as:
    Error is:[OLE DB Destination [1773]] Error: There was an error with input column "IID" (1839) on input "OLE DB Destination Input" (1786). The column status returned was: "The value violated the integrity constraints for the column.".

    Please let me know ho to remove this error?

    Thanks
    Well, you have to be sure that the values you are trying to insert exist in the tables that are being referenced by the foreign key constraints.  This may require more data flows before the one you are working on here, to get the data inserted first in those foreign tables - or drop the foreign key constraints, but they are likely there for a reason so that is not something I recommend.
    Phil Brammer | http://www.ssistalk.com
    Monday, September 28, 2009 1:50 PM
  • Hi,

    I have removed all the constraints on the destination table .But Still Same issue exists. Please help me.


    Regards
    Monday, September 28, 2009 3:21 PM
  • Hi,

    I have removed all the constraints on the destination table .But Still Same issue exists. Please help me.


    Regards

    You mean you have removed all the foreign key constraints and still you are getting "Integrity Constraint Violation"!!
    In the OLEDB Destination uncheck the constrants check box and see if still there is same\some error
    Nitesh Rai- Please mark the post as answered if it answers your question
    Monday, September 28, 2009 3:26 PM
  • Hi Nitesh,

    I have unchecked  the constraints  check box in OLEDB Destination  component still same issue exists.

    Regards
    Care Career

    Tuesday, September 29, 2009 7:33 AM
  • Hi Nitesh,

    I have unchecked  the constraints  check box in OLEDB Destination  component still same issue exists.

    Regards
    Care Career


    Is there any trigger being implemented for "Insert in the destination table" ?
    I cant understand why you are getting integrity constraint violation when all the foreign keys are removed/disabled.

    Nitesh Rai- Please mark the post as answered if it answers your question
    Tuesday, September 29, 2009 8:20 AM
  • Hi Nitesh,

    No trigger is used.

    I am also surprised why its heppening. 

     
    Tuesday, September 29, 2009 8:56 AM
  • Hi,

    Can you script the table from management studio and paste it here, it really helps in understanding better.

    Regards,
    Raju

    Tuesday, September 29, 2009 9:05 AM
  • Hi Care,
    There are almost 70 replies on this post. It will be tough for someone to follow the sequence of replies and help you.
    You can close this thread and open a new one with a nice subject line (Integrity Constraint Violation or something else) to attract more people.


    Nitesh Rai- Please mark the post as answered if it answers your question
    Tuesday, September 29, 2009 9:06 AM