SSIS Grab file name and insert into file table

Question

SSIS Grab file name and insert into file table

PSB

SSChampion

Points: 13195
More actions
September 3, 2010 at 7:51 am

#226519

I have to grab each file name in the folder and insert into the Files table . Then I have to grab the file id from the files table and insert into a staging table whose source is an excel sheet .
I am using a for each loop and a script task to get the file name from the variable . But not sure how to dump into the Files table and then send the file_ID to the staging table.
Below is the script of the table:
CREATE TABLE [dbo].[CMD_Files](
[CMD_File_ID] [bigint] IDENTITY(1,1) NOT NULL,
[CMD_File_Name] [varchar](255) NOT NULL,
[CMD_File_Date_Loaded] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[CMD_File_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

PSB SSChampion Points: 13195 More actions · Answer 1

Was able to grab the required info from an execute sql task into the dbo.CMD_Files table with expressions SqlStatementSource --> "INSERT INTO dbo.CMD_Files (CMD_File_Name,CMD_File_Date_Loaded)

SELECT '"+ (DT_WSTR, 200) @[User::FileName] +"' , GETDATE() "

PSB SSChampion Points: 13195 More actions · Answer 2

Not sure how to populate the FileID in the staging table from CMD_Files table .

Please help.

H4K Hall of Fame Points: 3374 More actions · Answer 3

use script task to store the file name in a variable (create an object of type FileInfo). And then, use a derived column to get that variable value into the table.

Thanks & Regards,

Amar Sale

BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI