Last Updated
Viewed 323 Times
           

I have a tpcds dsdgen generated data about 100GB, and each dat file is around 20GB+, I have exhaustively searched how to load flat file into sql-server 2016, and i tried importing it using SSIS package and enabled fast parse on all the decimal fields. Still it takes lot of time to load into a table. Its taking 24hrs and continuing and just loaded 9,700,000 records of a table only,

is the above the ideal way, or what could be the problem.

please help me, Iam stuck with this problem and also new to MSSQL world.

New to SSIS and am trying to import a flat file into my DB. There are 6 different rows on the flat file that I need to combine into one row in the database, each of these rows contain a different price for one symbol. For example below:

IGBGK  21 w  47 
IGBGK  21 u  2.9150  
IGBGK  21 h  2.9300    
IGBGK  21 l  2.9050   
IGBGK  22 h  2.9300      
IGBGK  22 l  2.8800 

So each of these are in a different rows on the flat file but will become one row in different columns for symbol IGBGK. I can transform the data to place each number into its own column but can not get them to combine into one row.

Any help on the direction I need to go with this is greatly appreciated.

End product should look like:

Symbol | col 1 | col 2 | col 3 | col 4 | col 5 | col 6
-------+-------+-------+-------+-------+-------+-------    
IGBGK  |  47   | 2.915 | 29.30 | 2.905 | 2.930 | 2.880

I have a csv file which is comma delimited but it has some address field with commas in them and a description field which starts and ends with double quotes and has commas within it. Import wizrad of sql server just adds in extra columns and therefore errors on the data type for other fields which this data is extending into. If I use excel the field is correctly seen and I can therefore import it but I want to automate this import so dont want to have to open each file (of which I have thousands )into in excel.

I have tried powershell

DECLARE @file NVARCHAR(250);
DECLARE @cmd  NVARCHAR(255); 
SET @file='C:\path\filename' 
SET @cmd = 'powershell "Import-csv -path '+@file+'.csv -Delimiter '','' | Export-CSV -path '+@file+'_converted.csv -Delimiter ''|'' -NoType " '
EXEC master.dbo.xp_cmdshell

but get the error

   output
   Import-Csv : A positional parameter cannot be found that accepts argument 
   'Premium'.
   At line:1 char:1
    + Import-csv -path C:\DWfilestoload\Collect Premium Insolvency Output.csv 
     -Delimit ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        ~~~
   + CategoryInfo          : InvalidArgument: (:) [Import-Csv], ParameterBind 
    ingException
   + FullyQualifiedErrorId : PositionalParameterNotFound,Microsoft.PowerShell 
   .Commands.ImportCsvCommand

    NULL

Any other ideas to get this file in a good format for import or to swap the delimiter, I have tried replacing the , for a | but its the same effect.

My goal is to build automatic process, which imports data from multiple xlsx files from certain directory to sql server database in regular phases.

I already created SSIS package, which imports data to sql server database. But everytime if I run this package, all xlsx files data will be inserted, but I would not like to import all data again, I would like to import just those xlsx files again, which have been updated after last import.

Currently my data flow is following:
Excel source > OLE DB Destination which runs inside foreach loop container.

I imagine something like that:
I have to insert last import timestamp with file name into some log table and then have to compare it with the xlsx last modified date. But I do not know how to do it. Hope that someone could provide some step by step instructions.

Similar Question 4 (2 solutions) : Import 50+ tables from MySQL to SQL Server using SSIS

Similar Question 6 (1 solutions) : New to SQL, how can I import multiple .CSV files?

cc