Stored procedures

SSIS and Import/Export
1

Hi,
I just wanted to know if you can import various files with the same layout in to a database using stored procedure if the file name changes all the time?

2

Sorry should have been more precise, I want to automate download from a ftp when files are uploaded SQL automatically downloads in to database but I was told you cannot have different views file names for the stored procedure to work unless you use a generic name

3

Use a bcp format file:
https://docs.microsoft.com/en-us/sql/relational-databases/import-export/xml-format-files-sql-server

1 Like
4

To use a stored procedure and BCP you would need to build the procedure with dynamic SQL - and you would need to poll the directory for the specific file names.

You could also use SSIS which has built-in capabilities to get a list of files from a directory and process them.

5

Yes you can, If that's all you really want to know. BTW a BCP format file may be used with OpenRowset like ths:

SELECT     
    IDENTIFIER = Substring(DATA, 120,10)
  , LST_UPD_ID = CASE WHEN Substring(DATA, 131,3) = Replicate(' ', 3) THEN NULL ELSE Substring(DATA, 131,3) END
  , LST_UPD_TS = CASE WHEN Substring(DATA, 135, 19) = Replicate(' ', 19) THEN NULL ELSE Replace(Stuff(Substring(DATA, 135, 19), 11, 1, ' '), '.', ':') END 
  , SPEDUSVCCD = CASE WHEN Substring(DATA, 162, 1) = ' ' THEN NULL ELSE Substring(DATA, 162, 1) END                      
  , SNGLPRNTCD = CASE WHEN Substring(DATA, 164, 1) = ' ' THEN NULL ELSE Substring(DATA, 164, 1) END                        
  , ADPRNT1REL = CASE WHEN Substring(DATA, 166, 1) = '?' THEN NULL ELSE Cast(Substring(DATA, 167, 6) AS int) END 
  , ADPRNT1DSC = CASE WHEN Substring(DATA, 174, 1) = '?' THEN NULL ELSE Substring(DATA, 175, 254) END
  , ADPRNT2REL = CASE WHEN Substring(DATA, 430, 1) = '?' THEN NULL ELSE Cast(Substring(DATA, 431, 6) AS int) END                              
  , ADPRNT2DSC = CASE WHEN Substring(DATA, 438, 1) = '?' THEN NULL ELSE Substring(DATA, 439, 254) END
  , OTBRTHSIBS = CASE WHEN Substring(DATA, 694, 1) = '?' THEN NULL ELSE Cast(Substring(DATA, 695, 6) AS tinyint) END                          
  , OTBRTHSIBD = CASE WHEN Substring(DATA, 702, 1) = '?' THEN NULL ELSE Substring(DATA, 703, 254) END                             
  , OTNONSIBSN = CASE WHEN Substring(DATA, 958, 1) = '?' THEN NULL ELSE Cast(Substring(DATA, 959, 6) AS int) END                             
  , OTNONSIBSD = CASE WHEN Substring(DATA, 966, 1) = '?' THEN NULL ELSE Substring(DATA, 967, 254) END                             
  , PREVADCLDN = CASE WHEN Substring(DATA, 1222, 1) = '?' THEN NULL ELSE Cast(Substring(DATA, 1223, 6) AS int) END                             
  , BRTHCLDEPN = CASE WHEN Substring(DATA, 1230, 1) = '?' THEN NULL ELSE Cast(Substring(DATA, 1231, 6) AS int) END                            
  , FOSCIF_NUM = CASE WHEN Substring(DATA, 1238, 1) = '?' THEN NULL ELSE Cast(Substring(DATA, 1239, 6) AS int) END
  , WRDGRDCSNO = CASE WHEN Substring(DATA, 1246, 1) = '?' THEN NULL ELSE Cast(Substring(DATA, 1247, 6) AS int) END                             
  , OTHCDFMYNO = CASE WHEN Substring(DATA, 1254, 1) = '?' THEN NULL ELSE Cast(Substring(DATA, 1255, 6) AS int) END                             
  , TOTMINCHLD = CASE WHEN Substring(DATA, 1262, 1) = '?' THEN NULL ELSE Cast(Substring(DATA, 1263, 6) AS int) END
  , OTHSIB_NO  = CASE WHEN Substring(DATA, 1270, 1) = '?' THEN NULL ELSE Cast(Substring(DATA, 1271, 6) AS int) END
  , OTHSIBAPNO = CASE WHEN Substring(DATA, 1278, 1) = '?' THEN NULL ELSE Cast(Substring(DATA, 1279, 6) AS int) END
  , OTHSIBPVAN = CASE WHEN Substring(DATA, 1286, 1) = '?' THEN NULL ELSE Cast(Substring(DATA, 1287, 6) AS int) END
  , OTHSIBGRDN = CASE WHEN Substring(DATA, 1294, 1) = '?' THEN NULL ELSE Cast(Substring(DATA, 1295, 6) AS int) END
  , OTHSIBLTFC = CASE WHEN Substring(DATA, 1302, 1) = '?' THEN NULL ELSE Cast(Substring(DATA, 1303, 6) AS int) END
  , OTHSIBFCEX = CASE WHEN Substring(DATA, 1310, 1) = '?' THEN NULL ELSE Cast(Substring(DATA, 1311, 6) AS int) END
  , OTHSBFCRFY = CASE WHEN Substring(DATA, 1318, 1) = '?' THEN NULL ELSE Cast(Substring(DATA, 1319, 6) AS int) END
  , OHSBUCTNPL = CASE WHEN Substring(DATA, 1326, 1) = '?' THEN NULL ELSE Cast(Substring(DATA, 1327, 6) AS int) END
  , ADPTPRNTSN = CASE WHEN Substring(DATA, 1334, 1) = '?' THEN NULL ELSE Cast(Substring(DATA, 1335, 6) AS int) END
  , ADLTCLDAPN = CASE WHEN Substring(DATA, 1342, 1) = '?' THEN NULL ELSE Cast(Substring(DATA, 1343, 6) AS int) END
  , PNTADPTPNT = CASE WHEN Substring(DATA, 1350, 1) = '?' THEN NULL ELSE Cast(Substring(DATA, 1351, 6) AS int) END
  , OTADLADPNT = CASE WHEN Substring(DATA, 1358, 1) = '?' THEN NULL ELSE Cast(Substring(DATA, 1359, 6) AS int) END
  , URADADPTNO = CASE WHEN Substring(DATA, 1366, 1) = '?' THEN NULL ELSE Cast(Substring(DATA, 1367, 6) AS int) END
  , WHADPTPNTN = CASE WHEN Substring(DATA, 1374, 1) = '?' THEN NULL ELSE Cast(Substring(DATA, 1375, 6) AS int) END
  , TOTADADPHM = CASE WHEN Substring(DATA, 1382, 1) = '?' THEN NULL ELSE Cast(Substring(DATA, 1383, 6) AS int) END
  , FKADPLC_T  = CASE WHEN Substring(DATA, 1390, 10) = Replicate(' ', 10) THEN NULL ELSE Substring(DATA, 1390, 10) END
  FROM OpenRowset(BULK 
     '\\adm-12-etl1\d$\CWSDW\sqlload\data\A_AD42RT.DAT'
   , FORMATFILE='\\adm-12-dw01\o$\Format\A_AD42RT_FMT.xml')   A_AD42RT

and the format file for this:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1"  xsi:type="CharFixed" LENGTH="1402"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1"  NAME="DATA" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>
6

Many thanks guys, will have a read up on the article and will come back to you should I have any further questions