ssis
i want to take a excel file (everyday diff)from my folder and
send that file to my email using send mail task using ssis and dumps data to sql table.
and in that file, the data and name keeps changing everyday..so i dont know how to create a varible in connection string that keep changing excel connection manager..
Answers
Just to add on what Mohan has suggested:
Take a variable (sheetname) inside SSIS package and give the name of excel sheet as its value (Sample.xls).
Then configure the data flow task as required, using dataaccessmode as table/view name from variable in excel source.
Then select the variable sheetname.
Go to expressions properties of the excel connection manager and write an expression for its connectionstring:"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + "C:\\MyFile\\" + @[User::sheetname] + "
;Extended Properties=" +"\"EXCEL 8.0;HDR=YES;;" + "\";"
Above conection string is to read the file from C:\MyFile\Sample.xls.
Now use Mohans suggestion to change the value of excel file name using Package Configuration.
Nitesh Rai- Please mark the post as answered if it answers your question- Marked As Answer byNai-dong Jin - MSFTMSFT, ModeratorTuesday, November 10, 2009 3:33 AM
- Proposed As Answer byBharani 3010 Wednesday, November 04, 2009 4:03 AM
All Replies
- There are multiple way to achieve this but one way is to :
1. enable "Package Configuration", select configuration type SQL Server provide table name that will keep all configuration information.
2. Update ConfiguredValue column before executing SSIS
3. Run SSIS- Proposed As Answer byMohan Kumar - SQLVillage.com Tuesday, November 03, 2009 11:01 PM
Just to add on what Mohan has suggested:
Take a variable (sheetname) inside SSIS package and give the name of excel sheet as its value (Sample.xls).
Then configure the data flow task as required, using dataaccessmode as table/view name from variable in excel source.
Then select the variable sheetname.
Go to expressions properties of the excel connection manager and write an expression for its connectionstring:"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + "C:\\MyFile\\" + @[User::sheetname] + "
;Extended Properties=" +"\"EXCEL 8.0;HDR=YES;;" + "\";"
Above conection string is to read the file from C:\MyFile\Sample.xls.
Now use Mohans suggestion to change the value of excel file name using Package Configuration.
Nitesh Rai- Please mark the post as answered if it answers your question- Marked As Answer byNai-dong Jin - MSFTMSFT, ModeratorTuesday, November 10, 2009 3:33 AM
- Proposed As Answer byBharani 3010 Wednesday, November 04, 2009 4:03 AM
