May 22, 2011 at 10:15 pm
How to do this task.
To load a flat file into database on incremental basis.
Source Flat File:
File path: C:/everydayhealth/Email.txt
File Name: Email.txt
Columns: Email, Name, City
You can create your own target table.
Archive Details:
File Path: C:/everydayhealth/Archive/Email_YYYYMMDD.txt
Log Tables:
Table Name: tbLoadLog
Table Structure:
LoadLogID int identity(1,1),
FileName varchar(100),
RecordCount int,
LoadDate datetime,
Success bit
•We need a SSIS solution to load the flat file on daily basis into a target table.
Either a package or a Flow diagram with Control Flow and Data Flow with specific task names is fine.
•We would like to log the details of the load as and when we load the file into the database.
Note that we need to log details like FileName, RecordCount and Job Result (Success/Failure)
•We would like to archive the file into the given archive folder for each load. Archived file name should have the date (on which it is loaded in YYYYMMDD format) concatenated to the filename
May 22, 2011 at 11:35 pm
Check your favorite search provider for the following keywords:
* flat file connection manager
* flat file source
* OLE DB Destination with Fast Load
* For Each Loop
* Filesystem Task
* SSIS Logging
* SSIS custom logging
* Rowcount transform
* SSIS event handlers
Also look for SSIS tutorials. There should be one around about importing flat files.
Try it out, and if you're stuck somewhere, let us know.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 23, 2011 at 9:22 pm
I tried but not getting with is log table info. and how to change the column name.
if possible can you provide the process and which transformation i have to use.
May 23, 2011 at 11:28 pm
Logging in SSIS --> right-click on control flow on choose the conveniently named "Logging"
Changing column name --> you can do this in the Flat File Source
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 24, 2011 at 1:44 am
First You can Create a data flow Task.(DFT)
In DFT Add task "Flat File Source" and point it to your input text file.
After that Add a task "Row Count" . Join "Flat File Source" To task "Row Count". To Capture the no of records in a variable use the variable in "Row Count" task.
Add a "OleDB Destination" task to the DFt and join the output of "Row Count" task to "Ole DB Destination". In Ole DB destination select the server and teh target table in which you wish to put the data from input text file. You have to do the Field mapping (from source to destination table)too while setting the properties of OLE DB destination.
Once this is Done you can archive the text file in the folder in which you wish to save it by writing script using "Script Task" in the Control Flow. After this is done you can do logging by clicking on menu SSIS-> Logging and enter the necessary setting to it to log to SQL server or text file etc.
May 24, 2011 at 2:14 am
sanvekar (5/24/2011)
First You can Create a data flow Task.(DFT)In DFT Add task "Flat File Source" and point it to your input text file.
After that Add a task "Row Count" . Join "Flat File Source" To task "Row Count". To Capture the no of records in a variable use the variable in "Row Count" task.
Add a "OleDB Destination" task to the DFt and join the output of "Row Count" task to "Ole DB Destination". In Ole DB destination select the server and teh target table in which you wish to put the data from input text file. You have to do the Field mapping (from source to destination table)too while setting the properties of OLE DB destination.
Once this is Done you can archive the text file in the folder in which you wish to save it by writing script using "Script Task" in the Control Flow. After this is done you can do logging by clicking on menu SSIS-> Logging and enter the necessary setting to it to log to SQL server or text file etc.
How to 'do' SSIS in one easy lesson - nice summary 🙂
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 24, 2011 at 8:02 am
Any one provide Step by Step information.
that will be great help.
May 24, 2011 at 8:11 am
vax09 (5/24/2011)
Any one provide Step by Step information.that will be great help.
Please do some Internet searches to get this basic information and then come back here with any specific questions. This will prove that you have at least tried to do this yourself.
Alternatively, get yourself some training booked in. Those people will be paid to help you - we are not.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 24, 2011 at 12:03 pm
Phil Parkin (5/24/2011)
Those people will be paid to help you - we are not.
You're not? Didn't you get the memo from Steve Jones? 😛 :hehe:
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 24, 2011 at 12:19 pm
Please Provide me Step by Step Information because i am new in it.
and it is interview screening question.
May 24, 2011 at 12:53 pm
vax09 (5/24/2011)
Please Provide me Step by Step Information because i am new in it.and it is interview screening question.
You must be joking. Not a chance.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 25, 2011 at 7:56 am
I'm sorry dude but you are going to have to do the digging for yourself. Koen Verbeeck has already pointed you on the right direction. In fact you would do much better at your interview if you actually took some time to take his advise instead of parrotting the solution.
😛
May 25, 2011 at 8:18 am
Dude,
Everyone wants to make more money and getting a job in technology certainly can help. But to be blunt, near as I can see you aren't qualified, by a lot, and the lack of desire you have shown to even try to do the research further indicates that you likely SHOULDN'T be working in this field.
As a community we will help people solve their database problems, BUT we aren't going to help unqualified people get jobs they shouldn't have. Do some research, get some experience, and then maybe you will be ready. We already have too many people who bluffed there way into positions that they couldn't handle, and fake it till you make it only goes so far..
CEWII
May 26, 2011 at 4:45 pm
vax09 (5/24/2011)
Please Provide me Step by Step Information because i am new in it.and it is interview screening question.
If you need this step by step info for an interview what do you plan to do if you actually get the job? :blink:
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply