SSIS Newbie (link server; file name date changes)

  • texpic


    Points: 5882

    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.


    (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\

    (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):


    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'',  
    @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
    (3) sp_configure 'show advanced options', 1
    (4) sp_configure 'Ad Hoc Distributed Queries',1

  • sgmunson

    SSC Guru

    Points: 110459

    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.

    ‌(aka sgmunson)
    ‌:) 🙂 🙂
    Health & Nutrition

Viewing 2 posts - 1 through 2 (of 2 total)

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