0

Issue:

My excel connection manager filepath is not being updated by the updated variable being passed in via a Foreach file enumerator.

Functional Requirements:

I have a set of excel files inside a directory that I need to iterate through and import into a SQL Server database.

The first restriction with these files is that one of the columns has up to 3000 characters in each of its cells (blowing out the standard 255 char max).

The second restriction is that I've got a restriction here where I can't take the normal path and update the registry to sample more rows (whereby I would normally create a primer file with the first couple of rows of that column having 3000 chars).

Solution Outline:

Prototype: To counter the >255 char cells and prohibition of editing the registry I elected to use a Script Task which leverages an Excel Connection. Reference:
https://egilhansen.com/2015/07/28/ssis-solution-excel-data-source-text-truncation-column-more-than-255-characters/

I have a single DataFlow task importing one file using this script working, so the script is sound.

FYI, the dataflow task has only 3 dataflow items in it: 1) Script task to ingest the contents of the excel file 2) A derived column that adds in the enumerated filstring 3) an ole db that loads these into a database

All Files in a Folder Solution:

I then set up a Foreach Loop container and copied the script task into it.

I configured the properties of the Excel connection's Expression to have a property of filepath and mapped the filename variable to it that the foreach file enumerator is mapped to.

Lastly, I set the Delayvalidation on the control of flow task, the excel connection and the Excel connection manager's properties.

sidenote: In the Foreach File enumerator the derived column is actually adding in the enumerated filenames correctly --> its just the excel connection string that isn't also updating.

Closing comments: I messed around changing the excel file expression to be a ConnetionString and added in another variable and script task to change it inside of the foreach loop (leveraging the enumerated file variable), but the ConnetionString variable isn't updating...... I'm now wondering if I need to nest a foreach loop container inside the existing foreach loop to update the excel connection strings variable before then proceeding to the main script

Minniman
  • 15
  • 3
  • I've also looked at a bunch of how-to instruction blogs on how to use a foreach file loop over a folder of excel files: – Minniman Oct 13 '17 at 12:27
  • eg https://mikedavissql.com/2013/09/16/loop-through-excel-files-in-ssis/ – Minniman Oct 13 '17 at 12:27
  • The difference is that I'm using a script task that connects to an excel connection manager connection rather than an Excel Dataflow source – Minniman Oct 13 '17 at 12:29
  • FYI my connection string is an ACE OLEDB Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\DIR_Aug_2017\FinalDataSet\aaaDemo.xlsx;Extended Properties="Excel 12.0 XML;HDR=YES"; – Minniman Oct 13 '17 at 12:30
  • I have also configured the package with Run64BitRuntime as FALSE – Minniman Oct 13 '17 at 12:31

1 Answers1

0

I do not have the reputation yet to comment and I apologize. I see in your last comment you stated that the connectionstring isn't updating. That is an issue I have experienced with SSIS before where it won't assign the value of a variable inside of the component. Meaning if your inside the loop it won't assign the value until it completes the loop. This applies to Data Flows as well. The way around it is to have that value set prior like what you suggested with the nested loop. If you don't mind we can use this as the comment essentially to help you with your resolution.

Kyle Pearson
  • 107
  • 8
  • Thanks Kyle, yes a agree that reframing the question to be "How do I configure a nested loop inside of my original loop to pass the correctly enumerated file and fliepath to – Minniman Oct 13 '17 at 19:11
  • also update the connection string variable as well as execute the Script Task components, both with the same variable" – Minniman Oct 13 '17 at 19:13
  • For anyone following this thread, I've found another thread which deals with issue that I'm seeing above using a nested foreach loop. It's thread reference is at https://stackoverflow.com/questions/7411741/how-to-loop-through-excel-files-and-load-them-into-a-database-using-ssis-package . I've been able to get this working incorporating my previous c# script object, which now overcomes the 255 char limitation – Minniman Oct 13 '17 at 20:59