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

  • Olga Klimova

    SSC Enthusiast

    Points: 171

    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

  • SaintGr8

    SSC Veteran

    Points: 229

    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

  • Daniel Parkinson

    Old Hand

    Points: 300

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

  • kev4king2000

    SSC Enthusiast

    Points: 173

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

  • Olga Klimova

    SSC Enthusiast

    Points: 171

    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

  • kev4king2000

    SSC Enthusiast

    Points: 173

    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.

  • Olga Klimova

    SSC Enthusiast

    Points: 171

    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

  • SaintGr8

    SSC Veteran

    Points: 229

    Beautiful!

    It works. Olga, you rock!

  • kev4king2000

    SSC Enthusiast

    Points: 173

    Wow! thanks a lot Olga. It works.

    Nice one.

  • Thomas LeBlanc

    SSCertifiable

    Points: 7980

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

    Thanks,

    Thomas

    Thomas LeBlanc, MVP Data Platform Consultant

  • SaintGr8

    SSC Veteran

    Points: 229

    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"

  • Thomas LeBlanc

    SSCertifiable

    Points: 7980

    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

  • jhsettles

    Newbie

    Points: 1

    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

  • sneumersky

    SSCertifiable

    Points: 7667

    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 14 (of 14 total)

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