January 4, 2008 at 6:41 am
🙂
Happy New Year!
Hi, This is Alok Saxena, looking and start a debate on topic "Imports Through Sql into sql server".
We have following options to import:-
1) DTS Package
2) SSIS
3) Store Procedure updating master tabler from parent tables(another Database).
My question are
1) What are the points must keep while creating Import.
2) Database Design issue
3) Should we first drop master table before importing Data in it. If yes then what we do if these master having some relation ship with other tables.
January 4, 2008 at 6:59 am
Some of your questions aren't technical in nature. For example, what to do about data already in the "master table". That depends on what data is in it and what that data is being used for, if anything. It's more of a business decision than a technical one. At least, that's how I'm reading it.
With regard to how to move data around, I like using procs/scripts to move data from one SQL database to another. Generally easier for me than SSIS, et al. I've also used DTS and SSIS for that, depending on the complexity of the data move and whether I had to do anything outside of SQL server for any of it. But I generally stick to using T-SQL for moving data between instances of SQL server.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 4, 2008 at 7:28 am
Thanks for Quick response!
I agreed with you.
I tell you, I also having same opinion. However I am locked with clients and our Database desgin. Client looking for SSIS, becuase They have data warehouse in Sql server 2000. and they want to import four tables from dataware house in xyz database Sql Server 2005. These four tables are master table in XYZ database added two more cloumns. As master tables (XYZ Database) are used FK constraints. So If I used SSIS
Step -1 drop constraint
Step- 2 drop tables/delete data,
Step-3 Excute SSIS Package
Step 4 Create Constraint.
Step-5 Update DataImport table (Used to update import status,datetime)
Let's example
*Dataware House
A)Dw1 Table columns :-
1) EMP_CODE
2) EMPNAME
*XYZ Database
B) Mast1 table Columns
1)ID (its PK + Identity) used for FKconstraint
2)EMP_CODE
3)EMPName
4)LastUpdateTime
C)DataImport Table Columns
1)ID
2)LastUpdatetime
3)ByUser
What do you think which options we should opt, I just tell you one more thing Application alredy completed except this import module.
We used C# ,Net3.0 and Sql Server 2005
January 4, 2008 at 2:29 pm
If this isn't something that's going to be done repeatedly (it'll only be done once as part of a migration from SQL 2000 to SQL 2005), I'd do the whole thing as a single script, unless we're talking about some truly huge number of records.
Is this in the range of hundreds of records? Thousands? Hundreds of thousands? Higher?
If it's a huge number of records, like hundreds of thousands, and a slow connection, there might be some advantages to using SSIS, because of the error handling.
If it's a few thousand (less than 100,000) records, and it's a reasonably fast connection (LAN or same server, for example), I'd just use a script.
The data (from the brief sample you gave) looks too simple to mess around with SSIS/DTS. Just "insert ... select ..." should do it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2008 at 3:07 am
HI!
What we do If CLients looking desprately SSIS. So please provide me some scripts if u have in C#+Asp.net
Regards
January 8, 2008 at 7:42 am
Since I'm not a C# programmer, I can't help with that part. I'd say just open up the Business Intelligence Studio and create an SSIS package and start working your way through it. Drag a Data Transformation object on there, dig through help files and go from there. That's how I learned how to use SSIS.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply