November 5, 2009 at 2:52 pm
Hi all,
I have a table, containing 5 columns: Column1, column2, column3, column4, Filename and a .csv file with name that every day is different and it contains a date when the file is received. The name of the file is like YYYYMMDD<4 characters that are random>003.csv (example: 200911053w3r003.csv). At the same time I may have 0, 1 ore more files. I have to import data from that file into a table.
My first question is how to fill the FileName column in the table? Until now, I made a SSIS that loop through all .csv files in the folder and import data from them but without filling the file name column.
Second how to handle this logic for sending emails:
If the file is imported successfully then send email with message: The file is imported
If there is now file to import send email with message: There isn’t any file to import
If the format of the file name is not as I described send email with message: Wrong file format.
If there is a problem with data in the file send email with message: The file can not be imported
I have read a lot of articles about processing files using SSIS but I couldn’t fine how to deal with those two requirements.
Please, provide me a solution with detail explanation if it is possible because I’m new in SSIS…Please, It is urgent
November 5, 2009 at 4:45 pm
Greetings,
My time is short but I will give you the quick outline.
-create a user defined string variable to hold you file name
-Go to the properties of the for each loop and on the variable mapping pane set this to your variable
So everytime the the loop happens the variable is set to the current filename being processed
-within your loop create a SQL task
-configure the parameter mapping here with your user variable
-set the SQLstatement to INSERT INTO YourTable (Filename) VALUES (?)
I hope this gets you started, I can provide more details tomorrow...
November 5, 2009 at 4:48 pm
Do you want to insert the file name in every line of data being imported? Use the above mentioned variable in derived column within your data flow.
November 5, 2009 at 8:14 pm
Dr.D (11/5/2009)
Please, It is urgent
That's quite possibly the worst thing you can say on these forums. Because of all the people that say that because the real urgency is a job interview, university final exam, or homework, most folks won't even bother answering a question when those words are present.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 6, 2009 at 12:30 am
Hi,
First, the solution with insert sqlstatement task is not what I need, because I want to have the file name when the data from the file is importing.
Second, emily (or somebody else), can you five me an example how to use derived columns with variable.
Thanks to all trying to help me
November 6, 2009 at 2:06 am
It is not about a job interview nor university final exam nor homework. This is my task at work and i have 3 days to finish it. Yesterday was my firs day, so 2 days left 🙁
November 6, 2009 at 2:13 am
Dr.D (11/6/2009)
It is not about a job interview nor university final exam nor homework. This is my task at work and i have 3 days to finish it. Yesterday was my firs day, so 2 days left 🙁
This is really basic ETL, what do you have trouble doing, I wish I was given 3 days to write something like this. 😀
I take it that you are importing the data into a single table and your comment about derived column, is for the filename column that you want populated with the filename that you are importing.
You need to say exactly what you have problems with, otherwise the only way someone could help you, is for them to actually create the package themselves and that is a bit too much to ask of anyone here.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
November 6, 2009 at 3:08 am
The problem is how to take the file name that I'm importing and fill the table with it. The situation is like that: The table that i'm filling it with data from .csv file contains column filename that should be filled in with the name of the file that i'm importing.
November 6, 2009 at 3:32 am
Dr.D (11/6/2009)
The problem is how to take the file name that I'm importing and fill the table with it. The situation is like that: The table that i'm filling it with data from .csv file contains column filename that should be filled in with the name of the file that i'm importing.
You cannot use the derive transformation to do that, unless anyone else wants to disagree, the transformation can add a new column or update an existing one based on the source. emily's suggestion was more valid.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
November 6, 2009 at 3:33 am
Thanks to all... I succedded to fill FileName column. Only error handling left.
Can somebody give me an example how to deal with this recuirement:
If the file is imported successfully then send email with message: The file is imported
If there is now file to import send email with message: There isn’t any file to import
If the format of the file name is not as I described send email with message: Wrong file format.
If there is a problem with data in the file send email with message: The file can not be imported
November 6, 2009 at 9:41 am
>>If the file is imported successfully then send email with message: The file is imported
This may help or at least give you the proper terminolgy for further research on your requirements.
http://msdn.microsoft.com/en-us/library/ms142165(SQL.90).aspx
http://www.developerdotstar.com/community/node/327
>>If there is now file to import send email with message: There isn’t any file to import
Lots of ways to do this, you could create an integer variable and increment for each loop. I would log each file into a table as I refered to earlier and then count the records.
>If the format of the file name is not as I described send email with message: Wrong file format.
>If there is a problem with data in the file send email with message: The file can not be imported
This is a difficult requirement. Saying the format is wrong for a package, well what does that mean, the package failed? Similarly its hard to say what the problem is with the data if it hasn't been imported.
You probably would have to validate the file before attempting import to see if its the 'right' format or if there is a 'problem' with the data.
I would email all error messages to get the best understanding of failure reasons. Perhaps you could categorize all possible error messages as 'bad format' or 'bad data' and email that.
November 6, 2009 at 12:43 pm
Thanks for your replay... I think this will give me an answer...
and... I agree with you about categorizing errors on the way as i described but... My PM want to see the result of the package, regarding the errors as I described...
Thanks to all for you help and i will investigate the links you give.
November 6, 2009 at 8:48 pm
Dr.D (11/6/2009)
It is not about a job interview nor university final exam nor homework. This is my task at work and i have 3 days to finish it. Yesterday was my firs day, so 2 days left 🙁
Didn't say it was... was just advising you why (for other people, it has been) using the word "urgent" has become a bit of a taboo on this forum. 😉
As a side bar, I'm sorry they're forcing you to do this in SSIS... I do this type of stuff all in T-SQL because every time I turn around, I see someone having the same problems that you're currently having with it. I thought SSIS was supposed to make things easier and it doesn't appear to have.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 7, 2009 at 12:47 am
Jeff Moden (11/6/2009)
As a side bar, I'm sorry they're forcing you to do this in SSIS... I do this type of stuff all in T-SQL because every time I turn around, I see someone having the same problems that you're currently having with it. I thought SSIS was supposed to make things easier and it doesn't appear to have.
I agree. If one has no experience with any product as complex as SSIS, it seems a bit unfair.
If one is deeply familiar with T-SQL and bcp, it will obviously be easier and faster to develop a solution using those tools. Likewise, I could probably have a good stab at producing a solution entirely in batch files 😉
SSIS is a very powerful tool - which is arguably also its greatest weakness. Like SSAS, SSRS, and CLR programming, SSIS is a whole separate product really. I know people that dedicate themselves to just one of those components, and make a good living at it.
So, my point is really that unless you have done extensive DTS work before, and are very famailiar with Visual Studio, and have the time to learn SSIS to a sufficiently deep level, you will probably find non-trivial tasks in SSIS an uphill struggle for a while.
That said, I have a lot of time for SSIS - it is worth learning, and is a great place to house most, if not all, or your ETL and other data transformation routines.
There are multiple valid ways to remove the epidermis of a felis domestica 😀
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 7, 2009 at 8:22 am
Heh... I LOVE batch files. 😛
Point well taken, though. I've been put off of SSIS (and, previously, DTS) because of what I've seen people do with it... they can't figure out how to do something in it and write a VBS, ActiveX, Perl, or other script to accomplish the task. DTS really put me off because of the PITA when it comes to things like reconvergent multiple paths (ie. follow a side path because of conditions and rejoin the main path when complete without having to execute the other path(s)). That notwithstanding, I guess it's probably time to learn how to use SSIS the right way.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply