Need guidence to build a SSIS package (its urgent)
-
Hi all,
basically i'm a .net developer and i'm interested in building a ssis package, using which i've to transfer data from source A to source B(sql server to sql server), i've some 10 tables in my source and same in destination too, hw shall i go ahead ? i'd done googling regarding the development of the package, and i've designed a package which is now running successfully , and it is able to transfer only data between single table. I want to transfer between multiple tables. And also most important thing is i've used a configuration file (xml) which contains the details about the source database name, destination name, uid, pass, but i'm not getting hw to make use of the configuration file. i'm executing the package from the .net application.
can any one put some light on how to transfer the data between multiple tables??? and also how to make use of the xml configuration file?????
Thanks in advance...
Murali....
*He that is good with hammer tends to think every thing is a nail*
-
For using of the XML file go to Menu SSIS -> Package Configuration -> Add..
If you want to transfer multiple table you can make use of Import/Export wizard...then save the package at the last step...and then configure it to use the XML configuration file.
Import/Export wizard -
Right click on DB -> Tasks -> Import Data/ Export Data.
-Vikas Bindra
-
Hi vikas thanks for your reply...
i've created the xml file, and followed the steps to configure the file, but when i execute the package from my .net application, it is not accesing the servername(src name), dest name, user id and password.
could you please give me the steps which i need to follow in order to access the configuration settings from the xml file...
coming to the second question, i want to build the package which should allow user to select multiple tables in the source and map them, so that he can transfer the data from source to destination.
once again thank you very much for ur reply....
*He that is good with hammer tends to think every thing is a nail*
-
i've created the xml file, and followed the steps to configure the file, but when i execute the package from my .net application, it is not accesing the servername(src name), dest name, user id and password.
Does the package accesses the file when you run it from VS IDE?
i want to build the package which should allow user to select multiple tables in the source and map them
Map the source table to single table in destination? or to multiple table?
If you want to map multiple table joins to single table then in the source of data flow task(DFT) you need to write a SQL query instead of selecting the table name from the drop down.
If it is a one to one mapping then you need have multiple DFTs one for each table.
I hope you know how to create a DFT...
-Vikas Bindra
-
Does the package accesses the file when you run it from VS IDE?
No, its is not accessing the configuration file, when i run the package it is displaying the below error msg...
check this out and plz guide me how to access the configuration file parameters...
*He that is good with hammer tends to think every thing is a nail*
-
Where is the error message?
-Vikas Bindra
-
i'm sorry i'd uploaded the image but got lost... here is the error message.....
Information: 0x40016041 at Package: The package is attempting to configure from the XML file "C:\Documents and Settings\murali\My Documents\Visual Studio 2005\Projects\MYETL2\MYETL2\setting 1.dtsConfig".
Error: 0xC001000E at Package: The connection "1APWN0001.test.dbname" is not found. This error is thrown by Connections collection when the specific connection element is not found.
Warning: 0x8001F02F at Package: Cannot resolve a package path to an object in the package ".Connections[1APWN0001.test.dbname].Properties[InitialCatalog]". Verify that the package path is valid.
Warning: 0x80012017 at Package: The package path referenced an object that cannot be found: "\Package.Connections[1APWN0001.test.dbname].Properties[InitialCatalog]". This occurs when an attempt is made to resolve a package path to an object that cannot be found.
Error: 0xC001000E at Package: The connection "1APWN0001.test.dbname" is not found. This error is thrown by Connections collection when the specific connection element is not found.
Warning: 0x8001F02F at Package: Cannot resolve a package path to an object in the package ".Connections[1APWN0001.test.dbname].Properties[Name]". Verify that the package path is valid.
Warning: 0x80012017 at Package: The package path referenced an object that cannot be found: "\Package.Connections[1APWN0001.test.dbname].Properties[Name]". This occurs when an attempt is made to resolve a package path to an object that cannot be found.
Error: 0xC001000E at Package: The connection "1APWN0001.test.dbname" is not found. This error is thrown by Connections collection when the specific connection element is not found.
Warning: 0x8001F02F at Package: Cannot resolve a package path to an object in the package ".Connections[1APWN0001.test.dbname].Properties[Password]". Verify that the package path is valid.
Warning: 0x80012017 at Package: The package path referenced an object that cannot be found: "\Package.Connections[1APWN0001.test.dbname].Properties[Password]". This occurs when an attempt is made to resolve a package path to an object that cannot be found.
Error: 0xC001000E at Package: The connection "1APWN0001.test.dbname" is not found. This error is thrown by Connections collection when the specific connection element is not found.
Warning: 0x8001F02F at Package: Cannot resolve a package path to an object in the package ".Connections[1APWN0001.test.dbname].Properties[UserName]". Verify that the package path is valid.
Warning: 0x80012017 at Package: The package path referenced an object that cannot be found: "\Package.Connections[1APWN0001.test.dbname].Properties[UserName]". This occurs when an attempt is made to resolve a package path to an object that cannot be found.
SSIS package "Package.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Error: 0xC0202009 at Package, Connection manager "1DTWN0023.AdventureWorks.sa": An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'sa'.".
Error: 0xC020801C at Data Flow Task, OLE DB Destination [16]: The AcquireConnection method call to the connection manager "1DTWN0023.AdventureWorks.sa" failed with error code 0xC0202009.
Error: 0xC0047017 at Data Flow Task, DTS.Pipeline: component "OLE DB Destination" (16) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Data Flow Task, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
SSIS package "Package.dtsx" finished: Failure.
*He that is good with hammer tends to think every thing is a nail*
-
Do you have a connection manager with the name "1APWN0001.test.dbname"?
Please post the config file also.
-Vikas Bindra
-
Hi vikas thanks for ur interest dude...
i've made it work... actually i was missing the password value in my xml....
once i updated the xml file with the password it worked... but i still its not the correct way to code the database password in the xml file, is there any other alternative?
in ur earlier posts u'd mentioned to transfer the data between multiple tables we need to design multiple DFT's is it so, or is there any other machanism to acheive this?
*He that is good with hammer tends to think every thing is a nail*
-
vikas i've one more doubt...
if i have connection managers for both source and destination, then wt is the use of xml file???
bcoz basically while v r adding the connection managers we'll have to give the server name, database name, user id, password, table name etc and also same for the destination also.
then where is the role of xml?? wt i'm thinking is i dont want to give the details about the source and destination in the connection manager.
and when i run the package then it has to read it from the configuration file(i.e XML)
am i ri8??? or hw is it done actually?
i'm stepped in to ssis and i'm a beginner u might find my queries bullish, please correct me if i'm wrong any where....
*He that is good with hammer tends to think every thing is a nail*
-
Creating different DFTs is one way to achieve multiple table transfer. And this is the simplest one.
The other way to have a loop and do extract and load by dynamically changing the name of the table in each step as the loop continues.
You can keep the name of the table to be exported in a metadata table then BCP out all the tables into files in one loop and then load the data in the destination tables from BCP outed files in the next loop. But this is little tricky one.
If i have connection managers for both source and destination, then wt is the use of xml file???
The XML files comes in picture when you have different Dev server, different Test server and different production server. If you have the server name in the XML file then you don't need to open the package and change the connection manager every time you change the environment.
SSIS is a good tool to learn [:)]...nobody minds the questions...
-Vikas Bindra
-
Thanks dude...
nw i'm able to run the package from my code, the problem is even after i change the destination server name and database name in the xml file, the data is being transferred to the same previous table, i.e the old table, the one to which the connection manager settings are done in the package, why is it so?? as u mentioned in ur previous reply, if i change the server name, database name i shld b able to transfer the data successfully na? what is the wrong with my procedure??
*He that is good with hammer tends to think every thing is a nail*
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply