Inserting File Name into SQL Table

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)

Inserting File Name into SQL Table

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-07-08 : 11:30:03

Hi Guys

I have a package that produces an extract with a datestamp at the end, so it looks something like 'Extract_20080708_160714.csv' (yyyymmdd_hhmmss).

The thing I want to do is to be able to insert this filename into a SQL table that I have produced. I am using a variable and an expression on a connection manager to produce the datestamp.

Is this possible to do?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-08 : 13:31:46

have a look at this:-

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

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-07-08 : 14:10:12

Hey

Thanks for that, but I think you are replying my 'File Watcher' question.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-08 : 14:22:42

quote:
Originally posted by rcr69er

Hey

Thanks for that, but I think you are replying my 'File Watcher' question.

sorry for that

. i think you can do something like this:-

http://www.mssqltips.com/tip.asp?tip=1443

once you get filename in a variable use a ExecuteSQLtask to insert it onto a table.

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-07-08 : 14:57:56

Hey

Thanks for that! What sql syntax would you use? I'm not sure when it comes to SSIS variables.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-09 : 00:55:16

quote:
Originally posted by rcr69er

Hey

Thanks for that! What sql syntax would you use? I'm not sure when it comes to SSIS variables.

Thanks

sql syntax? you just need to declare a variable in SSIS package and use a script task as shown in article to get filename onto it. Then use a sql task to insert it to yourtable.

rcr69er
Constraint Violating Yak Guru

327 Posts

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

Hi

Thanks, but how do you pass the variable using a sql task?

Thanks

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-07-09 : 05:22:02

Hi

I managed to get the file name into the table using the variable mapping and the following SQL EXEC dbo.[UpdateExportBatchOrdersTEST] ?

But when I look in the table it has enterd the filename without the datestamp. However the extract file created has the datestamp.

Any ideas???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-09 : 06:46:07

quote:
Originally posted by rcr69er

Hi

I managed to get the file name into the table using the variable mapping and the following SQL EXEC dbo.[UpdateExportBatchOrdersTEST] ?

But when I look in the table it has enterd the filename without the datestamp. However the extract file created has the datestamp.

Any ideas???

what does that sp do?can you post code?

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-07-09 : 07:33:33

The stored procedure updates a table. The syntax is:

CREATE PROCEDURE UpdateExportBatchOrdersTEST (@BatchFileName nvarchar(50))
AS

INSERT INTO dbo.ExportBatchOrdersTEST
(
OrderNumber
,[OrderDate]
,[ProcessDate]
,[Deltype]
,[DelHandle]
,BatchFileName

)
SELECT
fr.OrderNumber
,fr.ORDDATE
,GETDATE()
,del.Deltype
,del.Handle
,@BatchFileName

FROM FullReport AS fr
LEFT JOIN dbo.DeliveryCodes AS del ON fr.[DelMethod] = del.[Deltype]

WHERE fr.SupplierName = (SELECT SupplierName FROM Supplier WHERE ID = 1)
--AND fr.ImportedOn > (SELECT Date FROM [Admin] WHERE AdminID =1)

AND NOT EXISTS
(SELECT
*
FROM ExportBatchOrdersTEST
WHERE ExportBatchOrdersTEST.[OrderNumber] = fr.[OrderNumber])

Hope this helps!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-09 : 11:59:39

quote:
Originally posted by rcr69er

The stored procedure updates a table. The syntax is:

CREATE PROCEDURE UpdateExportBatchOrdersTEST (@BatchFileName nvarchar(50))
AS

INSERT INTO dbo.ExportBatchOrdersTEST
(
OrderNumber
,[OrderDate]
,[ProcessDate]
,[Deltype]
,[DelHandle]
,BatchFileName

)
SELECT
fr.OrderNumber
,fr.ORDDATE
,GETDATE()
,del.Deltype
,del.Handle
,@BatchFileName

FROM FullReport AS fr
LEFT JOIN dbo.DeliveryCodes AS del ON fr.[DelMethod] = del.[Deltype]

WHERE fr.SupplierName = (SELECT SupplierName FROM Supplier WHERE ID = 1)
--AND fr.ImportedOn > (SELECT Date FROM [Admin] WHERE AdminID =1)

AND NOT EXISTS
(SELECT
*
FROM ExportBatchOrdersTEST
WHERE ExportBatchOrdersTEST.[OrderNumber] = fr.[OrderNumber])

Hope this helps!

Does BatchFileName field have sufficient length to hold the timestamp part as well? Also you could try putting a breakpoint just after script task where you get the filename on variable and check the value of variable to see if its getting correct value.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources