February 19, 2008 at 9:01 pm
Following this post I am starting the following one, which is kinda the same thing but even more complicated.
We have hundreds of files that need to be transferred from say AS400 onto MSSQL2005.
Both source and destination tables are quite simple, there is no transformation performed, etc.
Currently we have these files transferred by DTS packages, grouped logically in bunches, and spawning hundreds of DTS packages calling eachother, hundreds of error logging scripts etc.
Now we are about to rewrite it in SSIS. As far as I understand the approach in this respect has not changed in SSIS -- we would still have to have millions of DTSX packages doing almost the same.
Is there any othe way of looping over the list of certain tables and just transferring them over "as-is" to the local database without having one DataFlow pair source-destination per each table?
Cheers,
VAL
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
February 20, 2008 at 8:26 am
February 20, 2008 at 3:11 pm
Not quite. Your method would do file->db or db->file transfer. I need db->db transfer and one of the servers is not M$. Ok. Imagine you have an AS400 box with hundreds of tables and you need these tables on your 2005 box every morning. No transformation, no filtering just tables as they are. What would you do? Create hundreds of dataflow pairs or try to create an intelligent loop?
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
February 21, 2008 at 5:08 pm
Well, you did not say why you want to rewrite them into SSIS. Typically, if these DTS packages are simple and you are not looking ways to improve them, the no brainer way is to directly call them from SQL2005.
February 21, 2008 at 5:26 pm
Valid comment, but it is not my decision to get rid of 2000.
Having legacy atavisms sitting in there and stuff is also not good for me who will be looking after the ETL from the migration on, and also in the future when we go for 2008.
You are right -- leave the beast in there if it works. It is not urgent but I am certainly looking into some better ways to do the same using the better (?) and fresher technology, which is at least supported and not called "legacy".
The other reason is that all the new ETL stuff will be developed in SSIS and a question of re-writing will become the question of the day pretty soon
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
February 21, 2008 at 7:02 pm
Gosh... can't you just make a linked server and write a proc that would maybe use a little dynamic SQL to just copy everything it sees on the linked server to tables using "SELECT/INTO"?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 21, 2008 at 9:12 pm
I am thinking about it, but the management would argue that I am distorting/abusing the concept of ETL, SSIS and all the rest of it.
It would be great if it was available in SSIS. Otherwise I could write my app in .net which does all this stuff and, which has no relation to SSIS.
The reason why I am raising the question is that I came to the company to convert from DTS to SSIS and these people have about 400 DTS and I wanted to figure out if its possible to do looping across the similar tasks rather than span packages.
Apparently it is not possible within SSIS.
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
February 21, 2008 at 9:46 pm
If they are currently working, why not just continue to run the DTS packages under SQL 2005?
DTS 2000 runtime is included with SQL Server 2005 to run DTS 2000 packages without upgrading them.
February 22, 2008 at 5:44 am
VALEK (2/21/2008)
I am thinking about it, but the management would argue that I am distorting/abusing the concept of ETL, SSIS and all the rest of it.It would be great if it was available in SSIS. Otherwise I could write my app in .net which does all this stuff and, which has no relation to SSIS.
The reason why I am raising the question is that I came to the company to convert from DTS to SSIS and these people have about 400 DTS and I wanted to figure out if its possible to do looping across the similar tasks rather than span packages.
Apparently it is not possible within SSIS.
Dunno... I loops were possible in DTS... I don't see why they wouldn't be in SSIS. My problem is, and I guess I should apologize for getting involved in this thread, is that I do all of this stuff through T-SQL and I don't really have the capability to describe exactly how to do it in DTS or SSIS... only that I've seen others do it quite successfully.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 22, 2008 at 7:16 am
Is SSIS a requirement? If not, why not use bulk insert to load the data. For our environment, we have a table that has the file name to look for and the table name it should populate. From there, we just run a cursor to dynamically build the bulk insert statement. If SSIS was a requirement, you could use the same table I mentioned earlier to populate a collection then use the bulk insert task in a foreach loop to load the data.
J.D.
February 24, 2008 at 3:36 pm
J.D. Gonzalez (2/22/2008)
Is SSIS a requirement? If not, why not use bulk insert to load the data. For our environment, we have a table that has the file name to look for and the table name it should populate. From there, we just run a cursor to dynamically build the bulk insert statement. If SSIS was a requirement, you could use the same table I mentioned earlier to populate a collection then use the bulk insert task in a foreach loop to load the data.J.D.
We load from one server (AS400) to another (MSSQL2005). Bulk insert works with flat files.
We dont want to involve flat files for it will increase the timing and introduce another complexity.
ssis is a requirement, yes...
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
February 24, 2008 at 3:41 pm
Jeff Moden (2/22/2008)
Dunno... I loops were possible in DTS... I don't see why they wouldn't be in SSIS. My problem is, and I guess I should apologize for getting involved in this thread, is that I do all of this stuff through T-SQL and I don't really have the capability to describe exactly how to do it in DTS or SSIS... only that I've seen others do it quite successfully.
Looping is not a problem. The problem is to perform a repetitive task within the loop.
How do you automatically map the import-export fields in the dataflow task??
People say it is not possible.
What I am essentially asking is this:
1. our as400 server has lets say 400 tables. they are on an as400 box
2. our mssql database has also 400 tables
3. we need to load the info from those 400 tables onto mssql every night.
4. the tables are simple, no transformation is performed..
A known messy solution is -- to span 400 dataflow tasks and run them.
Now question: is it possible to make a loop which would do this automatically, since maintaning several hundred dataflow pairs source-destination is difficult.
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
February 24, 2008 at 5:00 pm
Ah... I see. I was assuming that there were files involved. I've never worked on an AS400, but I'm interested on how you solve this problem as I may have a project similar to yours in a few months.
That being said, I haven't had any luck creating dynamic mappings.
J.D.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply