Importeren verschillende CSV's > Forum

Forum

Unanswered

Active Topics

Forums

Forums > Forums > Business Intelligence

Subject: Importeren verschillende CSV's

Prev Next

You are not authorized to post a reply.

Page 1 of 2

Author

Messages

Mark Waterreus User is Offline

Posts:8

14-01-2008 15:16:38	Alert
Hola Mede-sql'ers Ik zit hier bij een klant met de situatie dat er een extern bronsysteem is, welke we aan willen gaan sluiten op het datawarehouse. Nu gaat er vanaf hier data aangeleverd worden welke we kunnen gebruiken om onze staging op te bouwen waarna het reguliere proces van delta's en integratie in werking kunnen duiken. Na hevig discussieren zijn we tot de conclusie gekomen dat de enige manier om data uit het systeem te ontsluiten via losse .csv bestanden is. Aan de datawarehouse kant gaan we dan deze csv bestanden weer naar een database importeren zodat we vanaf daar de staging en delta's kunnen gaan draaien. (klinkt erg omslachtig maar er was geen andere optie) Nu hebben we vandaag de bestanden ontvangen waarmee wij moeten gaan werken en tot mijn schrik zijn het 275 csv-bestanden. Op het eerste gezicht komt dat neer op 275 dataflows in een SSIS-dtsx-pakket waarbij iedere dataflow een van de csv-bestanden uitleest en deze importeert naar de database. (dit of 275 losse SSIS-pakketten...) Nu is mijn eerste reactie hier gelijk of het niet mogelijk is om dit op een of andere manier dynamisch te doen. Het is uiteraard mogelijk om deze bestanden in een For Each Container te gooien en zo stuk voor stuk de .csv bestanden door een generieke dataflow te trekken, maar dit is toch niet zo makkelijk: - Ieder csv bestand heeft zijn eigen kolommen - Ieder csv bestand moet naar een eigen tabel weggeschreven worden - Iedere destination-tabel bestaat nog niet en zou dus dynamisch op basis van de inhoud van de csv gemaakt moeten worden Googlen en schakelen met wat collegae heeft mij alleen maar opgeleverd dat dit dat ik me er maar op moet voorbereiden dat ik 275 losse dataflows kan gaan maken. En dat moet volgens mij zeker te voorkomen zijn. Mijn vraag dus aan onze groep met experts hier of er iemand is die een soortgelijke situatie heeft gehad; en hoe hij/zij deze opgelost heeft. Indien er niemand is die iets dergelijks heeft meegemaakt hoor ik alsnog graag suggesties over hoe ik kan voorkomen dat ik 275x hetzelfde aan het doen ben (dus 275x een identieke dataflow-task, create scripts, mappings etcetc). Oplossingen in iets anders dan SSIS (SQL, C#/VB.Net, PHP.....) zijn welkom. (kant en klare pakketten welke dit kunnen heb ik niet veel aan...) Alsvast bedankt voor het meedenken. Met vriendelijke groet, Mark Waterreus

Antoon Vansina User is Offline

Posts:17

14-01-2008 16:40:49	Alert
Ik weet niet of dit veel zal helpen. Maar het startpunt lijkt mij het moeilijkste: nl kiezen om te werken met csv bestanden. Als je gewoon 200+ tabellen moet overdragen van een productiesysteem naar het DWH dan doet de SSIS Import/Export wizard het grote werk. Met tekst bestanden gaat dat allemaal niet zo vlot. Bovendien zal het toelaten om wijzigen aan de te-extraheren-gegevens veel vlotter en sneller te laten verlopen edm.. Ik zie ook geen reden om te werken met csv bestanden tenzij de DB zo exotisch is dat er geen connenction manager bestaat in SSIS. Als dat geen optie blijft én je zal in de toekomst priodiek die 200+ bestanden moeten inlezen in je DWH dan zou ik mbv de I/E wizard het toch maar allemaal opzetten dan heb je iets waar je later nog plezier aan kan hebben. Toch veel plezier ermee

Arjan Fraaij User is Offline

Posts:110

14-01-2008 20:35:08	Alert
Hallo, Ok CSV bestanden, inderdaad waarom die keuze zoals Antoon aangeeft. Ik weet er wel: geen directe data connectie met de data leverancier. Dit omdat het niet is toegstaan om de fysieke netwerken met elkaar te verbinden. Aanlevering van data gaat via DVD's één keer per maand bijvoorbeeld. Ook voor flat files geldt natuurlijk dat de import/export wizard het meeste werk doet. Wat echter blijft is dat de destination tabel dient te worden aangemaakt. Wil je het echt flexible maken dan blijft er volgens mij altijd een stukje onderhoud over, wat mijn idee zou kunnen zijn is twee tabellen aan te maken. CSVConnect: CSVFileName\|CSVFileLocation CSVTableCreation:CSVFileName\|ColumnName\|DataType\|DataLength\|etc CSVConnect gebruiken voor de FlatFileConnector CSVTableCreation gebruiken voor de CreateTable Step. blijft wel over dat je nog steeds éénmalig alle tabel constructies voor de CSV's in CSVTableCreation moet aanmaken. Daarna kan je echter wel af met 1 SSIS package voor de verwerking hiervan. Ook ben je flexibel in het uitbreiden hiervan. Nee een voorbeeld heb ik niet. Maar wel iets wat ik wil proberen om eens mee te spelen. Helaas momenteel even wat weinig tijd. gr, Arjan

Robert Hartskeerl User is Offline

Posts:86

15-01-2008 09:31:53	Alert
Je kan SSIS uitbreiden met scripts of met C# code om de dataflow of andere objecten te beïnvloeden. Ik heb even geen voorbeeld bij de hand, maar bij Microsoft staan voorbeelden. Ook in het MSDN magazine heeft een keer een voorbeeld gestaan van een custom dataflow object dat je in SSIS kon gebruiken. Het is dan vrij makkelijk om in bv C# de tabel aan te maken. Je kan dan nog steeds de foreach container gebruiken. 1. foreach directory om csv op te halen. 2. Met een custom object of script een tabel aanmaken. 3. Dan de csv importeren in de aangemaakte tabel Moeilijkheid is een beetje het definieren van de datatypes. Hiervoor zul je eerst een scan moeten doen van de data of alles op varchar(max) zetten.

Antoon Vansina User is Offline

Posts:17

15-01-2008 09:39:54	Alert
De I/E wizard maakt creeert wel degelijk de tabellen voor jou, zowel voor een txt-source als voor een DB-soource. Het verschil is dat je met een DB-source ineens je 200+ tabellen kan selecteren, met een txt-source moet per bestand de wizard opstarten om een package te maken.

Mark Waterreus User is Offline

Posts:8

15-01-2008 10:13:37	Alert
Bedankt voor de snelle reacties allemaal! Allereerst de voor de hand liggende vraag: CSV is geen keuze geweest maar wordt gewoon opgelegd vanuit de business. Hoofdreden is dat de brondatabase extern draait en we er geen directe toegang toe kunnen krijgen. Na er een nachtje over geslapen te hebben in combinatie met de reacties hier (en uit andere bronnen) ben ik al wel tot de conclusie gekomen dat ik er niet omheen kom om eenmalig alle table-constructies aan te maken waarbij de datatypes handmatig bepaald worden op basis van de inhoud van de CSV. Dit is dus ook het eerste wat ik ga doen om in ieder geval het raamwerk te maken waar de bestanden in moeten komen. Ondertussen kan ik dan nog nadenken over een slimme oplossing om de tabellen te gaan vullen. Ik twijfel nog tussen het schrijven van een stuk C# om dataflows (of losse packages) te genereren of het gebruiken van een ForEach in combinatie met script-tasks om alles in 1 loop in te kunnen lezen. De eerste geeft heel veel dataflows maar lijkt me sneller om te maken. De tweede wordt wat complexer maar is wel veel overzichtelijker

Arjan Fraaij User is Offline

Posts:110

15-01-2008 10:20:57	Alert
Ik bedoelde eigenlijk wat jij zij Antoon, de wizard maakt inderdaad de tabel aan voor tekst files, dan moet mark echter in deze context 275 keer door de wizard heen. Vandaar mijn opmerking dat je zelf de destination tabel moet aanmaken ik was alleen even vergeten bij te vermelden dat ik dit bedoelde in het kader als je één generiek SSIS package wil maken. Dit kan een C# object zijn of een stored procedure die gebruik maakt van de voorgedefineerde tabel structuur in de CSVTabelCreation tabel. Ga hier eens op mijn gemak mee zitten spelen, als ik iets heb dan plaats ik de package wel. Wellicht dat andere betere ideeën hebben.

Mark Waterreus User is Offline

Posts:8

15-01-2008 10:44:59	Alert
Hetgeen ik alleen niet direct bij jou zie Arjan is hoe dat je dan dynamisch gaat bepalen welke tabel gevuld gaat worden? Dit is eigenlijk mijn grootste vraagstuk nog steeds een beetje. Hoe zorg ik ervoor dat ik variabel een CSV in een Dataflow lees welke dan weggeschreven wordt naar een tabel welke per csv verschillend is met per csv ook verschillende kolommen. Het is wel mogelijk om variabel een bestand te lezen (de FlatFileConnector kan een variabele naam aan in combinatie met een ForEach) maar het is vooralsnog mij niet duidelijk hoe ik dan de destination variabel krijg. Het lijkt er bijna op alsof ik dit met een scripttask zal moeten gaan doen. En hoe voorkom ik dan hierbij dat ik constant metadata errors ga krijgen (want de kolommen zijn constant anders)? Het lijkt mij dat SSIS redelijk moeilijk gaat doen als iedere keer de kolommen anders zijn. Robert: weet jij nog in welk nummer van MSDN magazine dit stond?

Arjan Fraaij User is Offline

Posts:110

15-01-2008 13:29:46	Alert
Mark, Als het inderdaad is dat je per CSV een tabel wilt hebben? weggeschreven wordt naar een tabel welke per csv verschillend is met per csv ook verschillende kolommen. dus 275 tabellen? Kan je dan geen gebruik maken van de Bulk Insert Task in een forEach loop? 1 - Nieuw package 2 - variable CSVFile (string) 3 - ControlFlow toevoegen ForEach Container 4 - Op de Collection tab by Retreive File Name de "Name Only" selecteren 5 - Op de VariableMappings tab User::CSVFile mappen 6 - In de forEach Container een BulkInsert Task Toevoegen 7 - BulkInsert Task Tab Connection : Je database connectie maar DestinationTable Leeg laten 8 - BulkInsert Task Tab Connection : Format goed zetten (er vanuit gaande dat iedere CSV het zelfde geformat is) 9 - BulkInsert Task Tab Options : FirstRow op 2 zetten (er vanuit gaande dat iedere CSV een header row heeft) 10 - BulkInsert Task Tab Expressions : DestinationTableName = @[User::CSVFile] Blijft nog over dat je de destination tables moet creeren en de kolom volgorde gelijk houden aan die van de CSV files. Hievoor is ook wel iets voor te maken in de package. Weet alleen nog niet hoe. Of is dit wegens traagheid of iets anders wat ik over het hoofd zie geen optie?

Mark Waterreus User is Offline

Posts:8

15-01-2008 15:01:09	Alert
Arjan: dat zou inderdaad werken als de CSV's hetzelfde format hebben, maar dat hebben ze niet :) Na wat prutsen heb ik hem al wel zover dat hij alle CSV's uit de map dynamisch inleest en ze dynamisch naar de bijbehorende tabellen wilt schrijven maar verder dan dat hij het wil doen krijg ik het niet. Het probleem is namelijk dat de metadata niet automatisch gerefreshed wordt... Dus de OLEDB_Destinations blijven verwijzen naar de "vorige" insert en slaan vast omdat de kolommen niet meer bestaan in de dataflow. Zoeken op automatisch updaten van de metadata stuurt mij naar een zeer dikke muur van gewapend beton omdat dit dus niet mogelijk is. Enige optie welke door verschillende bronnen op het internet wordt gegeven is het automatisch genereren van dtsx-pakketten; eentje per csv en destination-table. http://www.ivolva.com/ssis_code_generator.html wilde ik gaan gebruiken als startpunt om in vb.net/c# een simpel applicatietje te maken welke deze pakketten kan genereren. Nog niet ideaal maar het lijkt wel iets te zijn wat gaat werken :)

André Kamman User is Offline

PASS Nederland

Posts:137

15-01-2008 15:33:30	Alert
Heb je er al eens aan gedacht om geen SSIS te gebruiken maar bulk insert of bcp.exe ? Die verwachten ook metadata maar dat is in tekstformaat (of zelfs xml tegenwoordig bij bcp) en dat kun je genereren ! Je maakt 1 keer een metadata tabel waar de gegevens over de te importeren tabellen inzitten en die gebruik je om ..... 1. Create scripts te genereren voor de tabellen 2. BCP formatfiles te genereren 3. batchfile genereren met de BCP commando's Als je alles inleest in tabellen die voor elke kolom een varchar(max) hebben dan kun je eerst de data opschonen voor je het naar de eindbestemming overzet. Ook dat kun je vanuit de repository doen door bijvoorbeeld standaard scripts te maken voor elk datum veld. etc. Ik heb wel eens met een dergelijke situatie te maken gehad maar dan met ongeveer de helft van het aantal tabellen, ook in c.s.v. formaat en DTS (ja, antiek inmiddels, ik weet het) DTS genereren is toen niet gelukt en we hebben ze met de hand gemaakt. Die repository heeft ons toen wel veel geholpen, bijvoorbeeld door code te genereren voor elk datum veld om het formaat naar wens aan te passen. Het was in het begin erg veel werk maar later in het beheer wel heel overzichtelijk ! SSIS is erg krachtig omdat je o.a. niet meer perse eerst alles naar tijdelijke tabellen hoeft te schrijven maar on the fly aanpassingen kunt doen. Soms geef ik echter de voorkeur aan de "ouderwetse" oplossing, juist vanwege de problemen met het verversen van de metadata in SSIS packages waar jij tegenaan loopt. Ongevraagd wil ik je nog graag iets meegeven dat ik bij voornoemd project heb geleerd : 1. CSV is geen vaste standaard, maak als je kunt afspraken over NULL values, text delimiter, column separator, field separator, etc. Multi line tekst in een csv is een drama, en ook het kiezen van een tekstdelimiter die in de tekst zelf ook kan voorkomen en niet goed uitgequote is kan veel problemen opleveren. (alt 250 bleek voor ons een goeie te zijn, zelfs data van Tandems en mainframes ging goed vanaf dat moment) 2. Maak afspraken over het beheer van het formaat, wie geeft je wijzigingen door in de bronbestanden en is dat op tijd etc. Hier komen die metadata tabellen goed van pas. Die code generator moest ik ook maar eens gaan bekijken...... Groeten, André

Arjan Fraaij User is Offline

Posts:110

15-01-2008 20:38:22	Alert
Mark, Indien geen standard formatting voor de csv bestanden kan je per csv bestand ook een formatfile maken. Ook deze kan je in het Bulk Insert Expresion Tab dynamisch toekennen. Dus inderdaad wat André ook aangeeft en ik ietsjes eerder met de CSVTableCreation meta tabel voor het creeëren van de destination tables. Dus hoewel André natuurlijk aangeeft om een batch command te maken zou je dit ook nog steeds kunnen met een SSIS package. Gr, Arjan

Ruud Boots User is Offline

Posts:62

15-01-2008 21:20:36	Alert
Hoi Mark, FYI In onze datawarehouse LOAD maken wij ook gebruik van het "antieke" commando BULK INSERT op een 70tal csv bestanden. (uit noodzaak geboren door ontbreken van een 64BIT informix driver destijds) De tabelnamen zijn opgeslagen in een metamodel, met een cursor over dit model wordt elk BULK INSERT commando (voorafgegaan door een truncate) in een Service Broker queue gezet, waarna deze parallel worden uitgevoerd, verdeeld over beschikbare CPU's. (4 stuks in onze server) Voorwaarde is wel dat de tabellen in staging aanwezig moeten zijn met een overeenkomstige layout als het CSV bestand. Voordeel van de service broker in combinatie met BULK INSERT is behalve een optimale performance door paralleliteit ook de Asynschrone verwerking. Bij een mismatch van de layout of inhoudelijk probleem van één of meerdere tabellen faalt de LOAD niet. De SB queue wordt volledig uitgelezen en verwerkt. Logging en alerts zijn ingebouwd om te kunnen debuggen bij een probleem. groet, Ruud

Mark Waterreus User is Offline

Posts:8

16-01-2008 09:41:59	Alert
Ik ga eens verder duiken in de wereld van Bulk Inserts. Het lijkt er inderdaad op of de oplossing daar ligt. Ik hou jullie op de hoogte :)

Ruud Boots User is Offline

Posts:62

16-01-2008 11:13:36	Alert
Als je nog eens van gedachten wil wisselen of een kijkje wil nemen in onze datawarehouse "keuken" ben je van harte welkom op ons kantoor in Amsterdam Sloterdijk

Rob Kool

Posts:11

13-02-2008 08:36:07	Alert
Ik heb laatst ook een oplossing gezien voor een soortgelijk probleem. Ook hier was het via een Bulk Insert oplost, maar wel binnen DTS m.b.v. format files en een metadata tabel waarin de formatfile en de destination tabel voor iedere import file stonden beschreven. M.b.v de dynamic property task werden de juiste properties van de Bulk Insert Task gezet nadat deze eerst uit de metadata tabel waren gelezen. Als je een voorbeeld van dit package wilt, laat het dan even weten, dan stuur ik de DTS file even op. Blijft natuurlijk wel de taak over om 275 tabellen en 275 format files te maken. Maar volgens mij zou dit hele verhaal ook in SSIS moeten kunnen werken. Als je de Bulk Insert Task neemt en de waarden voor "Format", "DestinationTable" en "File" dynamisch (in Expersions Tab) zet op basis van gegevens die je uit de metadata tabel ophaald. Het gaat dan de richting uit van Arjans verhaal: 1 - Nieuw package 2 - variable CSVFile, CSVFormatFile, CSVDesitnation (Alle string) 3 - ControlFlow toevoegen ForEach Container 4 - Op de Collection tab by Retreive File Name de "Name Only" selecteren 5 - Op de VariableMappings tab User::CSVFile mappen 6 - SQL Task voor ophalen van metadata Input: CSVFile Output:CSVFormatFile en CSVDestination 7 - Bulk Insert Task toevoegen 8 - Connection Tab: Connection naar Destination Database zetten 9 - Connection Tab: Format op "Use File" zetten 10 - Expressions Tab: Properties zetten: FormatFile = @[User::CSVFormatFile], SourceConnection = @[User::CSVFile], @DestinationTable = @[User::CSVDestination] Je kunt 1 source connection gebruiken en ook hiervan de ConnectionString middels een Expression zetten.

Arjan Fraaij User is Offline

Posts:110

13-02-2008 09:22:06	Alert
Rob, Inderdaad is dit mogelijk, heb het kort even gemaakt voor 3 csv files en formatfiles. Mark, wil je ons laten weten wat je uiteindelijke oplossing is geworden? Handig voor een soort knowledge base. Gr, Arjan

Ruud Boots User is Offline

Posts:62

13-02-2008 13:43:42	Alert
Rob, Ik ben nieuwsgierig naar de layout van een dergelijke formatfile, kun je daar een voorbeeld van posten? groet Ruud

Rob Kool

Posts:11

13-02-2008 14:18:34	Alert
De format file ziet er als volgt uit voor een file met "]" als delimiter: .... 7.0 22 1 SQLCHAR 0 9 "]" 1 JaarMeldingsNummer 2 SQLCHAR 0 6 "]" 2 PlaatsCode 3 SQLCHAR 0 2 "]" 3 SoortSchadeCode 4 SQLCHAR 0 5 "]" 4 ExpertiseBuroCode 5 SQLCHAR 0 5 "]" 5 CoordinatorCode 6 SQLCHAR 0 1 "]" 6 StatusCode 7 SQLCHAR 0 1 "]" 7 ZinvolleMelding 8 SQLCHAR 0 2 "]" 8 AantalBelendingen 9 SQLCHAR 0 2 "]" 9 AfgeslotenDoorAwCode 10 SQLCHAR 0 10 "]" 10 DatumMelding 11 SQLCHAR 0 4 "]" 11 TijdstipMelding 12 SQLCHAR 0 10 "]" 12 DatumOproep 13 SQLCHAR 0 4 "\r\n" 13 TijdStipAankomst .... Meer info over de format file: http://msdn2.microsoft.com/en-us/library/ms178129.aspx en http://msdn2.microsoft.com/en-us/library/ms191516.aspx

Mark Waterreus User is Offline

Posts:8

25-02-2008 16:09:43	Alert
Hola allemaal! Excuses voor de late reactie maar het is wat druk geweest aan deze kant. In ieder geval bedankt voor alle reacties hier, ik heb een hoop uitgezocht en uitgeplozen naar aanleiding van de geboden alternatieven en ben uiteindelijk tot de conclusie gekomen dat eender welk alternatief ik zou kiezen het me veel tijd zou gaan kosten om het op te zetten. Afgezien van het feit dat het in de toekomst veel oplevert was de ruimte er even niet voor op dat moment Ik ben teruggegaan naar de Business en heb aangegeven dat alles in 1x inlezen wel heel veel tijd gaat kosten en dat het misschien slimmer en sneller is om het toch iets anders aan te pakken. Zoals Business eigen is riepen ze initieel dat ze alles wilden hebben wat gelukkig weer eens overdreven bleek te zijn. Samen met de business heb ik de specs van het eindprodukt nog eens scherp gezet en zijn we tot de conclusie gekomen dat we de eerste fase met een kleine 25 tabellen afkunnen. Grappig genoeg kwam uit dezelfde discussie naar voren dat van dat alles wat ze eerst wilden hebben de helft toch helemaal niet nodig bleek te zijn... Business-users: je moet er mee om leren gaan... De eerste fase draait nu in ieder geval op die tabellen welke er voor nodig zijn. Op de achtergrond ben ik nu bezig met het opzetten van metadata beschrijving voor de verschillende bestanden. Ik ontkom er blijkbaar niet aan om alle tabellen te beschrijven (qua kolomnaam en format) dus dit is iets waar ik mezelf in de vrije uurtje mee kan vermaken. Voordeel voor mij is dat ik hier wat meer tijd voor heb omdat het volgende increment nog wel even gaat duren voordat het live moet.

You are not authorized to post a reply.

Page 1 of 2

Forums > Forums > Business Intelligence > Importeren verschillende CSV's

ActiveForums 3.6