I am trying to use Power Query in SSIS to import three CSV files located in a folder (this is a test environment to do a proof of concept for SSIS/Import From Folder/Using Power Query Transforms):
My Power Query Script is as follows:
let
Source = Folder.Files("D:\Documents\PBITestFolder"),
Navigation1 = Source{0}[Content],
RemoveColumns = Table.RemoveColumns(Source,{"Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
Transformed = Table.AddColumn(RemoveColumns, "Transform File", each #"Transform File"([Content])),
Transformed2 = Table.ExpandTableColumn(Transformed, "Transform File", Table.ColumnNames(#"Transform File"(Navigation1))),
Transformed3 = Table.RemoveColumns(Transformed2,{"Content"}),
Transformed4 = Table.ReorderColumns(Transformed3,{"FieldA", "FieldA_1", "FieldB", "FieldC", "Name"}),
Transformed5 = Table.RemoveColumns(Transformed4,{"FieldA"}),
Transformed6 = Table.RenameColumns(Transformed5,{{"FieldA_1", "FieldA"}})
in
Transformed6
I am getting the following error from SSIS:
===================================
The import Transform File matches no exports. Did you miss a module reference? (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HandleUserException(Exception e)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostReinitializeMetaData(IDTSManagedComponentWrapper100 wrapper)
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowComponentUI.ReinitializeMetadata()
at Microsoft.SqlServer.IntegrationServices.PowerQuery.UI.PowerQuerySourceUI.ConnMgrsPage_ConnectionManagersPageSaved(Object sender, EventArgs e)
It appears that because the connection manager is defined as a folder the columns of the transformed table emerging from the Power Query Script are not carrying the actual column names and feeding the column names to SSIS for mapping.
The script works fine in Power BI. The CSVs are simple ... three columns, FieldA, FieldB, FieldC. I can upload them if need be. They are stored in a single folder. The Proof of Concept is to prove we can use Power Query instead of SSIS For Each loops to import/transform files.
