Hello,
So, today I am struggling to bring to an end a small project I started. I have a stored procedure that reads the contents of a file into a TMP table via BULK INSERT. My statement, which works as expected, is as such:
BULK INSERT #Raw_NIC_Imported_TMP
FROM 'C:\SSIS\import directory\NetworkAdapterConfiguration_10_191_32.csv'
WITH ( KEEPNULLS, FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '"\n' );
In order to handle the many files that will be in that directory with the same file structure, I started to create an SSIS package that consists of a "Foreach Loop Container" with the following specifications:
Folder: C:\SSIS\import directory
Files: *.csv*
Retrieve file name: Fully qualified
Variable: User::CurrentFile
Variable Index: 0
Within the "Foreach Loop Container" I have placed an "Execute SQL Task" configured as such:
Connection Type: OLE DB
SQL Source Type: Direct Input
Parameter Mapping: User::CurrentFile, Input, VARCHAR, 0, -1
I have put a simple select statement in place and the connection to the DB is successful. However, I have tried to no avail, to figure out how to use the Foreach Loop Container variable as a replacement for the hard coded fully qualified file that I am using successfully using inside of the stored procedure. Typically, I can't save the sproc. I seem to be either not declaring something, improperly using the variable or maybe I can't do this which would seem odd.
Would someone be so kind as to comment on what a good solution would be to accomplish this?
EDIT: Sorry, I attempted to post a PNG of the stored procedure and it says that new users are not allowed. Please advise if more info is needed.
Thanks,
Casey