i need to transfer the data from allbase (old database) to sql server 2005 .
moreover the structure is different say in allbase we may have 10 tables but now we want more tables .and some tables may have more fields than the previous table's fields for ex: productdigit (previous table name) have 2 fields and present table productline have 4 fields (while using export/import wizard those are set to null but we dont want to set null).
and i dont have knowledge on ssis now i am learning it. but i could not understand how to use those items in toolbox and how to specify datasource for odbc and all.
and please help me to create custom component if needed.
thanks in advance
B L,
I would reccomend you to go to books on line; there are some tutorials that will help you on getting started. Getting some books will help you as well. Then you can come back with specific questions/issues.
http://msdn2.microsoft.com/en-us/library/bb418492.aspx
|||thanks for immediate response .
we have different data flow sources in toolbox but there is nothing for odbc .
If i want to connect to odbc what item should i use?
and for transferring data from source to destination which item i should use in in data flow transformation.
do i need to write any script if so can i write c# instead of vb.net wt property i need to set
|||Few things:
Toolbox is context sensitive; it will show a diffrent set of tools depending on where you are: control flow or data flow To pump data from an ODBC source: Create a connection manager that points to your ODBC source (DNSmust be define in the machine running the package) Create a data flow task in in the control flow In Data flow task add a data reader that uses the connection manager you created add a destination component COnnect the source component to the destination component.|||thanks for valuable suggestion .
for transferring data from old database to new one when the structure is different means
--> the number of tables / name of tables is different.
--> the structure of table is also different say the old table contains n number of columns and the new table may contain either n+ or n- number of columns and some columns in new table might not be there in old table .can i have new columns as primary?
can u guide me in that way and can you tell me which items i should use in data flow task.
|||hello,
i have data in old database which contains many tables and i want to retrive data from two tables and insert that data into one table in the destination by SSIS package.
for example:
input output
- --
userinfo ( table ) userdetails ( table )
user_id --> id
userdata ( table2 )
name --> name
city --> city
can i do like that? please suggest me to do so .
|||There are several ways. My first approach would be to write a query (IF the 2 source tables are in the same DB); then use that query in the source component. That way the dataflow will have all required columns right out of the source component.
Another apporach is to use 2 source componnets and then a Merge Join transformation to join the 2 data sets. This approach requires the 2 data sets to be order before hiting the merge join. Performance can also suffer with this approach since the Join takes place inside SSIS rather than in the DB engine.
|||
B L Rao wrote:
do i need to write any script if so can i write c# instead of vb.net wt property i need to set
The script task and script component only support VB.NET, not C#.
You can build your own custom SSIS component DLLs using any .NET language, but this adds a LOT of complexity.
|||hi Rafel Salas ,
thank you for your reply by which i got some idea ,
yes we have those tables in same DB.
but still some confusion in that first approach that is where to write that query and if possible what query i can write .
so plz give me clear idea .
and second approach is clear to me .
thanks and regards
B L Rao.
|||Just write the query that gives the desired output and then you can place it directly in the OLE DB source component by changing the data access mode to "SQL Command". Another option is to place the query in a SSIS string variable and then use data access mode "SQL Command from variable"|||hi,
The approaches are best suitable in the scenario where we can make join between two tables. Means when we have one common column on the basis of which we can create join.
But what should we do if we want to fetch data of column1, column2 from table 1 and data of col5 from table 2 without making join.
For example
table 1 has
column1
column2
column3
table 2
col1
col2
col3
col4
col5
I have tried Union All task, and Merge task but it returned 2 rows like following
null,null,col5
column1,column2,null
but output through SSIS in destination table 3 should be combination of three columns in one row like this :
column1,column2,col5 .
can you please help as soon as possible?
if possible can u please send a small package or query for this?
thanks & Regards,
Hariax
|||Try creating a dummy column (like a simple sequential row number) in each table to join on and use the Merge Join.|||Hi,
Please help me, I am getting the 'DTS_E_UNABLETORETRIEVEMETADATA' error while I am performing bellow mentioned steps.
1. Created 'Execute Sql Task'
2. In the editor of this task, General Page ->Sql statement, I wrote create table query.
3. I tried to execute the package, and got following error.
ADDITIONAL INFORMATION:
Error at Data Flow Task 1 [SQL Server Destination [376]]: The metadata for "[dbo].[temptable]" cannot be retrieved. Make sure the object name is correct and the object exists.
Error at Data Flow Task 1 [DTS.Pipeline]: "component "SQL Server Destination" (376)" failed validation and returned validation status "VS_ISBROKEN".
Error at Data Flow Task 1 [DTS.Pipeline]: One or more component failed validation.
Error at Data Flow Task 1: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
BUTTONS:
OK
If anybody has the solution of this then please let me know.
same query is running successfully from sql server management studio.
Thanks & Regards,
Hariax J. Thoria
|||If you are creating the table for the destination in the same package, make sure the DelayValidation property on the SQL Server Destination is set to TRUE.