August 27, 2008 at 1:37 pm
I'm designing an SSIS package to load an Excel file in a folder each week to sql server. Every week the name of the Excel file changes but I just need to load whatever Excel file is in that folder so in the SSIS package I have a script task (code below) before the data flow task to dynamically get the source file. But I execute this script task I got a runtime error like: The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.
I want to know where is the problem with the script. THANKS!
More information: I use Excel Connection Manager in the data flow Excel Source and in the Properties window of the Excel Connection Manager and the Expression Editor window I added a ConnectionString property and use the variable @[User::TotFileName] as the connection so the source file can change every time it runs.
And in the script page of script task editor, I have importFolder as the ReadOnlyVariable and TotFileName as ReadWriteVariable.
------------------------------------------------------------------------------------------------------------------------------------------------
Option Strict Off
Imports System
Imports System.IO
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dim sFolder
Dim fso
Dim fsoFolder
Dim fsoFile
Dim sFileName
Dim TotFileName As String
sFolder = Dts.Variables.Item("ImportFolder").Value
fso = CreateObject("Scripting.FileSystemObject")
fsoFolder = fso.getFolder(sFolder)
For Each fsoFile In fsoFolder.Files
sFileName = sFolder & fsoFile.Name
Dts.Variables.Item("TotFileName").Value = sFileName
Exit For
Next
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
August 27, 2008 at 3:07 pm
You really don't need a script task for this. You can do this with a ForEach loop container. You set the Enumerater to Foreach File Enumerator, set the Folder, and the files to *.xls. Then in Variable Mappings set a variable to Index 0, which will hold the name of the file. Then within the loop process the file using an Excel connection setting the ExcelFilePath to the variable in the Expressions.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 27, 2008 at 3:44 pm
Thanks a lot!! I did this and put the data flow task inside this foreach loop container. when i execute this container i got error like:
The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0XC0202009.
Error at load into temp table: component "Excel Source" failed validation and returned error code 0XC020801C.
Any idea what went wrong?
Thanks!
September 4, 2008 at 2:52 pm
looks like it doesn't work for Excel Connection Manager. Once I set the property of the Excel Connection Manager to use an expression of connectionstring of the variable, then immediately on the data flow tab inside the excel source box there is a red error sign, if you mouse over it the msg says sth like "cannot acquire connection from connection manager".
when I first set up the Excel Connection Manager I had to put the excel file path there like c:\folder\file1.xls, but after i did the above (added expression from properties screen for the Excel Connection Manager) then when I right click on the Excel Connection Manager and choose edit again the excel file path is empty.
A weird thing I noticed is that if I do not set up the expression (variable as the connection string) then when I execute the foreachloop container it's still going to load every file inside that folder even if in the Excel Connection Manager the excel file path shows c:\folder\file1.xls (in that folder I put file1.xls and file2.xls for testing) and even if the variable mappings index is set to 0.
Is this a bug or not?
you can try it yourself and you'll see what I mean.
I only have one excel file in that folder each time when the package runs so I only need to load one file.
September 5, 2008 at 1:45 am
Hey Jenny,
It definitely works for excel.
I have a for each file enumerator which enumerates and even traverses subdirectories and imports any excel files found (amongst other things). Wrote it quite a while ago, but just pulled it up to see if some guidance could be provided.
Lets start at the beginning:
- Your excel files you are importing will have to have the same layout. I am sure you are aware that SSIS is metadata driven.
- create a variable name. For arguments sake, lets call this variable varMyExcelFileName. Make sure its a string. Why the variable? This will hold the full file name that the enumerator will find
- create a for each loop enumerator. Change the delayvalidation property to false to ensure that it doesnt bomb the first time out. Why delayvalidation? Because the first time the loop enumerates, the excel file connection will be pointing to nothing.
- Specify the folder to enumerate on, and on the files, specify *.xls. In my case I treverse on subfolders
- In the variable mappings, add varMyExcelFileName with an index of 0
- In the connection manager, for your excel connection, add an expression. Use the property ExcelFilePath as opposed to connection string. The connection string will take care of itself as the loop enumerates.
Tada
In my case, I used multiple excel connectionmanagers, each for a different layout, which gave me enormous flexibility.
Good luck!
~PD
September 5, 2008 at 4:41 am
A couple of random thoughts (in case it is not working yet--guesswork follows):
1. In the Foreach Loop Editor/Collection, use "Retrieve file name" = 'Name only".
2. In your script statement" "sFolder = Dts.Variables.Item("ImportFolder").Value", I have noticed that the typical form is ....Value.ToString
(I have not tried it any other way.)
For testing, set the Excel connection manager to a sample file with the same format (Tab name, column names and data) as you expect to find in the actual files. This dummy won't be used when you execute the package.
If the input file tab names are not consistent, you will need to jump through some more hoops.
Good luck.
September 5, 2008 at 8:37 am
Thank you both for the help!!!
I changed the propery from connectionstring to ExcelFilePath and to use Retrieve file name: Name only in Foreach Loop Editor/Collection, but I still got the same error. Don't what I did wrong. Every step I did looks ok, same as what you described. I'm going nuts.
September 5, 2008 at 9:09 am
Welcome to the world of SSIS! Since you are still grasping at straws, I'll throw out a couple of more (still based on my own lack of understanding):
1. Does your path name end with a "\"?
2. I tried (and suspect I failed) to attach a .rtf file with some screen shots of the configuration I got to work. Sort of getting back to copying JCL cards...
(If it didn't attach, and you are desperate to try anything, let me know how to send it to you.)
September 5, 2008 at 9:30 am
Jim, you mean the folder path has \ at the end? yes I have.
I didn't see your .rtf attachement.
you can email to jzhou@majesticresearch.com.
Thanks a lot for your help and patience!!
September 16, 2008 at 9:28 am
Jenny,
I am having the same problem with SSIS. I am trying to load whatever excel files from the folder. The excel file names will be changed all time.Did you find solution for SSIS?
September 16, 2008 at 1:33 pm
Nope, I tried tons of times but it still didn't work, tried Jim's way (he says works for him) still no success; even tried script task using the code (below) on Microsoft site still get error.
i have no idea what went wrong. going nuts.
------------------------------------------------------------------------------------------------------------
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.IO
Imports System.Data
Imports System.Math
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Const file_pattern As String = "*.xls"
Dim excelfolder As String
Dim excelFiles As String()
excelfolder = Dts.Variables("importfolder").Value.ToString
excelFiles = Directory.GetFiles(excelfolder, file_pattern)
Dts.Variables("totfilename").Value = excelFiles
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
September 16, 2008 at 1:48 pm
I do not use script task. I found a workaround that I have to put a dummy excel file which has a header row with no data in the folder. @filename variable, need to put that dummy file path.
September 16, 2008 at 2:07 pm
That might be the solution, let me try that, mine didn't work because my dummy excel file doesn't have a header row. I have to use col1, col2, etc, I guess.
September 17, 2008 at 4:19 am
I think you may be on to the problem. The "dummy" Excel file needs to match (and probably should be) one of the actual input files you expect to process, so the column types can be inspected. Good luck!
September 17, 2008 at 6:45 am
I believe so.I have to find another solution. Thanks.:unsure:
Viewing 15 posts - 1 through 15 (of 53 total)
You must be logged in to reply to this topic. Login to reply