SQLTeam.com | Weblogs | Forums

Foreach Variable Passed to SQL Execute Task using Bulk Insert


#1

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


#2

SOLUTION:

Sorry, I have identified the solution to my problem. I was able to get around the issue by doing the following:

DECLARE @SQLQuery AS VARCHAR(MAX)

SET @SQLQuery = 'BULK INSERT #Raw_NIC_Imported_TMP FROM '''+@MyString+''' WITH ( KEEPNULLS, FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''"\n'' );'

EXEC (@SQLQuery)