Post reply

SSIS does not recognize created table from the task before (OLE DB destination specified in variable)?

  • November 4, 2011 at 11:39 am

    #390770

    Hi, everybody!

    I have a following problem:

    In my package, in the first SQL task I select max (ColumnA) from the existing Table1, and keep it as single row Result in the variable1 (which is actually date stored as a string).

    Second SQL task also passes the string to Single Row Result package variable2 - it contains the value, for example:

    'dbo.NewTableName_' + ? (from the task before - parameter mapping), and gives something like

    'dbo.NewTableName_20110930, which is stored in the variable2, in the end of this step.

    Third SQL task creates this new table in the database - I used execute sql task again, in which I mapped parameter variable2. (Create table ?, and so on ...)

    So, after these three steps I have new table in the database which I want to fill with data.

    In the next, fourth step I created Data Flow Task, chose OLE DB Source (existing) table, and OLE DB Destination with the option (fast load from Table or View name variable) - I want here to choose the dbo.NewTableName_20110930 as the OLE DB destination, the table which I created in the step three, to be filled with data.

    So, I chose the the variable2 that contains the name of the NewTable.

    But, at this step, SSIS fails, and the message is that object "dbo.NewTableName_20110930" does not exist. When I checked in the database, the table was there.

    Is there a way to solve this problem?

    (I set the validate external metadata to false).

    The purpose of this package, in general, is to archive data into archive table which is created every time when SSIS is started, every time with a different name (runs once a month for last month data).

    Thank you all in advance!

    Best,

  • November 4, 2011 at 11:52 am

    #1403428

    What version and edition of SQLS are you running?

    Also, ity would help if you could attach the package.

  • November 4, 2011 at 12:15 pm

    #1403440

    Hello, versions and editions are SQL Server 2008, 10.0.4000.0, Enterprise Edition, with

    Visual Studio 2008, 9.0.307.29.1 SP, Enterprise edition.

    I can only post the package on Monday, when I return to my work, unfortunately.

    Thanks in advance,

    Best,

  • November 4, 2011 at 1:51 pm

    #1403490

    I'll be here. I am on the Pacific Time - please expect a reply after 4 PM GMT.

    Have a great weekend.

  • November 4, 2011 at 6:34 pm

    #1403555

    Try setting DELAY VALIDATION to TRUE, that may allow your package to complete the steps up through table creation, and look for the table when it needs the table, not when it first loads the package

  • November 7, 2011 at 5:51 am

    #1403870

    Dear all,

    thanks for your concern, but after a long time testing package, I realized that I had corrupted package.

    Sorry for the trouble, and thanks,

    Best,

  • November 7, 2011 at 7:41 am

    #1403959

    Dear friends,

    I must add this - I hope this will be the final. Regarding the corrupted template I wrote before - I deleted (had to for some reason) OLE DB destination, created new one, and the was my problem - again. Doesn't recognize the object.

    I did not check that again, could not get that...

    But my solution was: instead of Data flow task, I used Execute SQL task option with the code in it:

    INSERT INTO ?

    SELECT x,y,z

    FROM dbo.SourceTable.

    Speed of execution the task was great!

    And, finally, what is equally important: result is here!!

    Best,

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

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