Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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"
Solved! Go to Solution.
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.
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"
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.
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"