blogs.conchango.com

Hello! I found out your blog extremely useful for me. Thank you very much. But I have a question. Could you answer me please? What is better to use SSIS package, Replication or T-SQL if I need a copy of table (actually, only some columns) on the other linked server; I'm going to update the copied table requrently (e.g. every 15 minutes) . The copied data will be used only for read (for join), but the table with copied data must be available for the queries all the time. Actually, I have a several such tables with 100K rows each. Which way do you prefer?

How about the performance impact on the source server, since the StoredProc's have to do all the work on the server itself, while the data flow can be pulling data (async, as per your last post) and doing the heavy lifting on a different machine?

While I agree that the learning curve for SSIS can be steep, I think the benefits of using SSIS in the ETL far out weigh the few benefits that you listed for stored procedures. So, personally, I have to vote for SSIS.

>how about the performance impact on the source server
Yes, but I can pull data from the destination server. Actually, I keep an open mind about; I want to find the best solution for my task. :)
>So, personally, I have to vote for SSIS.
Thank you very much. I've selected SSIS. In any case, it will be useful to study this service.
Regards,
Roman

I disagree with some of your points. While SSIS can eliminate a staging area, often this is a bad idea: we tried to use the no-staging approach importing a lot of XML log files and at the end we decided that it was a bad idea. Error handling, debugging and complex transformations are a lot easier when you have the source table in a relational store.
SCD components are another gray-area of SSIS, we improved performance eliminating SCD components with our custom-made components and/or scripts.
But at the end we definitely like SSIS against stored-procedure transformation: SSIS are a lot easier to manage when they become complex, and documentation is a lot simpler with SSIS graphical paths; if you see ETL development as a thing that needs maintenance after months or years, you definitely will appreciate SSIS visual representation of the "work".

Hi Jamie,

I understand you were not recommending, my use of english could be not very appopriate sometimes... You don't have to excuse, don't worry!

I'm currently involved in several projects and I'm dedicating my spare time to a document about SSAS modelling techniques based on many-to-many relationship. I will try to post more, I promise :-) , we had some experience with processing large XML files that worths to share.

Hi.

Marco

I didn't see listed on the SSIS side what I consider to be TWO huge benefits of using SSIS vs. T-sql: Structured Exception Handling, and Free Auditing/Logging.

Granted if you change all your SPs to .net code as you now can in 2005, you do get the structured exception handling- but then again, that is not t-sql...

Hi Jamie

I have to do a data migration task from an old db to a new db.The schema of the new db is different. Eg a table in the old db that had a primary key (say id) is now split into 2 new tables tbl1 and tbl2. Also the old table had say 10 columns which need to be split among the 2 new tables (master and some child tables with id as a reference key.) Also some columns in the old table are no longer required in the new tables and some new columns are introduced in the new tables.

I have read up on SSIS and i feel that my task does not require much data transformation. I just need to insert data into the new database.

I dont find any particular task in SSIS that will help me significantly as opposed to write an SP or script and include in it several insert statements.

It would be really helpful if u can guide me whether i should be using script or use the SSIS for this task.

If you recommend using SSIS, then what are the tasks i should be using (eg.dataflow task or Execute SQL task)

The databases are on two different servers and this migration will be a one time activity.

Thanks,

Dev

Hi Jamie

Thanks for the reply.

Nice post. Thanks.

Really important.

How about a case where it will be better to use a stored procedure as a source for a Data flow. I do not find a way to do this with SSIS. Any ideas.

Thanks

Not being a DTS dweeb at the start of my BIS project, I used VB.Net to run a bunch of stored procedures. This turned out to be a good thing when I had to put a variable in the FROM statement of several procedures. I just built the procedures from inside VB, passing in the variable I needed.

I've read elsewhere about ETL systems in general. The general problem is that they handle generic problems well but specific, painful, beat your brains out problems not so well.

I'm going to use SSIS for my incremental loads. I'm looking to use the SPs from the VB loader. That's a quick and easy solution. I'm not sure how much of the SP code will be converted along the way.

1. The performance issue is a major one.  I was involved in a project where we were pulling data from AS/400 to SQL-2005.  The data was to be fetched into a staging area first.  SSIS job never finished but the stored procedure finished in 15 minutes.  The basic reason being that SSIS fetches data then transforms  whereas using T-SQL you can do select * into from <source> - and that will out perform SSIS greatly.  This will remain the case until SSIS is optimized to look at the logic and translate it into direct SQL calls.

