get filenames to a table.. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

get filenames to a table..

Author

Topic

vamsimahi
Starting Member

29 Posts

Posted - 2008-09-15 : 00:39:00

I have a folder which consists of few files. I want to get the Filenames into a temporary table in Temp DB.

I created a package which consists of:
1) a sql task which creates a table in Temp DB
2) a foreach loop container
3) a sql task in foreach loop to insert the filenames in the TempTable. but I'm getting all Null values...

can someone help me with how to create the package and use the variables.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 01:13:37

Have you created a variable in package to hold the filename each time?You need a variable to get filename during each iteration of for each loop. the below article shows how you will get filenames inside the for each loop.You just need to change second part with sql task to insert the variable value to your table.

http://www.sqlis.com/55.aspx

vamsimahi
Starting Member

29 Posts

Posted - 2008-09-15 : 01:54:04

I created a variable of string type and package scope. And mapping that variable in variable mapping with index 0 in Foreach loop.

Now I created SQL task inside the Foreach loop to insert the Filenames into the TempTable. The loop is running through all the files but returning NULL values instead of file names.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 01:57:51

quote:
Originally posted by vamsimahi

I created a variable of string type and package scope. And mapping that variable in variable mapping with index 0 in Foreach loop.

Now I created SQL task inside the Foreach loop to insert the Filenames into the TempTable. The loop is running through all the files but returning NULL values instead of file names.

Can you put a break point inside the for each loop step where you assign variable value to table and check the variable value to understand if its correctly getting the value?

vamsimahi
Starting Member

29 Posts

Posted - 2008-09-15 : 02:04:32

In the SQL task for inserting filenames into TempTable, I am using following SQL script

DECLARE @FilesName varchar(128);
insert into tmpTFileList (FileName) values(@FilesName)

In parameter mapping, I am mapping my variable to the parameter @FilesName (Direction Input).

using ResultSet 'None' in SQL Task

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 02:15:35

you need to put ?s for parameter holders and then map them in parameter mapping tab. you just need below as statement

insert into tmpTFileList (FileName) values (?)

favaz.farook@gmail.com
Starting Member

8 Posts

Posted - 2008-09-15 : 09:34:12

Inside the loop add a Execute SQL Task
then right click go to edit set SQLStatement as

INSERT INTO [tblnames]([filename]) VALUES (?)

then go to Parameter Mapping then select your variable name the with proper varchar type give the parameter a name (if needed)

the For each is for File Enumerator in that select Name Only

then run thats it

vamsimahi
Starting Member

29 Posts

Posted - 2008-09-15 : 10:34:23

In the ForEach Loop:
I gave the folder path which consists of the files, Files as *.csv and file name as 'Name Only' option. In variable mappings I gave the variable with index 0.

In the Execute SQL Task within the ForEach Loop:
I gave Result set as 'none'
SQL statement: INSERT INTO [TFileList]([FileName]) VALUES(?)
Parameter mapping:
variablename: variable I created for package scope, Direction: Input, Datatype: varchar, parametername: ?

when i run the package I get the following error:

[Execute SQL Task] Error: Executing the query "INSERT INTO [TFileList]([FileName]) VALUES(?)" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Also when I parse the query below query, i get a error message
INSERT INTO [TFileList]([FileName]) VALUES(?)

The query failed to parse. Parameter information cannot be derived from SQL statement. Set parameter information before preparing command.

vamsimahi
Starting Member

29 Posts

Posted - 2008-09-15 : 11:00:06

Finally It worked...

used this link for reference:
http://forums.microsoft.com/msdn/ShowPost.aspx?siteid=1&PostID=57637

I gave the paramaeter name as 0 and it worked..

Connection type Parameter Marker parameterName

OLEDB ? 0
ADO.net @varname @parname
ADO ? @parname
ODBC ? 1

thanks for the link..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources