SSIS uniqueidentifier column import into sqlserver2008 table
-
Hello!
I have a column with uniqueidentifier datatype in sqlserver2000 table, I hav to perform a few functions based on that column n bringing that data in to sql server 2008 table(including the uniqueidentifier column). For this I have created a table in sqlserver2008 with the necessary columns and also included a column with the datatype uniqueidentifier. Now the problem is, when I am executing the query in management studio... it is giving me the column with the uniqueidentifier value (lets say the column name is scode_id). but when I am using the same query in "OLE DB Source".... there are only null values beinging displayed for the scode_id column.
The actual scode_id column values are some thing like this:
SCODE_ID
8FA29DFA-E5DC-4414-907E-8C153F737DA1
A631601D-1B7A-4968-9028-E7C9A352D543
8FA29DFA-E5DC-4414-907E-8C153F737DA1
A631601D-1B7A-4968-9028-E7C9A352D543
A631601D-1B7A-4968-9028-E7C9A352D543
--------------
I am getting the above values when I run the query in management studio.... but when I use the same query in SSIS Dataflow Task.... I get only null values for the scode_id column.
Can some one let me know where I am going wrong, or how to fix the issue?
I am using SSIS 2008.
-
From what I understand, you might need a data conversion task between your source and destination in the package to make sure that the datatypes are the same.
The other approach you could try is setting up a linked server between the two and just running a simple insert query after you done your computations.
-
Thank u for ur reply.......
The thing is..... even to use a data conversion first of all the columns comming from the "OLE DB Source" should have the scode_id column populated.. but the query itself is giving null values for the scode_id column. I used dataviewer to make sure that i am getting the data.... n thats when i found out that the cloumn scode_id is having null values while using "ole db source" in the dataflow task...... but that not the case when i use the same query in management studio.
-
Understood, Can you try the linked server solution. If that is not feasible can you post some sample data and also the package file, I can take a look in my free time.
-
I am not sure how to use the linked server approach... but I am goggling it... n hopefully i wil find out how to use it.
As for the sample data:
----------------
Create stmt for the table in sqlserver2000
--Create table sampletrack
--( [AP_APPROVED] [datetime] NULL,
-- [BASE_PRICE] [numeric](6, 0) NULL,
-- [CLOSED_DATE] [datetime] NULL,
-- [SCODE_ID] [uniqueidentifier] NULL)
---------------------
sample data for sampletrack table
AP_APPROVEDBASE_PRICECLOSED_DATESCODE_ID
2011-08-23 00:00:00.00012011-09-02 00:00:00.0000AE9463E-2A28-466D-8539-3DE2834F9F80
2011-08-10 00:00:00.00012011-08-13 00:00:00.0000AE9463E-2A28-466D-8539-3DE2834F9F80
NULL1NULL8FA29DFA-E5DC-4414-907E-8C153F737DA1
2011-09-04 00:00:00.00012011-09-30 00:00:00.0000AE9463E-2A28-466D-8539-3DE2834F9F80
NULL1NULL59AC0042-CCD8-4DCA-A690-410F38EFCB90
2011-09-23 00:00:00.00012011-10-14 00:00:00.0000AE9463E-2A28-466D-8539-3DE2834F9F80
NULL3NULL8FA29DFA-E5DC-4414-907E-8C153F737DA1
--------------
Create stmt for the destination table in sqlserver2008
--Create table sampleDestination
--( [AP_APPROVED] [datetime] NULL,
-- [BASE_PRICE] [numeric](6, 0) NULL,
-- [CLOSED_DATE] [datetime] NULL,
-- [SCODE_ID] [uniqueidentifier] NULL)
-------------------------
Now I have to get the data from sampletrack..... (the actual query i have uses a lot of joins and case stmts ..and functions..... the query is running fine in management studio)......... and insert the data into the sampleDestination table in sqlserver2008........ I absolutely have no idea wher i am going wrong......
-
You should be able to setup a linked server from the sql 2008 to sql 2000 server, please take a look at this
http://msdn.microsoft.com/en-us/library/ms190479.aspx
Once the linked server is setup you can just do an insert query,
insert SampleDestination
select * from linkedservername.dbname.schemaname.tablename
-
[font="Arial"][/font]
I have used an approach to solve this unique identifier issue........ I used SQL Server DTS (2000) to import the data into the table(2008). It worked....... but I still dont know how to do the same using SSIS 2008....... Any way....... for now my issue has been resolved using 2000 DTS.
Thanks for All UR suggestions.......
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply