SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Sarah Doriss / Dynamic ETL with SSIS / Latest Posts

RE: Dynamic ETL with SSIS

Pascal J v Vuuren — Tue, 07 May 2013 14:10:55 GMT

[quote][b]opc.three (5/3/2013)[/b][hr]Also worth noting, once you move file parsing into the database you gave up the option for doing data cleansing, validation, multi-system data enrichment, lots of things, in the SSIS space. I realize that sometimes we go with what we know, but SSIS was built to do this kind of work, and dynamically too. I think a lot of folks consider using tools other than T-SQL when the task seems to stretch the limits of what T-SQL [i]should[/i] be doing, but when it gets to a point where they just need to get something done they revert to T-SQL because that is what is comfortable to them. Crutches like xp_cmdshell (as one poster mentioned, not your article) make it easier to do that as well and point to that type of thought process. I applaud your use of SSIS to at least avoid accessing the cmd prompt and file system from within T-SQL, something I never condone.[/quote]Indeed. With a bit of code the EZApi on CodePlex (See [url=http://sqlsrvintegrationsrv.codeplex.com/]here[/url]) can also be used to leverage what SSIS can do natively through dynamically generated packages. Which, even if used as an accelerator, can provide benefits. Still, the right tool at the right time.

RE: Dynamic ETL with SSIS

SQLQuest29 — Fri, 03 May 2013 08:35:52 GMT

[quote][b]Martyn Hughes (10/19/2010)[/b][hr]Steve, I've been a great fan of SSC for many years, I've used a number of redgate tools and Litespeed to compress some rather large DBs especially when using a SAN. I think Susan made a good contribution to the forum.My comment is not meant to be 'snarky', I'm always troubled when I have to fight within an organization to get useful tools. Pervasive does cost several thousand but the amount of time saved using appropriate tools rather than paying FTEs to 'reinvent the wheel' is certainly a better use of company resources.Pervasive has data connectors for just about anything, even really old 132 column report type stuff! (Only useful if you're dealing with old mainframe stuff)I've used it to convert Progress to Oracle DW, Progress to SQL and vice-versa, Foxpro to SQL. On a smaller scale I used it to convert Quickbooks to SQL.SSIS is certainly better than the old DTS but far behind 3rd party tools.[/quote]Does not Pervasive use RIFL scripting as the language ?

RE: Dynamic ETL with SSIS

ahperez — Fri, 03 May 2013 08:29:07 GMT

Thanks Sarah, your article is very intersting and gave me some ideas. I can't wait to play around with this. Most of my business data comes from vendor flat files, mostly fixed width, with anywhere from 50-400 fields. Data dictionaries or layout guides are usually available, although the vendors don't usually notify me when making format changes. Fun!You are right, it is much simpler to use the database for transformations and logic. Thanks for explaining how to store and use the data definition and import criteria there. SSIS is generally slow and frustrating for testing and troubleshooting my wide files. Your solution is also one I can afford on my shoestring IT budget!I use the field name and width with the concatenate function in Excel to quickly make the fields section of a script for a raw import table, then paste that into my SSMS script. A quick way to make fields in SSIS is to use BIDS Helper (free, available on CodePlex). I copy the field name and width and paste it into the BIDS Helper "Create Fixed Width Columns" window. These two things have saved me days of work during testing of a new vendor format.

RE: Dynamic ETL with SSIS

opc.three — Fri, 03 May 2013 08:03:00 GMT

Thanks for sharing your success story. I like that in the end you got a working solution that serves your users and your company well. That's what we do.While I appreciate the result, I would have gone about it differently. From my perspective many of the efforts duplicated what SSIS does for us for free, namely the file parsing. Also worth noting, once you move file parsing into the database you gave up the option for doing data cleansing, validation, multi-system data enrichment, lots of things, in the SSIS space. I realize that sometimes we go with what we know, but SSIS was built to do this kind of work, and dynamically too. I think a lot of folks consider using tools other than T-SQL when the task seems to stretch the limits of what T-SQL [i]should[/i] be doing, but when it gets to a point where they just need to get something done they revert to T-SQL because that is what is comfortable to them. Crutches like xp_cmdshell (as one poster mentioned, not your article) make it easier to do that as well and point to that type of thought process. I applaud your use of SSIS to at least avoid accessing the cmd prompt and file system from within T-SQL, something I never condone.Here is a technical paper from Microsoft explaining how to do everything in SSIS:[u][url]http://download.microsoft.com/download/D/2/0/D20E1C5F-72EA-4505-9F26-FEF9550EFD44/Build%20a%20Metadata-Driven%20ETL%20Platform%20by%20Extending%20Microsoft%20SQL%20Server%20Integration%20Services.docx[/url][/u]I know not everyone will sign up for a pure SSIS solution but something along these lines is how I would have solved this problem. Granted, i have .NET development skills which are required for putting together the solution described in the paper and not a lot of DBAs do, nor is the technology a good choice in all shops because of that same fact. Someone has to support the code too, so that has to be considered as well.

RE: Dynamic ETL with SSIS

Terry300577 — Fri, 03 May 2013 03:50:03 GMT

Excellent article. This is similar to something i have previously developed whilst working for a company which processes 10 - 1000's of data files daily.having the information in the database made it so much easier to process and create new imports. the one thing we didn't have was detailed info from our suppliers, we had to work this out and make loads of assumptions too.we also had the added problem of managing excel files along with text format files.

RE: Dynamic ETL with SSIS

s.doriss — Fri, 12 Nov 2010 11:27:39 GMT

yes, these are the same. The Load_* tables are supposed to have the Id, EverythingElse fields but the naming can be arbitrary because you would want one for each file. Also you would want one table for the parsed output per file. Sorry for the misnomer in the example, hope that helps you.

RE: Dynamic ETL with SSIS

ranjith_s 65449 — Fri, 12 Nov 2010 10:51:04 GMT

I see Load_RNFile in previous comments not Load_RN.

RE: Dynamic ETL with SSIS

s.doriss — Fri, 12 Nov 2010 10:44:06 GMT

The Load_RN table is referenced in a previous post comment.The function is purposely missing as mentioned in the article because you may want to calculate your own date.The RN_Upd table doesn't exist because this really needs to be formatted to the data dictionary of your file.Hope that helps.

RE: Dynamic ETL with SSIS

ranjith_s 65449 — Fri, 12 Nov 2010 10:13:13 GMT

While testing I see a couple of items missing:Msg 208, Level 16, State 1, Line 1Invalid object name 'RN_UPD'. (RN_UPD Table is Missing)Msg 208, Level 16, State 1, Line 2Invalid object name 'dbo.Load_RN'. (Load_RN Table is Missing)Msg 208, Level 16, State 1, Line 2Invalid object name 'dbo.GetNextWednesdaysDate'. (GetNextWednesdaysDate SP is Missing)Can you please add it? thanks!

RE: Dynamic ETL with SSIS

Steve Jones - SSC Editor — Thu, 11 Nov 2010 16:27:04 GMT

If you have a question about something not in the article, please start your own thread in the SSIS forum.

RE: Dynamic ETL with SSIS

shirishjani — Thu, 11 Nov 2010 13:46:00 GMT

i am new with this can you explain me if i have dynamic file like 123.txt ,456.csv and like that and want to load them in to sql server How can i achive that.??

RE: Dynamic ETL with SSIS

shirishjani — Thu, 11 Nov 2010 13:40:54 GMT

can you tell me how to create bulk like that

RE: Dynamic ETL with SSIS

Steve Jones - SSC Editor — Wed, 27 Oct 2010 14:54:11 GMT

I have added the DTSX file to the article if you are interested in viewing Sarah's work.

RE: Dynamic ETL with SSIS

s.doriss — Mon, 25 Oct 2010 11:10:14 GMT

I believe you could use this approach for exporting as well. You would be going back the other way in terms of concatenating all your fields together and then dumping them into the flat file into the "everythingelse" field so that SSIS could pick up the flat data. You would still want to create some sort of dictionary but it might mainly be used for converting the fields to varchar/nvarchar so that they could be concatenated. You also may need to create some special functions for concatenating based on fixed length versus delimited. If you created your business tables that mapped exactly to the fields you wanted to export, getting them concatenated into the Load_* tables wouldn't be too bad. Then your SSIS package would read the criteria of which table to pick up and export that. File names could be generated using information in the database.

RE: Dynamic ETL with SSIS

mishaluba — Sat, 23 Oct 2010 12:38:23 GMT

This is a great article! I am facing a very similar task right now and will certainly use many of these ideas. One request. If it's not too much trouble, please provide an SSIS leveraging this approach (the screen shots are in the article by an actual .dtsx would be helpful).Thank you!

RE: Dynamic ETL with SSIS

thumper_disc — Fri, 22 Oct 2010 12:01:07 GMT

You can export data directly from a stored procedure by using xp_cmdshell and running a bcp command on the operating system.

RE: Dynamic ETL with SSIS

jamcarlson — Fri, 22 Oct 2010 07:22:21 GMT

Nice article. Any thoughts on doing the reverse, dynamically exporting data to tab-delim or csv files? I support an MDM db with application specific views. Occassionally we have to export the view results to flat files for ftp outside the organization. I'd like to develop a single SSIS pkg that would create the export files based on the view columns.

RE: Dynamic ETL with SSIS

s.doriss — Tue, 19 Oct 2010 22:32:14 GMT

The format for the table would look like this:[code="sql"] CREATE TABLE [dbo].[Load_RNFile]( [Id] [int] IDENTITY(1,1) NOT NULL, [EverythingElse] [varchar](500) NULL, CONSTRAINT [PK_Load_RN] PRIMARY KEY CLUSTERED ( [Id] ASC)) ON [PRIMARY]GO[/code]

RE: Dynamic ETL with SSIS

shresthadk — Tue, 19 Oct 2010 19:21:16 GMT

Hi SarahWhile testing I found that dbo.[Load_RNFile] is not defined anywhere in your script and am bit lost (or may be am totally wrong) Could you please review and profide the schema of dbo.[Load_RNFile]Thanks

RE: Dynamic ETL with SSIS

SQLRNNR — Tue, 19 Oct 2010 17:02:00 GMT

Thanks - I needed something like this.

RE: Dynamic ETL with SSIS

Geoff A — Tue, 19 Oct 2010 10:52:10 GMT

Its good info. Gives me some new ideas on how to handle text files....I have always used a User Defined Function to get text files into a table.[code="sql"]---------------------------------------------------- USAGE :-- Select line from-- Dbo.uftReadfileAsTable('MyPath','MyFileName')--------------------------------------------------USE [master]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCreate FUNCTION [dbo].[uftReadfileAsTable](@path VARCHAR(255),@filename VARCHAR(100))RETURNS @file TABLE([LineNo] int identity(1,1), line varchar(8000)) ASBEGINDECLARE @objFileSystem int, @objTextStream int, @objErrorObject int, @strErrorMessage Varchar(1000), @command varchar(1000), @HR int, @String VARCHAR(8000), @YesOrNo INTselect @strErrorMessage='opening the File System Object'EXECUTE @HR = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUTif @HR=0 Select @objErrorObject=@objFileSystem, @strErrorMessage='Opening file "'+@path+'\'+@filename+'"',@command=@path+'\'+@filenameif @HR=0 execute @HR = sp_OAMethod @objFileSystem , 'OpenTextFile' , @objTextStream OUT, @command,1,false,0--for reading, FormatASCIIWHILE @HR=0 BEGIN if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='finding out if there is more to read in "'+@filename+'"' if @HR=0 execute @HR = sp_OAGetProperty @objTextStream, 'AtEndOfStream', @YesOrNo OUTPUT IF @YesOrNo<>0 break if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='reading from the output file "'+@filename+'"' if @HR=0 execute @HR = sp_OAMethod @objTextStream, 'Readline', @String OUTPUT INSERT INTO @file(line) SELECT @String ENDif @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='closing the output file "'+@filename+'"'if @HR=0 execute @HR = sp_OAMethod @objTextStream, 'Close'if @HR<>0 begin Declare @source varchar(255), @Description Varchar(255), @Helpfile Varchar(255), @HelpID int EXECUTE sp_OAGetErrorInfo @objErrorObject, @source output,@Description output,@Helpfile output,@HelpID output Select @strErrorMessage='Error whilst ' +coalesce(@strErrorMessage,'doing something') +', '+coalesce(@Description,'') insert into @file(line) select @strErrorMessage endEXECUTE sp_OADestroy @objTextStream -- Fill the table variable with the rows for your result set RETURN END[/code]your method works well also. Thanks....[url]http://www.simple-talk.com/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/[/url]

RE: Dynamic ETL with SSIS

Steve Jones - SSC Editor — Tue, 19 Oct 2010 10:16:37 GMT

[quote][b]Martyn Hughes (10/19/2010)[/b][hr]My comment is not meant to be 'snarky', I'm always troubled when I have to fight within an organization to get useful tools. Pervasive does cost several thousand but the amount of time saved using appropriate tools rather than paying FTEs to 'reinvent the wheel' is certainly a better use of company resources....[/quote]Thanks, and I would agree with you. there are many tools that are worth buying and will help you do a better job with a good ROI. It's good to hear where other tools might fill in a space. My comment was there as it appeared you were knocking this article as a way of accomplishing a task without providing any reasoning. Thanks for the clarification.

RE: Dynamic ETL with SSIS

thumper_disc — Tue, 19 Oct 2010 10:11:46 GMT

I appreciated you article on SSIS. It is timeconsuming to use SSIS in a lot of situations like yours.You might be able to eliminate SSIS completely. You can use xp_cmdshell to run operations like ftp directly on the operating system. You can also unzip. Just build the commands you want.Use insert / exec to pull the directory contents into the stored procedure.Use bulk insert to load data into a database table. The format file option is great for fixed length records and the fastest way I know to load data.

RE: Dynamic ETL with SSIS

Martyn Hughes — Tue, 19 Oct 2010 09:56:16 GMT

Steve, I've been a great fan of SSC for many years, I've used a number of redgate tools and Litespeed to compress some rather large DBs especially when using a SAN. I think Susan made a good contribution to the forum.My comment is not meant to be 'snarky', I'm always troubled when I have to fight within an organization to get useful tools. Pervasive does cost several thousand but the amount of time saved using appropriate tools rather than paying FTEs to 'reinvent the wheel' is certainly a better use of company resources.Pervasive has data connectors for just about anything, even really old 132 column report type stuff! (Only useful if you're dealing with old mainframe stuff)I've used it to convert Progress to Oracle DW, Progress to SQL and vice-versa, Foxpro to SQL. On a smaller scale I used it to convert Quickbooks to SQL.SSIS is certainly better than the old DTS but far behind 3rd party tools.

RE: Dynamic ETL with SSIS

amenjonathan — Tue, 19 Oct 2010 09:11:26 GMT

[quote][b]Nitya (10/19/2010)[/b][hr]The assumption that files coming in are of fixed length is little hard to generalize.In that case the Configuration table values are hard to determine.Great work in getting it up and running[/quote]It's not a generalization if your suppliers have given you a data dictionary. Many of our data suppliers do the same. The files they give us are always formatted with these fixed field lengths in mind, which we must then TRIM before using properly. The only difference in our packages is we handle erroneous row lengths in the package.Very interesting way of importing flat files. We also build specific packages for each of our files coming in from different suppliers, and it's a real pain. SSIS can handle a lot of different scenarios, but IMO it's not a simple enough process, as you have mentioned.Good article. ... Need to think about the pros and cons of adopting it here. Thanks for sharing!

RE: Dynamic ETL with SSIS

ranjith_s 65449 — Tue, 19 Oct 2010 08:54:52 GMT

Thanks and nice work Sarah! But can you update it with link to source code. I know all the SQL scripts are in the content, but we need SSIS files too. Thanks in advance!

RE: Dynamic ETL with SSIS

Steve Jones - SSC Editor — Tue, 19 Oct 2010 08:38:04 GMT

[quote][b]Martyn Hughes (10/19/2010)[/b][hr]I don't know why you'd want to go to such lengths to use SSIS, just use Pervasive (formerly Data Junction). It's a far superior way of getting data into SQL.[/quote]Not everyone can afford third party tools, or get the approval to buy them. Knowing how they could perform this in SSIS is a piece of knowledge that many can benefit from.If there's a superior way to use Pervasive, perhaps you would want to mention specifically why rather than just add a snarky comment.

RE: Dynamic ETL with SSIS

Nitya — Tue, 19 Oct 2010 08:10:26 GMT

The assumption that files coming in are of fixed length is little hard to generalize.In that case the Configuration table values are hard to determine.Great work in getting it up and running

RE: Dynamic ETL with SSIS

Virendra Chauhan — Tue, 19 Oct 2010 07:38:14 GMT

Great work, if you have a spare time to do so. I think SSIS is designed to take care the work done in the back end. Storing the file name and others make sense but rest is really confusing for anyone who have to maintain or troubleshoot the package. I would say simple is better.

RE: Dynamic ETL with SSIS

Samuel Vella — Tue, 19 Oct 2010 04:38:24 GMT

[quote][b]Martyn Hughes (10/19/2010)[/b][hr]I don't know why you'd want to go to such lengths to use SSIS, just use Pervasive (formerly Data Junction). It's a far superior way of getting data into SQL.[/quote]I agree with you to a point but there are more elegant ways of doing this in SSISSarah, you mention that SSIS requires a flat file format to load the data properly in to SQL Server... so why not create the bulk load format file on the fly using a script? its fairly easy to parse the file and determine number of columns, data types etc. and then build the format file and the destination table based on that information.

RE: Dynamic ETL with SSIS

Martyn Hughes — Tue, 19 Oct 2010 03:34:02 GMT

I don't know why you'd want to go to such lengths to use SSIS, just use Pervasive (formerly Data Junction). It's a far superior way of getting data into SQL.

Dynamic ETL with SSIS

s.doriss — Mon, 18 Oct 2010 22:20:52 GMT

Comments posted to this topic are about the item [B]Dynamic ETL with SSIS[/B]