Zack
asked on
BULK INSERT most recent CSV
Heyas,
How would I get a BULK INSERT statement to only insert the CSV with most recent file creation date?
Any assistance is welcome.
Thank you
How would I get a BULK INSERT statement to only insert the CSV with most recent file creation date?
Any assistance is welcome.
Thank you
ASKER
Hi Vitor,
I just adjusted the PowerShell script that generates the file, it now has the creation date in the format is the following: AllMessages_MO_raw_2016100 1_20161031 .csv
Thank you.
I just adjusted the PowerShell script that generates the file, it now has the creation date in the format is the following: AllMessages_MO_raw_2016100
Thank you.
you probably need to think for a proper ETL (Extraction, Transformation, Load) approach and build your logic accordingly.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Pawan,
Never created an SSIS package before give my some time to 'Google it' and I will let you know if I need any assistance.
Any tutorials you can recommend?
Thank you.
Never created an SSIS package before give my some time to 'Google it' and I will let you know if I need any assistance.
Any tutorials you can recommend?
Thank you.
Hi,
Pls refer this MSDN URL - https://msdn.microsoft.com/en-us/library/ms135937.aspx
and inside the script task use below and check if you are getting the last file name or not.
Other steps will take later.
Pls refer this MSDN URL - https://msdn.microsoft.com/en-us/library/ms135937.aspx
and inside the script task use below and check if you are getting the last file name or not.
var YourdirectoryName = new DirectoryInfo(@"E:\Pawan\SSIS\");
var YourFileName = YourdirectoryName.GetFiles()
.OrderByDescending(k => k.LastWriteTime)
.First();
Other steps will take later.
ASKER
Hi Pawan,
That link is https://msdn.microsoft.com/en-us/library/ms135937.aspx is Active Directory how does that relate?
Thank you.
That link is https://msdn.microsoft.com/en-us/library/ms135937.aspx is Active Directory how does that relate?
Thank you.
No leave that ... Just add a script task using the steps..
You have to use the code that I gave you. In their example they have used active directory.
You have to use the code that I gave you. In their example they have used active directory.
ASKER
Heyas,
Found this article much better for a tutorial
http://blog.sqlauthority.com/2015/01/29/sql-server-the-basics-of-the-ssis-script-task-notes-from-the-field-065/
Following now.
Found this article much better for a tutorial
http://blog.sqlauthority.com/2015/01/29/sql-server-the-basics-of-the-ssis-script-task-notes-from-the-field-065/
Following now.
Great , please follow !!
ASKER
Hi Pawan,
Keep getting a compile on the script you gave me.
How do I proceed?
Thank you.
Compile-Error-on-Build.PNG
Keep getting a compile on the script you gave me.
How do I proceed?
Thank you.
Compile-Error-on-Build.PNG
Add this namespace in your code..
using System.IO;
using System.IO;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Add this also
using System.Linq;
using System.Linq;
ASKER
Hi Victor,
Using the following code:
I get the following error message:
(1 row(s) affected)
Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file "AllMessages_MO_raw_201610 01_2016103 1.csv" does not exist.
Using the following code:
DECLARE @FileName AS NVARCHAR(128)
DECLARE @MaxDate AS VARCHAR(15)
DECLARE @BulkCmd varchar(1000);
CREATE TABLE #DirectoryTree (
subdirectory nvarchar(512),
depth int,
isfile bit);
INSERT INTO #DirectoryTree
EXEC master..xp_dirtree 'D:\Test', 1, 1
SELECT TOP 1 @FileName = subdirectory, @MaxDate=MAX(RIGHT(subdirectory, 12))
FROM #DirectoryTree
WHERE isfile=1 AND subdirectory LIKE '%.csv'
GROUP BY subdirectory
ORDER BY MAX(RIGHT(subdirectory, 12))
DROP TABLE #DirectoryTree
USE MedicalObjectsReporting
SET @BulkCmd = 'BULK INSERT MOMSGS
FROM ''' + @FileName + '''
WITH (ROWTERMINATOR = ''\n'')'
EXEC(@BulkCmd);
I get the following error message:
(1 row(s) affected)
Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file "AllMessages_MO_raw_201610
Sorry, couldn't test it.
You need to add the correct path. Below example with a fake path (C:\MyFiles\):
You need to add the correct path. Below example with a fake path (C:\MyFiles\):
SET @BulkCmd = 'BULK INSERT TableName
FROM ''C:\MyFiles\' + @FileName + '''
WITH (ROWTERMINATOR = ''\n'')'
ASKER
Hi Victor,
That worked below was the final script:
Thank you.
That worked below was the final script:
DECLARE @FileName AS NVARCHAR(128)
DECLARE @MaxDate AS VARCHAR(15)
DECLARE @BulkCmd varchar(1000);
CREATE TABLE #DirectoryTree (
subdirectory nvarchar(512),
depth int,
isfile bit);
INSERT INTO #DirectoryTree
EXEC master..xp_dirtree 'D:\Test', 1, 1
SELECT TOP 1 @FileName = subdirectory, @MaxDate=MAX(RIGHT(subdirectory, 12))
FROM #DirectoryTree
WHERE isfile=1 AND subdirectory LIKE '%.csv'
GROUP BY subdirectory
ORDER BY MAX(RIGHT(subdirectory, 12))
DROP TABLE #DirectoryTree
USE MedicalObjectsReporting
SET @BulkCmd = 'BULK INSERT MOMSGS
FROM ''D:\test\' + @FileName + '''
WITH ( FIRSTROW=2,
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''\n'')'
EXEC(@BulkCmd);
Thank you.
ASKER
Thank you both for your assistance on this matter.
I will have to do some research on SSIS later. I have never used SSIS, so I didn't know running scripts via an SSIS package would be so seamless according to the tutorials I have been reading. Thank you Pawan for pointing this out to me.
I will have to do some research on SSIS later. I have never used SSIS, so I didn't know running scripts via an SSIS package would be so seamless according to the tutorials I have been reading. Thank you Pawan for pointing this out to me.
I can see this being done with CLR or xp_cmdshell but with the latest one you'll need to enable it since isn't enabled by default for security reasons.
If the creation date is part of the file name maybe I can find a 3rd way to do this.