load match excel file data into another folder
Please let me know dealing with below task
Table 1 is in sql server data base
Table 1
col1
plp
mkt
pom
folder contain excel file with name plp,mkt etc
Need to match table1 col1 with folder file name and want to load match folder file data into another folder .
If you know how to do it please let me know.Thanks
December 17th, 2010 1:03am
Use an Execute SQL Task to retrieve the rowset into an SSIS Object variable. Use a SELECT statement in the Execute SQL Task, set the ResultSet type to Full Result Set, go to the Result Set tab and assign the result to your Object variable.
Use a Foreach Loop Container with the Foreach ADO Recordset enumerator to loop over your Object typed SSIS variable. Create a String typed SSIS variable for the file name. On the Variable Mappings tab, assign index 0 (zero) to the string variable.
Create two other string variables - one for the full source pathname, and one for the full destination pathname. Set both to EvaluateAsExpression. Write an expression to hardcode the source path plus the filename variable into the source pathname
variable. Do a similar expression for the destination pathname variable.
Inside the Foreach Loop Container, use a File System Task to copy or move files. Set the source and destination to those variables.
Talk to me now on
Free Windows Admin Tool Kit Click here and download it now
December 17th, 2010 1:32am
Hi Todd,
Many Thanks .This is much appreciated.As you said I configured Execute sql task ,got file name from table and store in SSIS object variable.
Inside Foreach loop passed SSIS object variable and create ssis string variable to store match file name .It's working fine ,I got matched file name .But instead of using File system task ,moving match file data from one folder to other ,I have to use web
service task and move match file data from folder to some http location .Do you think if we put web service task inside this Foreach loop container and by configuring we can moved match file data .Please let me know about this because I never used web service
task .Thanks
December 18th, 2010 7:50pm
Thanks.I am able to move match table file name data from one folder to other but requirement say to move data from folder to some http location ,need to use web service task .I am working on it if you know anything about that please let me know.Thanks
Free Windows Admin Tool Kit Click here and download it now
December 19th, 2010 8:51pm
You should be able to do so - but it's heavily dependent on the web service you're using. Good luck.
Talk to me now on
December 22nd, 2010 2:36am