SSIS
-
Hi all, Hope this is in the correct forum. I have an issue with some work I am involved in. We have a flat file we are loading into an MSSQL database which is failing on the insert because the original flat file contains suspect data. Invalid dates or values etc. We are using SSIS to load this data. Can someone tell me if it is possible to exception these records easily. requirements are - to identify the number of records with the problem - to identify these records - to manage the issues which error I am a project manager and do not want the wool pulled over my eyes. I am familar with C# and SQL server but have only seen SSIS over a shoulder of a developer. I believe this would be a simple change and would be a matter of minutes to resolve. I am currently being led to believe it is hard. Thanks all
-
Hi all, Hope this is in the correct forum. I have an issue with some work I am involved in. We have a flat file we are loading into an MSSQL database which is failing on the insert because the original flat file contains suspect data. Invalid dates or values etc. We are using SSIS to load this data. Can someone tell me if it is possible to exception these records easily. requirements are - to identify the number of records with the problem - to identify these records - to manage the issues which error I am a project manager and do not want the wool pulled over my eyes. I am familar with C# and SQL server but have only seen SSIS over a shoulder of a developer. I believe this would be a simple change and would be a matter of minutes to resolve. I am currently being led to believe it is hard. Thanks all
Its not really the right forum, this is for question to do with C# coding. Happy to answer your question though.
Iain Wiseman wrote:
I believe this would be a simple change and would be a matter of minutes to resolve
:confused: NOTHING takes minutes to resolve - you should know that as a PM. Development, testing, deployment, bugfixing - a time estimate should take all of this (and more) into account. Anything to do with SSIS is a royal PITA, nothing is quick and easy. My best suggestion for this problem is to bulk load your text file into an empty table, and do the data clensing thereafter. All this can be achived easily with SSIS, but certainly not in a matter of minutes.
-
Its not really the right forum, this is for question to do with C# coding. Happy to answer your question though.
Iain Wiseman wrote:
I believe this would be a simple change and would be a matter of minutes to resolve
:confused: NOTHING takes minutes to resolve - you should know that as a PM. Development, testing, deployment, bugfixing - a time estimate should take all of this (and more) into account. Anything to do with SSIS is a royal PITA, nothing is quick and easy. My best suggestion for this problem is to bulk load your text file into an empty table, and do the data clensing thereafter. All this can be achived easily with SSIS, but certainly not in a matter of minutes.
Hi, Thanks for replying. So you are saying that you cannot ignore errors easily in SSIS and count them. I C++ or java or C# this would be a try, carch cout which is a matter of minutes. I had hoped that microsoft would have made this easier. Iain
-
Hi, Thanks for replying. So you are saying that you cannot ignore errors easily in SSIS and count them. I C++ or java or C# this would be a try, carch cout which is a matter of minutes. I had hoped that microsoft would have made this easier. Iain
Iain Wiseman wrote:
Thanks for replying. So you are saying that you cannot ignore errors easily in SSIS and count them
Not knowing how your SSIS package is constructed makes answering this harder. For a Data Flow task (which is the most likely thing you're using), you can often pipe errored rows to a separate file. Its still not "minutes" work though ;)
-
Hi, Thanks for replying. So you are saying that you cannot ignore errors easily in SSIS and count them. I C++ or java or C# this would be a try, carch cout which is a matter of minutes. I had hoped that microsoft would have made this easier. Iain
Jamie is correct, this will be a PITA to accomplish in SSIS. Anything that does the transform is bloody difficult, trapping the error rows would be a nightmare. SSIS and Biztalk are an attempt to dumb down the ETL layer so power users can do it - they FAILED. I load my data into a table of varchar columns, the bulk copy does not care what the data is like then. I then use a stored proc to do the transorms. It is MUCH easier to manipulate the data in TSQL or C# than in SSIS.
-
Jamie is correct, this will be a PITA to accomplish in SSIS. Anything that does the transform is bloody difficult, trapping the error rows would be a nightmare. SSIS and Biztalk are an attempt to dumb down the ETL layer so power users can do it - they FAILED. I load my data into a table of varchar columns, the bulk copy does not care what the data is like then. I then use a stored proc to do the transorms. It is MUCH easier to manipulate the data in TSQL or C# than in SSIS.
Thanks all, I come from an open source background and want to treat my colleagues who use microsoft fairly. All I want is the number of records in error and possible which they are. I assume that once you have a package to load them theat error reporting was easy. Reading this SSIS does not provide this easily. With oracle PLSQL this would simply be looking at a return code and doing something. I will ask the developer how to proceed.