2. Multiple data sources - Here too I find standard SQL approach of using linked servers much easier.  With SSIS data flow tasks the connection information was embedded in the task and that causes issues when moving stuff from dev to prod.  With linked servers that is an outside entity and the movement between environments was easier.

3. I have not tried it yet, but SQL-2005 allows stored procedures to be written using .NET languages as well.

SSIS is definitely a good thing as long as the user employs it properly.  Its sequencing and documentation features are great but if someone thinks that they can do a real data warehousing project without needing SQL/T-SQL - they would be disappointed.

Hi Jamie,

How about implementing a cursor inside a data flow, is that possible? I would like to do arow by row processing and based on conditions update or insert into tables. How would I go about doing that? Also I personally I feel that if u have to implement case logic in ur transformations it would be easier to do that in TSQL rather than in data flows. But the above comments are really helpful.

Thanks

Hi Jamie,

I once tried implementing case logic using SSIS, sometime back, but didnt find any transformations within data flow that would do it, finally I had to give up and ended with using Tsql to do that, may be there is a way but im not aware of it. would be helpful if you can let me know.

Thanks

Hi Jamie,

I havent tried that yet, will do so soon, one question I had was in the Derived Column or Conditional Split tranformation I dont see any expression for case, will I have to use something else instead of case? how do I go about doing that, can you please point me to an example , im sure there must be something that I can use, thanks for your input.

Thanks

Hi Jamie,

Thanks for the input, I tried it and it works great, may be the only reason I never tried it before was that I was just being lazy or just ignorant, I think this is really kool and thanks for the enlightenment :) .

Thanks

Anyone know if I'm missing something on the dataflow task here?

I've got a source csv file with no header row and around 150 columns to go into a SQL database. The SQL database has all the datatypes properly defined and the column order is in the same order as the csv columns.

I had 2 major annoyances whilst setting this up in a dataflow task.

1) I could not see any way of mapping the csv columns to the database columns by index. I had to do it all manually by pressing the down key and the enter key 150 times in the mappings box.

2) I had loads of validation errors where the inferred datatypes for the CSV flat file connection manager did not convert to the mapped SQL datatype. What I wanted at this point was a way of setting up the CSV datatypes from the mapped SQL data types but I could not see a way of doing this and again had to do it manually.

Could I have avoided the manual approach and done either of these things automatically?

This query helped save my sanity and make things a bit less tedious when manually setting up field lengths where the inferred field length was shorter than I actually wanted to be allowed. I found the whole user experience of setting up data flows unbelievably poor. Please tell me that I am missing something here.

declare @tBLaLL TABLE

(

RANK INT IDENTITY (0,1),

length INT,

xtype INT

)

INSERT INTO @tBLaLL

(LENGTH, xtype)

SELECT length,xtype  FROM syscolumns

WHERE Object_name(id) = 'Fidessa_TRADING_POSITIONS'

order by colorder

select * from @tBLaLL where xtype = 167 and length>0  

order by length, rank

"Secondly, if you are using configurations (which you ALWAYS should be) then the connection info is taken outside of the package and moving through environments is a breeze."

This is surely some kind of joke. Although package configurations are much better than trying to alter all your configuration by hand when deploying to a new environment, there's no way anyone could reasonably call them 'a breeze', given that there a nightmare to get working, often stop being loaded for no apparent reason when you deploy to a new machine, and more importantly don't really cope well with the (fairly common) situation where you have multiple environments on the same machine. (try setting up an indirect configuration when the only thing you have available to indirect with is a machine wide property(i.e. environment variable or file in known location). It can all be dealt with sure, but only after a lot of head-wall interaction.

Hi Jamie,

Yes the component is incorrectly guessing field lengths and data types.

As this is being imported into a SQL database and the columns are mapped what I wanted was a way to set the flat file datatypes to correspond with the SQL datatypes according to the mapped columns rather than having to set up the whole thing manually.  

I found setting up the String Lengths in the flatfile connection manager mind numblingly tedious to do when dealing with large numbers of columns but at least the above query allowed me to order the DT_STRING columns so those with the same length were next to each other and I could select them all in the UI using the CTRL key.

Now I see that I have another problem though where numbers are being rounded due to incorrectly guessed data types so it looks like I'm going to need to manually review every numeric datatype as well in 10 different CSV files with up to 150 columns. *sigh*

Hi Jamie,

Does the scenario (Data Flow vs T-SQL) change if you are on a 64-bit platform (both OS and SQL Server) and you have 8GB RAM (with a possibility of adding more RAM if need be)?

I suppose Data Flows use memory as against T-SQL using the physical disk and accessing memory would always be faster than accessing physical disk.

Thanks

Sumit

Hi Jammie,

My thoughts which might bring smiles to TSQL Lovers Vs SSIS.

you mentioned.

Advantages of using a SSIS data-flow

Handle data from heterogenous sources in the same place

You could do this with TSQL too with SQLCMD(New built in utility from 2005.)

Consume data from sources that can't be accessed using a SQL statement

There is nothing like this.if SSIS can access, TSQL can also access it. just matter of knowing the proper linked server kind of aprocach or DOS commandline utility and parameters so that u can accesss it with combination of SQLCMD again

Data can be transformed without the need for an intermediate staging area

I think people have already discussed about his.not a good idea all the times. just to add to that, you could use table variables & Temporary tables to get the same effect of avoiding Staging

If data does need to be persisted temporarily it can be to a raw file whereas T-SQL requires temporary tables for which there may be a management/security overhead

i dont know how SSIS does this, but i guess it uses table variables only to get it but then question is why microsoft not lifting out the concept of temporary tables altogether when they can not server our needs in any case by becoming over head.

i think temporary tables are very useful if u use them properly. u can put indexes on them to get performance.

just open thought.

Extra transformation functionality (e.g. Fuzzy logic, data mining, text mining, insert to Analysis Services)

This is just buildup.no body would do this in automation.but definitely helpful just during development but not in once gets into production.

Visual representation of the "work"

100% agree

Bad data can be captured to a different data sink for examination later

I never tried this.but good thought but also could be done in TSQL.

Exception Handling

TSQL also has exception handling.try/catch blocks

Use .Net Data providers as an alternative to OLE DB Providers (e.g. mySAP Business Suite .Net Data Provider)

just verify how SQLCMD works if on OLEDB or .net providers to connect to heterogeneous data sources.

Data-flows are, to some extent, self-documenting

good agreed.but TSQL is easy to manage technically.

User-controlled parallel execution of data-flows is possible where it isn't really in the inherent batch operated world of stored procedures

not to object, but parallel processing /batch procesing are trade offs.depending on situations most of the time batch processing is realiable.

"Heavy-lifting" of data can occur on a different to machine to that storing the data (thanks to John in the comments section)

i did not understand this to comment.

Cheers

Sundar

This post was made in 2006... Its Jan 2008 and two people next to me are talking about this topic right this second :).

I find SSIS to be a good way of doing data imports but like anything else its a tool and many approaches might work. The best approach is always arguable and depends on the details.

What I don't like is switching technologies in the middle of a project when the differences are arguable details.

TSQL  rules !

Change Management – it’s not easy to check  impact changes with SSIS implementation. In very complex project SSIS (many packages) and even well documented SSIS project is hard to recognize all dependent  tasks. In TSQL I only need query dictionary table to find potential objects, it’s beautiful !

Hi,

If possible can anyone please provide me some idea on this.

I have an Oracle 8i DataSource. And I have to migrate data from some 100 tables with some containing around 1 million & some with 1 billion records to an SQL Server 2005 database. So how should I proceed with this. Whether using a Linked Server and some T-SQL statements would solve this, or an SSIS package would be more useful.

Please provide me the details of how the usage will increase my performance and minimize time.

One question - does SSIS need linked databases to enable the data transfer directly between one SQL2005 database and another?  We are trying to migrate PROD data back to TEST and the DBAs wont allow linked databases outside PROD. We had written T_SQL to do the migration but relies on linked DB.

After facing the task of setting up an SSIS project for 350 pipe delimited flat files, I may have found a way to make setting up Flat File Connections slightly less painful.

Start by doing it in reverse by creating an OLE DB Source on the SQL table and a creating a Flat File Destination.  That way the Flat File Connection will be based on the table instead of on a guess at the file contents (and setting all the strings to 50!).

So just save your Flat File connections and use them with your real Flat File Sources.  Throw away the rest.

You still may have to tweak a few things but it's much faster.

I have to import some data from oracle to SQL and i know i can do this using ssis. Once the data is imported I need to do some cleanup of the data. Currently i do it using a storedproc which has a bunch of update statements. I know i can execute a stored procedure from ssis. So which method is better / faster ? 1) execute the stored proc from ssis after importing data. 2) in the dataflow task, do some transformation to clean up the data?