I briefly looked at the URL you gave. You need to change code in the "Begin
Loop" code. You need to include an additional line of code to point to the
second worksheet. In other words, you need two lines that are similar to the
one reading:
conTextFile.DataSource = .......
There may be a couple of other things you need to change. But the main thing
is that in your case, you have two transforms for each file.
You can find out the exact properties you should be editing by doing a
Disconnected edit on the package, looking at the properties in the package
hierarchy, and writing the appropriate expressions.
Cheers,
Charles Kangai, MCT, MCDBA
Author of Learning Tree's 4-day course: "SQL Server 2005 Integration
Services"
http://www.learningtree.com/courses/134.htm Author of Learning Tree's 4-day course: "SQL Server Reporting Services"
http://www.learningtree.com/courses/523.htm [quoted text, click to view] "Conan Kelly" wrote:
> Hello all,
>
> A while back I downloaded the sample Loop-Import-Archive package from
www.SQLDTS.com and have put it to good use.
>
> The problem I'm running into now is that instead of importing from a flat text file, I'm trying to modify it to import form MS
> Excel. I need to import 2 different sheets in each Excel file. I replace the Source Text File connection with an Excel 97-2000
> connection and I have to 2 Transform Data tasks running from it to my SQL Server destination connection. The problem is that when I
> run the package, it is only running one of the Transform Data tasks, then archives the file, then loops to the next file. In the
> "Executing DTS Package" dialog box, it looks like the second Transform Data task is at the bottom of the list of steps, outside of
> the loop.
>
> Is there any way to adjust this package so both Transform Data tasks will run inside the loop importing both worksheets from each
> file?
>
> --
> Thanks for any help anyone can provide,
>
> Conan Kelly
>
>
