Post reply

Custom SSMS Shortcuts for ETL Developer. Part 3: Source-Target Mapping

  • September 29, 2011 at 12:07 am

    #248657

    Comments posted to this topic are about the item Custom SSMS Shortcuts for ETL Developer. Part 3: Source-Target Mapping

    Olga Klimova
    BI Consultant, MCITP
    www.returnonintelligence.ca

  • September 29, 2011 at 2:02 am

    #1388213

    I so much enjoyed the lecture. I mean this is simply beautiful. I cannot wait to have it up and running in my environment.

    I ran the query [sp_SourceTargetMapping] and then drag the Person.Person Table unto the query window after setting up the shortcut for execution but got the following error:

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near '.'.

    Does it matter which database is used to run the proc?

    Is there anything that I should change in the s. proc e.g @tablename?

    Please help out.

    Thank you

  • September 29, 2011 at 5:47 am

    #1388296

    First time I've actually done anything with the short cuts, pretty cool!

  • September 29, 2011 at 5:49 am

    #1388299

    Thanks a lot for the lovely teaching.But how do I implement it to work in my environment?

  • September 29, 2011 at 5:59 am

    #1388306

    Thank you!

    Please make sure you're enclosing the entire table name in delimited identifiers (quotation marks or brackets - the same measures required as I commented in http://www.sqlservercentral.com/Forums/Topic1179153-3010-3.aspx).

    And yes, you need to connect to the 'source' database this table belongs to (AdventureWorks2008R2 in this case).

    Olga Klimova
    BI Consultant, MCITP
    www.returnonintelligence.ca

  • September 29, 2011 at 6:12 am

    #1388312

    Thanks a lot for the lovely teaching.

    This proc was ran against Adventureworks2008 without changing anything in the original code.

    Do I need to change @TableName to any name in the Adventureworks2008 table? Because when I ran the code I got an error like this:

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near '.'.

    I'd love to know why it didn't work.

    Please elaborate on this.

    Thank you.

  • September 29, 2011 at 6:30 am

    #1388321

    Thank you!

    In regard to Incorrect syntax near '.':

    Please make sure you're enclosing the entire table name in delimited identifiers - use quotation marks or brackets: "Person.Person" (the same measures required as I commented earlier).

    And yes, you need to connect to the 'source' database this table belongs to (AdventureWorks2008R2 in this case).

    Olga Klimova
    BI Consultant, MCITP
    www.returnonintelligence.ca

  • September 29, 2011 at 6:49 am

    #1388333

    Beautiful!

    It works. Olga, you rock!

  • September 29, 2011 at 6:53 am

    #1388338

    Wow! thanks a lot Olga. It works.

    Nice one.

  • September 29, 2011 at 7:51 am

    #1388374

    I get SELECT * FROM [Person].[Contact] when I run it.

    Thanks,

    Thomas

    Thomas LeBlanc, MVP Data Platform Consultant

  • September 29, 2011 at 7:56 am

    #1388378

    Thomas LeBlanc (9/29/2011)

    I get SELECT * FROM [Person].[Contact] when I run it.

    Thanks,

    Thomas

    Make sure your table is within the double quote, e.g: "HumanResources.Employee"

  • September 29, 2011 at 8:07 am

    #1388383

    The problem was I created the SP in our DBA database on the instance, then the shortcut was DBA.dbo.sp_xxx.

    The SP and table have to be in the same database and the shortcut.

    Thanks,

    Thomas

    Thomas LeBlanc, MVP Data Platform Consultant

  • October 10, 2011 at 2:03 pm

    #1392585

    Works great! Thanks very much for sharing!

    I made a couple modifications to the stored procedure for tables with large number of columns.

    In the Declaration section, increase size from 4000 to Max:

    DECLARE @cmd NVARCHAR (Max)

    DECLARE @cmd_loc NVARCHAR (Max)

    Then because Print @Cmd is limited to 4000 characters:

    -- To see @Cmd when longer than 4,000 characters

    If LEN(@Cmd)>4000

    BEGIN

    Print '@Cmd exceeds 4,000 characters.'

    Select @cmd as Cmd Into #tmpCmd

    Select Cmd from #tmpCmd

    Drop Table #tmpCmd

    END

    Else

    BEGIN

    PRINT @cmd -- Max length for Print is 4,000 Characters

    END

    --EXEC sp_executesql @cmd

    Joe Settles, Nashville, TN

  • April 4, 2013 at 12:54 pm

    #1603709

    Looks like the perfect tool for following the ETL Standards as outlined by Joy and Warren in chapter 7. Good job 🙂

Viewing 14 posts - 1 through 13 (of 13 total)

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