Multiple flat file import to corresponding SQL server tables

We are trying to use SSIS Import export wizrd to import the flat files (CSV format) that we have into MS SQL Server 2005 database tables. We have huge number of CSV files. Is there a way by which we can import these flat (CSV) files in to corresponding SQL server tables in a single shot. I would really appreciate this help as it is painful to convert each and every file using the Import Export wizard.

Thank you very much,

Regards,

Srinivas Raveendra

I am also facing the same problem.|||The simplest way I can see that is to create a package per every file-table destination pair. Each package could have a ForEach loop to process all files that have the same structure.|||

The way we have accomplished this task is to have a table that holds the names of all the files and the names of the tables each file is loaded into.

The SSIS package reads all the rows from this table and runs a foreach loop container for each record; The looping process firstly truncates the staging table and then bulk inserts based on the variable values for each iteration of the loop container. the syntax for the Bulk Insert would look something like this:

EXEC('BULK INSERT ' + @.strTable + ' FROM ' + @.strFileDir + @.strFile + '.csv''')

If the files you are importing are not exactly the same format as the tables you are importing them into. ie the columns are in a different order or there are more/less columns in the file than in the table, you will need to have format files set up and the syntax with the format file would look something like this:

EXEC('BULK INSERT ' + @.strTable + ' FROM ' + @.strFileDir + @.strFile + '.csv'' WITH (FORMATFILE=''' + @.strFormatDir + @.strFormat + '.fmt'')')

As a note we have the variables @.strFileDir and @.strFormatDir set at the package level, and dynamically controlled through a package configuration.

For more information on Bulk Insert see Books Online: http://msdn2.microsoft.com/en-us/library/ms188365.aspx

Hopefully this helps.

sql

Multiple Columns in report Matrix?!

Hi there,

I'm trying to use a matrix to create report with the following format.

Location Carrier Period Total Total YTD

C1 C2 C3

Bangkok 1 2 3 6 5

CLT 1 1 1 3 5

Totals 9 10

I'm having trouble designing my report using a matrix. Any ideas how to add "total ytd" column and the totals below??

Thanks in advance,

Elias

Which version of SQL Server are you using?

With SQL2005 there is a PIVOT command that would allow you to produce a more conventional shape of report and then flip it.

|||

I'm using SQL reporting 2005. Could you please walk me through how to do that? Many thanks.

|||

Have you just got SQL2005 Reporting or have you got a backend SQL2005 database?

|||

Hello I had the same problem. I just adjusted the body to allow 2 text fields at the top of the colum and put my Sum expression in each of them for those colums. It gives the totals at the top but that can be a good thing if some one is looking for the totals and doesn't want to scroll to the bottom of your report.

|||

using both SSRS and SQL Server 2005

|||

Could you please give more details on how to do this? Still no able to solve my problem. Thanks

|||

The solution to creating the required matrix, will require some different thinking.

Location Carrier Period Total
C1 C2 C3 Total YTD
Bangkok 1 2 3 6 5
CLT 1 1 1 3 5
Totals 9 10

Is the number of carriers fixed? If yes then the problem simplifies enormously, as there besides using a dynamic temporary table (dynamic in the sense that when an extra carrier appears an additional column will be created), a new table can be created as an intermediate in the analysis. This can be either a table within the database or an ordinary temporary table. The SELECT to read this can include a UNION to a SUM on PeriodTotal and TotalYTD as in
SELECT 'A', Location, CONVERT(VARCHAR(10), C1) AS C1,
CONVERT(VARCHAR(10), C2) AS C2,
CONVERT(VARCHAR(10), C3) AS C3, PeriodTotal, TotalYTD FROM Fred
UNION
SELECT 'B', '' AS LOCATION, '' AS C1, '' AS C2, 'Totals' AS C3, SUM(PeriodTotal), SUM(TotalYTD) FROM FRED
ORDER BY 1, 2

|||

Neither the location nor carrier are fixed. Thanks!

|||

>Neither the location nor carrier are fixed.

Since their numbers are not fixed. you will need to adopt an array of arrays approach to accumulate the data and then generate the HTML yourself. The generation of the HTML sounds complicated, but once you make a mockup of what you want to generate, it becomes quite simple.