InstantForum.NET v4.1.4SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 20 Mar 2010 17:23:17 GMT20http://www.sqlservercentral.com/Forums/Topic814587-148-1.aspxHeh... I LOVE batch files. :-PPoint 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.Sat, 07 Nov 2009 08:22:23 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic814587-148-1.aspx[quote][b]Jeff Moden (11/6/2009)[/b][hr]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.[/quote]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 :-DPaulSat, 07 Nov 2009 00:47:56 GMTPaul White NZhttp://www.sqlservercentral.com/Forums/Topic814587-148-1.aspx[quote][b]Dr.D (11/6/2009)[/b][hr]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 :([/quote]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.Fri, 06 Nov 2009 20:48:58 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic814587-148-1.aspxThanks 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.Fri, 06 Nov 2009 12:43:31 GMTDr.Dhttp://www.sqlservercentral.com/Forums/Topic814587-148-1.aspx>>If the file is imported successfully then send email with message: The file is importedThis 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).aspxhttp://www.developerdotstar.com/community/node/327>>If there is now file to import send email with message: There isn’t any file to importLots 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 importedThis 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.Fri, 06 Nov 2009 09:41:53 GMTemily-1119612http://www.sqlservercentral.com/Forums/Topic814587-148-1.aspxThanks 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 importedIf there is now file to import send email with message: There isn’t any file to importIf 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 importedFri, 06 Nov 2009 03:33:47 GMTDr.Dhttp://www.sqlservercentral.com/Forums/Topic814587-148-1.aspx[quote][b]Dr.D (11/6/2009)[/b][hr]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.[/quote]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.Fri, 06 Nov 2009 03:32:52 GMTSilverfoxhttp://www.sqlservercentral.com/Forums/Topic814587-148-1.aspxThe 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.Fri, 06 Nov 2009 03:08:35 GMTDr.Dhttp://www.sqlservercentral.com/Forums/Topic814587-148-1.aspx[quote][b]Dr.D (11/6/2009)[/b][hr]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 :([/quote]This is really basic ETL, what do you have trouble doing, I wish I was given 3 days to write something like this. :-DI 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.Fri, 06 Nov 2009 02:13:28 GMTSilverfoxhttp://www.sqlservercentral.com/Forums/Topic814587-148-1.aspxIt 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 :(Fri, 06 Nov 2009 02:06:48 GMTDr.Dhttp://www.sqlservercentral.com/Forums/Topic814587-148-1.aspxHi,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 meFri, 06 Nov 2009 00:30:26 GMTDr.Dhttp://www.sqlservercentral.com/Forums/Topic814587-148-1.aspx[quote][b]Dr.D (11/5/2009)[/b][hr]Please, It is urgent [/quote]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.Thu, 05 Nov 2009 20:14:13 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic814587-148-1.aspxDo 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.Thu, 05 Nov 2009 16:48:04 GMTemily-1119612http://www.sqlservercentral.com/Forums/Topic814587-148-1.aspxGreetings,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 variableSo 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...Thu, 05 Nov 2009 16:45:36 GMTemily-1119612http://www.sqlservercentral.com/Forums/Topic814587-148-1.aspxHi 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 importedIf there is now file to import send email with message: There isn’t any file to importIf 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 importedI 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 urgentThu, 05 Nov 2009 14:52:05 GMTDr.D