April 5, 2018 at 5:24 pm
Hi All,
I am using a for each loop with a data flow task that is using a ADO .NET for the excel files connection (excel connector is not working). The loop and ADO net connector loop through the 3 files in the source folder but it is loading the same file 3 times. I have the filename mapped in the loop variable mappings, but how do I pass this to the ADO NET connection manager via a expression?
I have tried using the 'server name' expression but this doesn't work.
Any suggestions?
April 6, 2018 at 6:47 am
I would handle this in a script object either using C# or VB.net, rather than a for loop container, with a variable holding the loop number and incremented after each pass. Using FOLDER and FILE objects to get the number of files in the folder and then assign this to another variable, loop until the incremented variable is greater than the number of files. Process each file accordingly then exit the script. I know Excel connections do not allow dynamic assigning of file names, i.e. the file specified in an Excel Connection cannot change.
Gordon Barclay
April 6, 2018 at 8:09 am
At work we did it parameterising the Connection property of the package-level OLEDB connector (which allows parameterisation)
MyProcessingFile is set with the for-each iterator
Connectionstring
"Data Source=" + @[User::MyProcessingFile] + ";Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1;\";Mode=Read;"
in the package-xml it looks like
<DTS:ConnectionManagers>
<DTS:ConnectionManager
DTS:refId="Package.ConnectionManagers[MyExcel]"
DTS:CreationName="OLEDB"
DTS:DTSID="{89637DFA-9675-4F6E-B64F-E3B3E4613F74}"
DTS:ObjectName="MyExcelConnection">
<DTS:PropertyExpression
DTS:Name="ConnectionString">@[User::Connectionstring]</DTS:PropertyExpression>
<DTS:ObjectData>
<DTS:ConnectionManager
DTS:ConnectionString="Data Source=;Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;Extended Properties="Excel 12.0;HDR=YES;IMEX=1;";Mode=Read;" />
</DTS:ObjectData>
</DTS:ConnectionManager>
</DTS:ConnectionManagers>
replace big smile with : D (without spaces), tongue-smiley with : P (without spaces)
April 8, 2018 at 6:45 pm
Thanks for the replies.
I am still unable to connect to the excel xlsx files, These are being generated by IBM TM1. If open and save the excel file then I can make a connection. But the TM1 files sourced files are not working via the excel driver 32 or 64 bit. Any suggestions?
April 8, 2018 at 8:21 pm
I got it working using the Kingsway excel connector. The Microsoft excel connector wont accept the metadata.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply