none
Delay Validation property

    Question

  • I am trying to create a SSIS package to move data from a source table to a destination table. So My package contains 2 Execute SQL Tasks. The first one finds the source table max date, the next Execute SQL Task creates the destination table, if it does not exist and then finds the destination max date.

    Then I have a Data Flow Task and the Delay Validation property on it is set to true. In the DFT I have an OLEDB source that copies the data from the source to the destination using a SQL statement based on the difference between the source and the destination max date. Then I have an OLEDB destination, the connection string for which I set by going to the Show Advanced Editor. I cannot select the table since the table does not exist in the destination database and I want it to be created during the first time when the package is executed and also want the data to be transferred.

    When the package is executed it creates the table in the destination DB but it fails to transfer the data giving me the following error

    [OLE DB Destination [1529]] Error: A destination table name has not been provided.

    [SSIS.Pipeline] Error: "component "OLE DB Destination" (1529)" failed validation and returned validation status "VS_ISBROKEN".

    So is there a way to create the table in the destination and transfer data to the destination table at the same time? I thought that was the point of setting the delay validation property to true?

    Any idea why I am getting the above error?

    I really appreciate any help on this.


    • Edited by Diggys Thursday, February 14, 2013 3:33 AM
    Thursday, February 14, 2013 3:31 AM

Answers

  • No what i meant is the table is required in the development stages once you have validated and mapped the source target then you drop.

    Regards,Eshwar.


    Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.

    • Marked as answer by Diggys Tuesday, February 19, 2013 2:39 AM
    Thursday, February 14, 2013 3:57 AM

All replies

  • Set the delay validation to true for the dataflow as well Execute SQL tasks.

    Also create the table initially because you have to do mapping after the development drop the table if required.

    Regards,Eshwar.


    Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.

    Thursday, February 14, 2013 3:47 AM
  • So what you are telling me is there is no way to create the table and transfer the data at the same time?

    Then what is the purpose of the delay validation property? Just wondering.

    Thursday, February 14, 2013 3:50 AM
  • SSIS is based on static metadata, you can fool it by disabling the validation but it may fail at run time still.

    I do not understand what data hits the target and why it may not exist. At the moment the design appears flawed to me, sorry to tell you.

    If you try to sync tables, you can 1st check if the table exists and then using a Precedence Constraint direct the flow to 1st creating the table if it does not exist (and you then copy all the data) which is easily can be done using the Transfer Database Object Task set to copy the object with its data, or you branch off to simply grabbing the delta that can be done very easily with the T-SQL Merge (if MERGE is not available to you use http://www.sqlis.com/sqlis/post/Get-all-from-Table-A-that-isnt-in-Table-B.aspx approach).

    Arthur My Blog

    Thursday, February 14, 2013 3:52 AM
  • It might be the data type issue as well, check the datatypes of the columns which you have setted in the DFT and check which the datatypes of the columns of the table which you are creating in SQL Task, it might differ..

    Try to run DFT in BIDS by creating the table manually, see if it is able to transfer the data.

    Please mark it as useful if it helps.

    Thanks

    Sumit


    Thursday, February 14, 2013 3:52 AM
  • No what i meant is the table is required in the development stages once you have validated and mapped the source target then you drop.

    Regards,Eshwar.


    Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.

    • Marked as answer by Diggys Tuesday, February 19, 2013 2:39 AM
    Thursday, February 14, 2013 3:57 AM
  • Sumit

    I have already tried this and it works fine in BIDS without any issues.
    Thanks for your suggestion.

    • Edited by Diggys Thursday, February 14, 2013 3:59 AM
    Thursday, February 14, 2013 3:57 AM
  • Arthur,

    Actually I do the checking in one of the Execute SQL tasks. I first check to see if the table exists and if it does not I create the table. Then I try to find the delta in the date so that I can copy the data based on the date delta. I have to sync the delta everyday and the first time I run the package the tables are not in the destination DB. So I was under the impression that setting the delay Validation to true will let the Execute SQL task to create the table first and when it is time for the OLEDB Destination to run, it will copy the table to the just created tables.

    Thursday, February 14, 2013 4:04 AM