tag:blogger.com,1999:blog-2303058199815958946.post5358037872631911878..comments2024-03-28T10:36:35.016+01:00Comments on Microsoft SQL Server Integration Services: How to configure a Foreach Loop Container: File EnumeratorJoost van Rossumhttp://www.blogger.com/profile/01125981589974671317noreply@blogger.comBlogger28125tag:blogger.com,1999:blog-2303058199815958946.post-54208611977533456602016-08-12T18:35:04.428+02:002016-08-12T18:35:04.428+02:00It's a zero based collection. You can either u...It's a zero based collection. You can either use the column names (if you know them) or use the 0-th column and if there are more columns the 0, 1, 2 - column....Joost van Rossumhttps://www.blogger.com/profile/01125981589974671317noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-21876253491060753782016-08-12T17:08:07.539+02:002016-08-12T17:08:07.539+02:00More I read about File Enumerator, more confused I...More I read about File Enumerator, more confused I get. I understand, that we have a collection of files and our FileName variable will be changing the value as we go through the list of files. But.. am I missing something here or all these examples are lacking the explanation of the why the FIleName variable has an index 0? How do we map the FileName variable, if we don't know the index of the respective property for each item?Anonymoushttps://www.blogger.com/profile/10190494726445850086noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-54441611088670742162015-07-07T22:40:31.825+02:002015-07-07T22:40:31.825+02:00Great help, thanks!Great help, thanks!DRhttps://www.blogger.com/profile/16202252784496884710noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-15992447641129213002015-07-07T08:47:15.338+02:002015-07-07T08:47:15.338+02:00In the FTP Task you need to overwrite the LocalPat...In the FTP Task you need to overwrite the LocalPath property with an expression that uses the variable from the foreach loop. Edit the FTP Task and go to the Expressions page. Locate that property and use the foreach loop variable to overwrite its value. To have differnt remote folders you need to thing of an expression on the RemotePath propery (or use two FTP Tasks. One for succeeded files an one for failed file).<br /><br />If the variable contains and empty string then you might want to enter a dummy value or set the DelayValidation property of the FTP Task to true to get it validated in designtime.Joost van Rossumhttps://www.blogger.com/profile/01125981589974671317noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-29442228172689458032015-07-07T02:27:04.105+02:002015-07-07T02:27:04.105+02:00Great article. Just what I needed.
But now, I wan...Great article. Just what I needed.<br /><br />But now, I want to upload processed files to a Successful folder on the FTP site and failed imports to a Failed folder.<br /><br />I have not been able to figure out how to do that. It doesn't like the connections I have.<br /><br />Any ideas?<br /><br />Regards,<br /><br />DanDRhttps://www.blogger.com/profile/16202252784496884710noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-20223534465375331202015-01-03T15:23:08.647+01:002015-01-03T15:23:08.647+01:00That is only possible when you can distinguish the...That is only possible when you can distinguish the difference on the filename. That way you can have multiple Data Flow Tasks within your loop and start the correct Data Flow Task depending on the filename.<br />Another option is to read everything as one big column.... or to take a look at the third party data flow task from <a href="http://www.cozyroc.com/" rel="nofollow">cozyroc</a>Joost van Rossumhttps://www.blogger.com/profile/01125981589974671317noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-13351903485211033622015-01-03T13:52:29.810+01:002015-01-03T13:52:29.810+01:00Well explained....
One tricky question...how to re...Well explained....<br />One tricky question...how to read multiple CSV files each having different number of columns??<br /><br />Regards,<br />Nishant Nishanthttps://www.blogger.com/profile/06809158287109088923noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-53269481022511889172013-06-15T13:18:37.080+02:002013-06-15T13:18:37.080+02:00You could use the sorted foreach loop enumerator:
...You could use the sorted foreach loop enumerator:<br /><a href="http://microsoft-ssis.blogspot.com/2011/04/how-to-configure-foreach-loop-container.html" rel="nofollow">http://microsoft-ssis.blogspot.com/2011/04/how-to-configure-foreach-loop-container.html</a><br />or my free custom enumerator:<br /><a href="http://microsoft-ssis.blogspot.com/2012/01/custom-ssis-component-foreach-sorted.html" rel="nofollow">http://microsoft-ssis.blogspot.com/2012/01/custom-ssis-component-foreach-sorted.html</a>Joost van Rossumhttps://www.blogger.com/profile/01125981589974671317noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-54293169539851046252013-06-15T13:16:07.078+02:002013-06-15T13:16:07.078+02:00Hi,
I am using foreach file enumarator to load mu...Hi,<br /><br />I am using foreach file enumarator to load multiple files in to Sql server.Along this i have implimented SCD1 using scd component.My issue is this Foreach component takes latest file first for loading making my SCD logic useless. Any idea how to get old file first?<br /><br />Regards,<br /><br />Chaitanya Chaitanyanoreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-40668183966845011052013-06-11T07:19:08.658+02:002013-06-11T07:19:08.658+02:00You can use the variable in a Derived Column Trans...You can use the variable in a Derived Column Transformation to create/fill a filename column or you could use the Flat File Source component to create a <a href="http://sqlblog.com/blogs/jamie_thomson/archive/2010/03/31/filenamecolumnname-property-flat-file-source-adapter-ssis-nugget.aspx" rel="nofollow">filename</a> column. Also check out the SSIS <a href="http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/threads" rel="nofollow">forum</a> on MSDN.Joost van Rossumhttps://www.blogger.com/profile/01125981589974671317noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-35201630682842962552013-06-10T23:27:48.922+02:002013-06-10T23:27:48.922+02:00I'm kind of a SSIS newbie so I have to ask . ....I'm kind of a SSIS newbie so I have to ask . . . I'd like to import the file name itself into an additional column in my staging table. Is it possible to map the variable to a column in my OLE DB Destination?<br /><br />Thanks,<br /><br />Andre RanieriAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-1229340456616880992013-02-18T02:44:29.942+01:002013-02-18T02:44:29.942+01:00Great tutorial - a real good help. Thank you very ...Great tutorial - a real good help. Thank you very much<br />ScottAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-36837478464559805322013-02-01T12:25:06.334+01:002013-02-01T12:25:06.334+01:00There was apparently no way to preserve the connec...There was apparently no way to preserve the connection manager so I decided not to waste anymore time on it. It wrote a script to write the file to the new location.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-71619074062734373512013-01-30T16:40:18.232+01:002013-01-30T16:40:18.232+01:00What you mentioned did have an effect, though. The...What you mentioned did have an effect, though. The Connection String - for instance in the properties of the File Connection - wasn't erased during Build or Deployment. However, on the server whereto the package was deployed the Connection Manager is nowhere to be found. So, we're not there yet. But thanks for the help sofar!<br />PaulAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-59364586155799564052013-01-30T16:11:58.081+01:002013-01-30T16:11:58.081+01:00I was surprised with how easy that was. But then, ...I was surprised with how easy that was. But then, no sigar. While deploying the Connection Manager is removed from the package, since it is deemed incomplete even with a default value for the variable.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-25285663719238903452013-01-30T15:23:44.389+01:002013-01-30T15:23:44.389+01:00Hi Anonymous,
Just fill the variable with a defau...Hi Anonymous,<br /><br />Just fill the variable with a default value...Joost van Rossumhttps://www.blogger.com/profile/01125981589974671317noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-17539539517621586552013-01-30T13:46:05.026+01:002013-01-30T13:46:05.026+01:00This is what I do. I use the filename variable aqu...This is what I do. I use the filename variable aquired in de foreach loop to write a new file to a different server. <br /><br />However, during a build something goes wrong. During the build the foreach loop is not running and so the variable stays empty. So the connection manager that is using the variable as a filename is not exported during a build because it needs a filename to be valid.<br /><br />Any solution to this?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-90905387418136907292013-01-23T22:22:27.753+01:002013-01-23T22:22:27.753+01:00Hi Shiwam,
Not sure what you mean... you have the...Hi Shiwam,<br /><br />Not sure what you mean... you have the name of a text file in a variable filled by the Foreach Loop. Let's say fileA.csv and you now need the corresponding filename from excel? fileA.xls?<br /><br />Please explain or post a message in the <a href="http://social.msdn.microsoft.com/forums/en-us/sqlintegrationservices/threads/" rel="nofollow">SSIS MSDN forum</a>.Joost van Rossumhttps://www.blogger.com/profile/01125981589974671317noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-90436374743314517542013-01-23T21:50:28.567+01:002013-01-23T21:50:28.567+01:00Hi,
Is there any way we can fetch the text file n...Hi,<br /><br />Is there any way we can fetch the text file name which is being used in each enumeration and can be mapped in a variable.<br />I need to load data from a text file to excel file having same name s text file name.Shiwamhttps://www.blogger.com/profile/05323218108133138312noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-34610883543956887882013-01-17T14:15:24.081+01:002013-01-17T14:15:24.081+01:00Hi Unknown,
Thanks for your comment, but I don...Hi Unknown,<br /><br />Thanks for your comment, but I don't get it. I think all you need to know about looping through excel files is mentioned... please explain yourself. Or contact me via de Contact me link in the menu.<br /><br />Kind regards,<br /><br />JoostJoost van Rossumhttps://www.blogger.com/profile/01125981589974671317noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-42927585451166290332013-01-17T10:32:24.752+01:002013-01-17T10:32:24.752+01:00Hi Thanks for posting wonderful article. But you h...Hi Thanks for posting wonderful article. But you have missed to tell ExcleFilePath under [5) Expression] section. We have to select ExcleFilePath under Expression part under Excle connection properties. <br /><br />Note: Reader please make correction while reading this articleUnknownhttps://www.blogger.com/profile/12692713796364749163noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-65329526602211448242012-09-17T10:58:09.540+02:002012-09-17T10:58:09.540+02:00Maybe a bit confusing, but it's the name of th...Maybe a bit confusing, but it's the name of the package... The package doesn't contain a Foreach Loop yet in step 1.Joost van Rossumhttps://www.blogger.com/profile/01125981589974671317noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-25001958647966899332012-09-17T10:36:43.129+02:002012-09-17T10:36:43.129+02:00Scope of variable should be SSIS package not Forea...Scope of variable should be SSIS package not Foreach Loop. When I created User::FilePath variable in Foreach Loop scope, I couldn't see User::FilePath varibale in sourcefile connection expression editor.beehappyalwaysshttps://www.blogger.com/profile/02102282855308647521noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-9151253439645771062012-06-22T11:17:04.559+02:002012-06-22T11:17:04.559+02:00thanks, I'll use them.thanks, I'll use them.Anonymoushttps://www.blogger.com/profile/00531065067883625379noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-90982754934612829632012-06-22T11:03:50.137+02:002012-06-22T11:03:50.137+02:00Something went wrong during installation. Which ve...Something went wrong during installation. Which version of SSIS are you using? There are blogs about it:<br /><a href="http://www.halhayes.com/blog/PermaLink,guid,07ed62c7-58a6-4575-a035-765ec065dc9b.aspx" rel="nofollow">http://www.halhayes.com/blog/PermaLink,guid,07ed62c7-58a6-4575-a035-765ec065dc9b.aspx</a><br /><br />But the best place to ask general ssis questions is the msdn forum:<br /><a href="http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/fb2db01a-7f6e-4111-ac4f-1f339c200df9" rel="nofollow">http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/fb2db01a-7f6e-4111-ac4f-1f339c200df9</a>Joost van Rossumhttps://www.blogger.com/profile/01125981589974671317noreply@blogger.com