SheetNames appear differently in SSIS compared to SmartSheet

senpnathan

Hello,

I am trying pull the information from Smartsheet Dynamically using SSIS. I see that the Sheet Names in SmarSheet is different to what I see from SSIS ODBC Source.

For Example :

          Names in SmartSheet                Displayed Name in SSIS

• 801 Centre Road                        801 Centre Road__8524479023343492s_(Home)
• Canterbury Road                        Canterbury Road__4221359879415684s_(Home)
• Burke Road Construction Programme Rev. 6 Burke Road Construction Programme Rev_ 6__490216562681732s_(Home)

Is there a way to find the exact sheet names as in SmartSheet?

If not will the names be displayed same as above SSIS across all users? Why I ask this is if I change the user name in the ODBC source connection for Smarsheet in future SSIS and if the names are shown differently in SSIS for these sheets then the SSIS would fail to pull the data as it would say that the mentioned Sheet is not present in Smartsheets.

Alternatively could we access the metadata of the sheets, may by an admin to see what exact sheet name is stored there?

Please advice.

Thanks

Senthil

Michael Weiland

Hi Senthil,

Thanks for reaching out with your question. The reason that the sheet name is displayed differently between Smartsheet and SSIS, is because the ODBC Driver is designed to use format SheetName_SheetID_Location. The reason that this format is used is because unique sheet names are not enforced in Smartsheet, so attaching the SheetID and Location to the Sheet Name help keeps the Name unique when you are writing SQL queries.

The Sheet Name will appear in the same format to all users that are shared to the sheet. There will be a SheetName_SheetID_Location format, and this will be the same accross all Viewers, Editors and Admins shard to that sheet.

There would not be a way to access the exact sheet name in Smartsheet via the ODBC, but this may be something that your team will be able to do on your side, but the ODBC Driver will always return the SheetName_SheetID_Location file name format to ensure the uniqueness can be used when using the sheet name in a SQL Query.

Please let me know if you have any questions and I'm happy to help!

Kind regards,

Michael

Smartsheet Support

senpnathan

Hi Michael,



I need your help, even after providing NVarchar(max) to retrieve data only 255 characters get stored, please could you advice?

 

CHeers

Senthik