March 25, 2014 at 3:42 am
Hi all,
I need to design a SSIS for uploading between 5-6 flat files each month.
These flat files are not the same format. Like .xls,.txt,.csv....
I can achieve it by creating separate flat file source for each format file. But I want to automate it because there can be more files with different format in the same folder. Please suggest me how to go about it.
March 25, 2014 at 3:52 am
How about using CASE or IF...ELSE to check .txt, .xls etc.
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 25, 2014 at 4:22 am
Could you please elaborate the solution?
March 25, 2014 at 5:05 am
SAMPLE CODE, you can replace ListPrice with various file extention.
---------------
USE AdventureWorks2012;
GO
SELECT ProductNumber, Name, "Price Range" =
CASE
WHEN ListPrice = 0 THEN 'Mfg item - not for resale'
WHEN ListPrice < 50 THEN 'Under $50'
WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'
WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
ELSE 'Over $1000'
END
FROM Production.Product
ORDER BY ProductNumber ;
GO
--------------
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 25, 2014 at 5:35 am
Hi,
I know how a CASE statement works :-), As you said we can recognize the file format by using CASE but what after that in SSIS. Do we need to use Foreach loop? What if the columns are different in Source ? Means if we want to load each file in different table?
March 25, 2014 at 8:03 am
dharmendra.mittal (3/25/2014)
Hi all,I need to design a SSIS for uploading between 5-6 flat files each month.
These flat files are not the same format. Like .xls,.txt,.csv....
I can achieve it by creating separate flat file source for each format file. But I want to automate it because there can be more files with different format in the same folder. Please suggest me how to go about it.
Are you meaning that the same file (same number of columns and column names etc) can be delivered to you as .csv, .xls etc or they are different files different columns etc as well as with differing file extensions (.xls, .csv etc)?
March 25, 2014 at 8:29 am
Just to clarify...
If you are receiving what is essentially the same file but it could be in differing formats (.csv, .txt, .xls etc) then, by far, the easiest thing to do would be to agree with the third party sending you the files on a consistent format to send the files in. This is not unusual and is infact a good practice, if you can follow it.
On the other hand, if you are receiving files that are different (such as different column names, number of columns and so on) and you would like to load those into a table in a database with SSIS, then I think you are almost certainly left with programmitically handling this...
in other words, you would write code against the SSIS API to compensate for the fact that you don't know what the file would look like at design time.
Not clear to me what your situation actually is.
March 25, 2014 at 10:02 am
Similar question from here.
Regulary I want to import Excel files. (Or CSV files).
Extention is known.
Format is not predetermined.
Is there a solution for this ?
A general solution which imports data without a predetermined format ?
Ben
March 25, 2014 at 10:27 pm
Hi Guys,
My concern was mainly to the scenario where Columns are same but files are different format. Sorry If I confused you in my last post.
Please explain me the resolution step by step so that I can give it a try and confirm.
For the answer to the question where columns are not predetermined we can first get the columns names by using script task and then create a temporary table on the fly with the same columns name and then export the records in the temp table. Later we can export the record from Temp table to main table.
March 26, 2014 at 3:28 am
ben.brugman (3/25/2014)
Similar question from here.Regulary I want to import Excel files. (Or CSV files).
Extention is known.
Format is not predetermined.
Is there a solution for this ?
A general solution which imports data without a predetermined format ?
Ben
I believe there is out-of-the-box help with this kind of thing in SSIS for SQL Server 2012 but unfortunately not
SQL Server 2008.
You'll probably end up having to do some kind of scripting with maybe the Script Component or if you'd rather have full control over the Package creation process, including programmatically configuring components, creating destination tables in a target database, mapping columns and such then you can code against the SSIS API in a way similar to how its done in this
March 26, 2014 at 8:15 pm
Option 1
I guess you could design a package with various scenarios i.e input file as excel/csv and union them to a temp table before inserting the records where you want. this way if the file is blank it will not load anything and you will only have one set of records .
option 2
you could set up a variable for the extension of the input file to check which format the data has come through and then execute the load (given the file name remains consistent)
March 27, 2014 at 6:25 am
Hi devilsid,
Can you explain me these 2 options by taking an example :
Option 1
I guess you could design a package with various scenarios i.e input file as excel/csv and union them to a temp table before inserting the records where you want. this way if the file is blank it will not load anything and you will only have one set of records .
option 2
you could set up a variable for the extension of the input file to check which format the data has come through and then execute the load (given the file name remains consistent)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply