February 27, 2018 at 9:23 pm
This is an oversimplified example of what I’m trying to accomplish. If I can get this working I can make it all work. I have about 70 Excel files and 10 CSV files that I load. 90% of these are monthly but a few are daily or weekly. I copy/paste the import code with whatever other SQL I’m using to clean up or manipulate the data. I’m using SQL Server v17.5,
I would like to move this into SSIS. For several reasons I’d like to continue to use the link server code (I realize SSIS could import data and will probably use it on some). For this discussion assume I’m using link server code.
Questions:
(1) In SSIS, look at where I’m going to have the file name (in DECLARE statement). How can I make the date a wild card for this?
(2) How do I move a successful processed file to the processed directory?
(3) How do I move a failed processed file to the failed directory? Thank you !!!
Assume two databases:
(1) MyImportDatabase
a. SSIS packages here
b. Procedures to “clean up” data here
(2) MyProductionDatabase
a. Once data cleaned, stored here in a table
The idea is this:
(1) Drop the file in the directory C:\ImportData\
(2) Execute the SSIS package for the respective file (manual for now)
(3) If the process is successful, the file should be moved to C:\ImportData\Processed\
(4) If the process is not successful, the file should be moved to C:\ImportData\Failed\
SSIS:
(1) File will be named distinctly with a date in the name
a. MyFileName_YYYYMMDD.xlsx
(2) Once the link server code grabs the data it will be held in a #temp file (or a “tmp” table in the he MyImportDatabase)
(3) Every SSIS package after executing the link server code will EXEC a stored procedure in the MyImportDatabase that “cleans up” and manipulates the temp file
(4) Once clean the procedure code moves the data to the MyProductionDatabase
(5) Truncate or drop temp tables
(6) Drop the link server
Sample Files (to import):
LinkServerTestFileCsv_20180227.csv
LinkServerTestFileExcel_20180227.xlsx
Import CSV (could not upload file, any csv file will work):
DECLARE @ImportFilename varchar(100) = ' LinkServerTestFileCsv_20180227.csv'
IF OBJECT_ID('tempdb..##Final') IS NOT NULL DROP TABLE ##Final
DECLARE @Sql nVARCHAR(MAX) = 'SELECT * , IDENTITY (INT,1,1) as RecordId
INTO ##Final FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Text;Database=c:\ImportData\;'',
''SELECT * FROM ' + @ImportFilename + ''')'
EXEC (@Sql)
SELECT * FROM ##Final
Import Excel (sample file uploaded):
DECLARE @FileSource varchar(100) = ' LinkServerTestFileExcel_20180227.xlsx'
TRUNCATE TABLE ParserTools..FileSource
INSERT INTO ParserTools..FileSource SELECT @FileSource
IF EXISTS(SELECT * FROM sys.servers WHERE name = N'StdLink')
EXEC sp_dropserver
@server = N'StdLink',
@droplogins = 'droplogins'
DECLARE @SQLStatement nvarchar(MAX)
SELECT @SQLStatement = 'EXEC sp_addlinkedserver
@server = N''StdLink'',
@srvproduct=N''ExcelData'',
@provider=N''Microsoft.ACE.OLEDB.12.0'',
@datasrc=N''C:\ImportData\' + @FileSource + ''',
@provstr=N''EXCEL 12.0''; '
EXEC (@SQLStatement)
--PRINT (@SQLStatement)
DECLARE @FileSource1 varchar(100)
SELECT @FileSource1 = FileSource FROM ParserTools..FileSource
IF OBJECT_ID('tempdb..#StdFile1') IS NOT NULL DROP TABLE #StdFile1
SELECT *, @FileSource1 as FileSource
INTO #StdFile1
FROM OPENQUERY (StdLink, 'Select * from [Sheet1$]')
SELECT * FROM #StdFile1
Link Server Requirements (in case anyone wants to use link server in SQL 2016 this may help):
(1) Run SSIS as administrator (anytime I import; tries making user an owner but wasn’t able to import; DBA could probably get permissions right)
(2) Needed to install this AccessRuntime_X64.exe
a. https://www.microsoft.com/en-us/download/details.aspx?id=10910
(3) sp_configure 'show advanced options', 1
(4) sp_configure 'Ad Hoc Distributed Queries',1
March 1, 2018 at 12:42 pm
If you can write either VB or C# code, SSIS allows you to use a Script Task to execute such code, where you could create a filename in that code, and populate a package variable, which can then be used as the filename for various other tasks, and including a connection manager for a flat file.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply