| Author |
Topic |
|
Grifter
Constraint Violating Yak Guru
274 Posts |
 Posted - 2011-10-24 : 09:04:28
|
| HiI am looking for a simple example of using excel templates in ssis. I have to investigate this as part of my work and it is a team effort to investigate certain uses of ssis, I have no direct experience using ssis but have some knowledge of it from discussions and working with others on it. So my first real question is why would you use a template in ssisSecond would be how do you automate the process(whichever process that is)?ThanksG |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
Grifter
Constraint Violating Yak Guru
274 Posts |
Posted - 2011-10-24 : 09:36:59
|
HiThanks for links. I can't figure out how to do this bit:quote:
Add a variable “FileName”at Package Level having type string and assign path to your first Excel File, C:\ExcelFiles\First.xls
There is no instructions for how to assign the path of the excel sheet to the variableG |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-24 : 09:40:13
|
| you've variable window available on ssis. right click on ssis package and select variables and it will show variables window. just click on add variable button (1st button) and then you will be able to give it a name, data type and also default value which you set as excel file path------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Grifter
Constraint Violating Yak Guru
274 Posts |
Posted - 2011-10-24 : 11:05:51
|
| I'm not following the instructions in that link they are far too vague.G |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
Grifter
Constraint Violating Yak Guru
274 Posts |
Posted - 2011-10-25 : 06:50:43
|
| i can only pick up data from one file and want to know how I cannot pick up data from the other two files. From the instructions given in the first link it says add the file path for first file to the variable so how can it pick up data from the two other spreadsheets?I have the folowing and not sure where it is wrong:A for each loop container with it pointing to the appropriate directory containing my 3 excel files.In the same for each loop container properties I have the variable mapping User::FileName, Index = 0Within ForEachLoop I have execute sql task, which truncates the table so that SQL server table is cleared when execute task is run to clear existing recordsWithin data flow are an excel source and an OLE DB destination.Excel source is pointing to excel connection I have created. It also has name of the excel sheet = Sheet1$ (this drop down list also has sheet2$ and Sheet3$)OLE DB Connection has connection to my server and the table I have, which has the same schema as schema in excel spreadsheet.If I put data in one sheet and then run the package it updates the table but if I put data in any other spreadsheet it does not pick up that data.G |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-25 : 07:45:43
|
| for changing sheets you need to point to appropriate sheet in excel source. sheet is like table. so you cant point to one sheet and expect it to pull data from other sheets.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Grifter
Constraint Violating Yak Guru
274 Posts |
Posted - 2011-10-26 : 04:53:58
|
quote:
Originally posted by visakh16
for changing sheets you need to point to appropriate sheet in excel source. sheet is like table. so you cant point to one sheet and expect it to pull data from other sheets.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Sorry when I mentioned sheet2 and 3 I did not mean I was using them, I am only using sheet 1 on all 3 xl files. Currently I have a problem creating an expression in my excel connection. All other things seem fine and the variable does have the different file name at each loop iteration so that is fine. The problem I have is that it is only picking up data from first xl file THREE times, not once from each file. I strongly suspect I need to set up my connection string to use variable through the expression property of the excel connection manager. When I do that it fails with connection problem. Before I add expression I have these excel connection properties:quote:
ConnectionString = Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\andser\Desktop\ExcelAccessTraining\ExcelSSISExtract\First.xlsx;Extended Properties="Excel 12.0;HDR=YES";ExcelFilePath = C:\Users\andser\Desktop\ExcelAccessTraining\ExcelSSISExtract\First.xlsxExpressions = "" << I strongly suspect my variable needs to be used here but what is the syntax?
I have been working on this for 2 days and getting nowhere can anyone suggest how to do this? I have tried different ways from tutorials on the web and none of them work they all fail on connection and it is driving me loopy.For instance this link shws how to do a simple loop with connection and expression but mine does not work this way and I do not see why it shouldn't work:http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspxWhen i add an expression as per the the instructions in the link then my connection properties change to:quote:
ConnectionString = Provider=Microsoft.ACE.OLEDB.4.0; <<< Changes automatically when expression is added and to oledb ver. 4.0ExcelFilePath = "" <<< Changes automatically when expression is addedExpressions = @[User::ExcelFiles]
Also excel connection property excelversionnumber changes from 4 to 3???G |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-26 : 13:57:15
|
| can you give step by step details of how you set the expression? for which property you set the expression?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Grifter
Constraint Violating Yak Guru
274 Posts |
Posted - 2011-10-27 : 10:20:06
|
| HiOk I worked this out ok now, I just didn't fully understand what I was supposed to be doing but have got it now.There is something else that I would now like to try and that is take the data from the table I have created in SQL Server with data from the excel files and import it into Xcelsius. I am looking into how to do this but if anyone can suggest a good tutorial on doing this it would be good to see.ThanksG |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
Excel Templates and Automating Process