cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ryatatu
Frequent Visitor

power query with HTML.table not working in SSIS package, but surprisingly works fine in PBI desktop

Dear BI Professionals,

 

i met the following challenge for me: 

having used power query (with HTML.table) PBI desktop worked fine = HTLM table successfully parsed into table for further using inside PBI model. 

 

but i need to grab HTML table into SQL table by means of SSIS package. 

and when using the same query inside SSIS surprisingly got only blank values when expected to get as the same table as PBI table 

 

Any help how to make it works = will be appreciated. 

 

M code looks simple and shows no mistakes inside PQ Editor: 

 

let linked_tbls =
(wb as text) as table =>
let
#"Extracted Table From Html" = Html.Table(wb, {{"Column1", "TABLE[id='RelLinks'] > * > TR > :nth-child(1)"}, {"Column2", "TABLE[id='RelLinks'] > * > TR > :nth-child(2)"}, {"Column3", "TABLE[id='RelLinks'] > * > TR > :nth-child(3)"}, {"Column4", "TABLE[id='RelLinks'] > * > TR > :nth-child(4)"}, {"Column5", "TABLE[id='RelLinks'] > * > TR > :nth-child(5)"}, {"Column6", "TABLE[id='RelLinks'] > * > TR > :nth-child(6)"}, {"Column7", "TABLE[id='RelLinks'] > * > TR > :nth-child(7)"}}, [RowSelector="TABLE[id='RelLinks'] > * > TR"]),
#"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Тип", type text}, {"№ проекта", Int64.Type}, {"Рег.номер", type text}, {"Наименование", type text}, {"Статус", type text}, {"Состояние", type text}, {"Связь", type text}})
in
#"Changed Type",

linked_tbls2 =
(wb as text) as table =>
let
#"Extracted Table From Html" = Html.Table(wb, {{"Column1", "TABLE[id='RelOthers'] > * > TR > :nth-child(1)"}, {"Column2", "TABLE[id='RelOthers'] > * > TR > :nth-child(2)"}, {"Column3", "TABLE[id='RelOthers'] > * > TR > :nth-child(3)"}, {"Column4", "TABLE[id='RelOthers'] > * > TR > :nth-child(4)"}, {"Column5", "TABLE[id='RelOthers'] > * > TR > :nth-child(5)"}, {"Column6", "TABLE[id='RelOthers'] > * > TR > :nth-child(6)"}, {"Column7", "TABLE[id='RelOthers'] > * > TR > :nth-child(7)"}}, [RowSelector="TABLE[id='RelOthers'] > * > TR"]),
#"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Тип", type text}, {"№ проекта", Int64.Type}, {"Рег.номер", type text}, {"Наименование", type text}, {"Статус", type text}, {"Состояние", type text}, {"Связь", type text}})
in
#"Changed Type",

Source = Sql.Database("msk-ssbi-001", "DocStat"),
dbo_ORD_PROCESS = Source{[Schema="dbo",Item="ORD_PROCESS"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(dbo_ORD_PROCESS,{"PK", "RelationsLinks"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [RelationsLinks] <> null and [RelationsLinks] <> ""),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each try
linked_tbls([RelationsLinks])
otherwise try linked_tbls2([RelationsLinks])
otherwise null),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Тип", "№ проекта", "Рег.номер", "Наименование", "Статус", "Состояние", "Связь"}, {"Тип", "№ проекта", "Рег.номер", "Наименование", "Статус", "Состояние", "Связь"})
// #"Filtered Rows1" = Table.SelectRows(#"Expanded Custom", each [Тип] <> null and [Тип] <> "")
in
#"Expanded Custom"

ryatatu_0-1634125779700.png

 

2 ACCEPTED SOLUTIONS
v-jingzhang
Community Support
Community Support

Hi @ryatatu 

 

From this link I found that HTML.Table function is currently not supported in SSIS.

------------------------------------------------------

The Html.Table nor the Web.BrowserContents functions are currently supported in SSIS. That's because those functions were still in preview when the latest version of the SSIS component was released. The SSIS team hasn't made any updates to the component and it's been the same since 12/24/2019. You could try and use the Web.Page function to emulate a similar approach to the one that you use with the other functions, but if you're solely relying on CSS selectors this might not work.

------------------------------------------------------

 

Additionally, this blog may be helpful. It is using Web.Page with SSIS. 

Power Query Source for SQL Server Integration Services (mssqltips.com)

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

View solution in original post

Thank YOU!

I made replacement Html.Table was replaced by Web.Page and got success ! 

Now it works! 

 

let linked_tbls =
(wb as text) as table =>
let
Source = Web.Page(wb),
Data = Source{0}[Data]
in
Data,

Source = Sql.Database("msk-ssbi-001", "DocStat"),
dbo_ORD_PROCESS = Source{[Schema="dbo",Item="ORD_PROCESS"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(dbo_ORD_PROCESS,{"PK", "RelationsLinks"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [RelationsLinks] <> null and [RelationsLinks] <> ""),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each linked_tbls([RelationsLinks])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Тип", "№ проекта", "Рег.номер", "Наименование", "Статус", "Состояние", "Связь"}, {"Тип", "№ проекта", "Рег.номер", "Наименование", "Статус", "Состояние", "Связь"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Custom", each [Рег.номер] <> null and [Рег.номер] <> "")
in
#"Filtered Rows1"

View solution in original post

3 REPLIES 3
v-jingzhang
Community Support
Community Support

Hi @ryatatu 

 

From this link I found that HTML.Table function is currently not supported in SSIS.

------------------------------------------------------

The Html.Table nor the Web.BrowserContents functions are currently supported in SSIS. That's because those functions were still in preview when the latest version of the SSIS component was released. The SSIS team hasn't made any updates to the component and it's been the same since 12/24/2019. You could try and use the Web.Page function to emulate a similar approach to the one that you use with the other functions, but if you're solely relying on CSS selectors this might not work.

------------------------------------------------------

 

Additionally, this blog may be helpful. It is using Web.Page with SSIS. 

Power Query Source for SQL Server Integration Services (mssqltips.com)

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

View solution in original post

Thank YOU!

I made replacement Html.Table was replaced by Web.Page and got success ! 

Now it works! 

 

let linked_tbls =
(wb as text) as table =>
let
Source = Web.Page(wb),
Data = Source{0}[Data]
in
Data,

Source = Sql.Database("msk-ssbi-001", "DocStat"),
dbo_ORD_PROCESS = Source{[Schema="dbo",Item="ORD_PROCESS"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(dbo_ORD_PROCESS,{"PK", "RelationsLinks"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [RelationsLinks] <> null and [RelationsLinks] <> ""),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each linked_tbls([RelationsLinks])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Тип", "№ проекта", "Рег.номер", "Наименование", "Статус", "Состояние", "Связь"}, {"Тип", "№ проекта", "Рег.номер", "Наименование", "Статус", "Состояние", "Связь"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Custom", each [Рег.номер] <> null and [Рег.номер] <> "")
in
#"Filtered Rows1"

View solution in original post

@ryatatu Great job! It looks Web.Page makes the code easier.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors