Post reply

From a flat File into a Proc as Table-Valued Param. Getting error.

  • June 12, 2009 at 1:02 pm

    #133166

    Howdy

    I didn't see a 2008 SSIS section so mods I apologize and please move if needed.

    In SSIS, trying to take a flat file and using OLE DB Command trying to execute a proc that has a TVP.

    The TVP is declared as readonly, however the error I get is..."The table-valued parameter "@P1" must be declared with the readonly option."

    Anyone done this yet?

  • June 12, 2009 at 1:37 pm

    #1008880

    are yu posotive you want to extract and trannsform your data this way??

    Any reason you can't bulk insert and run updates via execute sql task?

    That should perform faster

  • June 12, 2009 at 2:01 pm

    #1008885

    To use TVP in SQL 2008 you first need to create a UDT based on the data type Table. I'm not sure if what youre trying to do is possible.

    Here's a link with more info http://go.microsoft.com/fwlink/?LinkID=126190

    Alberto De Rossi
    Microsoft Certified Solutions Associate - SQL Server
    Microsoft MVP - Data Platform
    Power BI User Group Lima - Community Leader

  • June 12, 2009 at 2:54 pm

    #1008906

    Thanks!

    This should remove some confusion.

    The proc and it's TVP already exist.

    The proc is a massive 1000+ line beast that does heavy processing.

    The exising call to that proc is old vb that is called from a scheduled task.

    I don't want to remake the wheel by redoing all the functionality in the proc in ssis.

    I would like to but I just don't have the time for that.

    Since we're using cool new 2008 stuff I want to replace the anitquated vb.

    I am leaning towards it not being possible.

    Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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