Need to copy All oracle tables to SQL Server 2005

I am racking my brain on this one. I am migrating Oracle data to SQL Server and would like an SSIS package that will copy the oracle data from every table to SQL server. I already have the schema set up in SQL and a straight copy of a specified table works fine between the OLEDB source and OLEDB destination. However, when I set up a loop which sets a global variable for the table names, which are identical on both dbs, and try to set the OLEDB Source and Destination OpenRowsetVariable to the global variable, the process fails with these errors with all validation turned off:

[OLE DB Source [18]] Warning: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

[OLE DB Source [18]] Error: Column "ID" cannot be found at the datasource.

[DTS.Pipeline] Error: component "OLE DB Source" (18) failed the pre-execute phase and returned error code 0xC0202005.

To sum up, I want to set the tables dynamically and have them infer the mappings. The names of the columns are already the exact same on each schema, and the data types are mapped accordingly. I have already verified that the names of the tables have been formatted correctly for each database. If I can't do it this way then how?

You can't change the source at run-time since the Meta-data for the columns is static.

To do a copy like this, you need to have a seperate source and destination for each table. The easiest way to do this is to use the Import/Export wizard in 2005 to generate the package. Select the target database in SSMS, right click it and pick Tasks/Import data to run the wizard. it should be pretty self-explanatory, but, if you have trouble, there is a good section on it in BOL.

|||

swatts777 wrote:

[OLE DB Source [18]] Warning: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

This warning can easily be fixed by setting AlwaysUseDefaultCodePage=TRUE on the OLE DB Source component.

swatts777 wrote:

To sum up, I want to set the tables dynamically and have them infer the mappings. The names of the columns are already the exact same on each schema, and the data types are mapped accordingly. I have already verified that the names of the tables have been formatted correctly for each database. If I can't do it this way then how?

SSIS won't do this. I'm worried that you might have read some documentation somewhere that led you think that it will. Is that the case?

Like David said, you have to build a data-flow for each table. You could make it easy on yourself and get the import/export wizard to build it for you.

-Jamie

-Jamie

|||

Apparently, my Oracle Provider wasn't registered in SQL, so I did not know I had this option avalable to me. Using Microsoft's OLEDB Provider for Oracle only lets you pull data over using custom queries. I restarted SQL and my oracle provider showed up. WOW! Thanks for all your help, this worked great! I was able to go through each table and map datatypes and everything. Then I saved the whole thing as an SSIS package for use when we go live.

|||

Hi swatts777,

Can you(or anyone else) tell me how to register an Oracle provider as you described above?
i am having the same endless shyte with data types when pulling single values from Oracle.

Anybody please help me

Regards,

Pieter

|||

I think yhe answer is right there...

swatts777 wrote:

I restarted SQL and my oracle provider showed up.

|||

Hi Rafael,

It seems pretty much easy doesn't it, but I'm still not sure if I should have a new version of Oracle installed before I restart my SQL server or install just a driver or what?

Maybe I should change my name to StupidPete?

Regards,

Pieter

|||

Peter,

After installing the Oracle client, you must set up Oracle Net services to point to the correct Oracle database. There are plenty of articles on doing this. You should have installed the Client using Oracle Universal Installer that comes with the client you are using and that you downloaded from Oracle's site. If you have done all of this, then you can restart the machine. That is the best way. If you are using SQL Server 2005, under Server Objects>Linked Servers>Providers you should now see a OraOLEDB.Oracle provider listed. Now you may create your Linked server using this provider instead of MSADORA.

Hope this helps,

Shawn

|||

CleverPete wrote:

Hi Rafael,

It seems pretty much easy doesn't it, but I'm still not sure if I should have a new version of Oracle installed before I restart my SQL server or install just a driver or what?

Maybe I should change my name to StupidPete?

Regards,

Pieter

This is a perfect example of how a flamming thread starts and how they get deleted/locked. I just wanted to point out a detail that you could have miss. BTW, that was quite the answer; just install it and re-start the machine.

Rafael Salas wrote:

I think yhe answer is right there...

swatts777 wrote:

I restarted SQL and my oracle provider showed up.

|||I wasn't really focused on the questions being asked, rather my on experiences in dealing with the problems I faced. I will try to answer questions more precise and pay more attention to the actual questions being posted.|||

I am trying to do the same exact thing, but with a CACHE database, not oracle. My issue is that SSIS will not dynamically map the columns from each new table that I setup in a foreach loop. I do not want to create a separate design time mapping for each table (there are lots of tables). If I cannot figure this out, I will use OPENQUERY with a linked server, and set the OPENQUERY string dynamically in a loop. Unless I misunderstood, you are not dynamically writing to tables with different structures, correct? If so, how did you get that to work?

|||

Dave Dumas wrote:

I am trying to do the same exact thing, but with a CACHE database, not oracle. My issue is that SSIS will not dynamically map the columns from each new table that I setup in a foreach loop.

You're right, it doesn't do this. You can't do what you are attempting unless the columns in all the source tables are the same.

Dave Dumas wrote:

I do not want to create a separate design time mapping for each table (there are lots of tables).

Unfortunately if you want to use data-flows this is your only option.

Dave Dumas wrote:

If I cannot figure this out, I will use OPENQUERY with a linked server, and set the OPENQUERY string dynamically in a loop.

Good idea that. Let us know how it goes.

-Jamie

|||

Thanks Jamie. What I did (since the OPENQUERY syntax does not all variables, only strings), was to write a select statement with dynamic sql as output. I outputted 300 OPENQUERY statements inside of try-catch BEGIN END blocks. This worked great. - Dave

Need to copy All oracle tables to SQL Server 2005

I am racking my brain on this one. I am migrating Oracle data to SQL Server and would like an SSIS package that will copy the oracle data from every table to SQL server. I already have the schema set up in SQL and a straight copy of a specified table works fine between the OLEDB source and OLEDB destination. However, when I set up a loop which sets a global variable for the table names, which are identical on both dbs, and try to set the OLEDB Source and Destination OpenRowsetVariable to the global variable, the process fails with these errors with all validation turned off:

[OLE DB Source [18]] Warning: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

[OLE DB Source [18]] Error: Column "ID" cannot be found at the datasource.

[DTS.Pipeline] Error: component "OLE DB Source" (18) failed the pre-execute phase and returned error code 0xC0202005.

To sum up, I want to set the tables dynamically and have them infer the mappings. The names of the columns are already the exact same on each schema, and the data types are mapped accordingly. I have already verified that the names of the tables have been formatted correctly for each database. If I can't do it this way then how?

You can't change the source at run-time since the Meta-data for the columns is static.

To do a copy like this, you need to have a seperate source and destination for each table. The easiest way to do this is to use the Import/Export wizard in 2005 to generate the package. Select the target database in SSMS, right click it and pick Tasks/Import data to run the wizard. it should be pretty self-explanatory, but, if you have trouble, there is a good section on it in BOL.

|||

swatts777 wrote:

[OLE DB Source [18]] Warning: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

This warning can easily be fixed by setting AlwaysUseDefaultCodePage=TRUE on the OLE DB Source component.

swatts777 wrote:

To sum up, I want to set the tables dynamically and have them infer the mappings. The names of the columns are already the exact same on each schema, and the data types are mapped accordingly. I have already verified that the names of the tables have been formatted correctly for each database. If I can't do it this way then how?

SSIS won't do this. I'm worried that you might have read some documentation somewhere that led you think that it will. Is that the case?

Like David said, you have to build a data-flow for each table. You could make it easy on yourself and get the import/export wizard to build it for you.

-Jamie

-Jamie

|||

Apparently, my Oracle Provider wasn't registered in SQL, so I did not know I had this option avalable to me. Using Microsoft's OLEDB Provider for Oracle only lets you pull data over using custom queries. I restarted SQL and my oracle provider showed up. WOW! Thanks for all your help, this worked great! I was able to go through each table and map datatypes and everything. Then I saved the whole thing as an SSIS package for use when we go live.

|||

Hi swatts777,

Can you(or anyone else) tell me how to register an Oracle provider as you described above?
i am having the same endless shyte with data types when pulling single values from Oracle.

Anybody please help me

Regards,

Pieter

|||

I think yhe answer is right there...

swatts777 wrote:

I restarted SQL and my oracle provider showed up.

|||

Hi Rafael,

It seems pretty much easy doesn't it, but I'm still not sure if I should have a new version of Oracle installed before I restart my SQL server or install just a driver or what?

Maybe I should change my name to StupidPete?

Regards,

Pieter

|||

Peter,

After installing the Oracle client, you must set up Oracle Net services to point to the correct Oracle database. There are plenty of articles on doing this. You should have installed the Client using Oracle Universal Installer that comes with the client you are using and that you downloaded from Oracle's site. If you have done all of this, then you can restart the machine. That is the best way. If you are using SQL Server 2005, under Server Objects>Linked Servers>Providers you should now see a OraOLEDB.Oracle provider listed. Now you may create your Linked server using this provider instead of MSADORA.

Hope this helps,

Shawn

|||

CleverPete wrote:

Hi Rafael,

It seems pretty much easy doesn't it, but I'm still not sure if I should have a new version of Oracle installed before I restart my SQL server or install just a driver or what?

Maybe I should change my name to StupidPete?

Regards,

Pieter

This is a perfect example of how a flamming thread starts and how they get deleted/locked. I just wanted to point out a detail that you could have miss. BTW, that was quite the answer; just install it and re-start the machine.

Rafael Salas wrote:

I think yhe answer is right there...

swatts777 wrote:

I restarted SQL and my oracle provider showed up.

|||I wasn't really focused on the questions being asked, rather my on experiences in dealing with the problems I faced. I will try to answer questions more precise and pay more attention to the actual questions being posted.|||

I am trying to do the same exact thing, but with a CACHE database, not oracle. My issue is that SSIS will not dynamically map the columns from each new table that I setup in a foreach loop. I do not want to create a separate design time mapping for each table (there are lots of tables). If I cannot figure this out, I will use OPENQUERY with a linked server, and set the OPENQUERY string dynamically in a loop. Unless I misunderstood, you are not dynamically writing to tables with different structures, correct? If so, how did you get that to work?

|||

Dave Dumas wrote:

I am trying to do the same exact thing, but with a CACHE database, not oracle. My issue is that SSIS will not dynamically map the columns from each new table that I setup in a foreach loop.

You're right, it doesn't do this. You can't do what you are attempting unless the columns in all the source tables are the same.

Dave Dumas wrote:

I do not want to create a separate design time mapping for each table (there are lots of tables).

Unfortunately if you want to use data-flows this is your only option.

Dave Dumas wrote:

If I cannot figure this out, I will use OPENQUERY with a linked server, and set the OPENQUERY string dynamically in a loop.

Good idea that. Let us know how it goes.

-Jamie

|||

Thanks Jamie. What I did (since the OPENQUERY syntax does not all variables, only strings), was to write a select statement with dynamic sql as output. I outputted 300 OPENQUERY statements inside of try-catch BEGIN END blocks. This worked great. - Dave

need to convert this SQL2000 DTS validation code to SQL2005 SSIS

Hello,

How do I convert VBSCRIPT code in a DTS package which is used for data validation to SQL 2005 SSIS?

Thanks,

Michael

Michael,

There isn't really a tool to do that for you. Its a manual process I'm afraid.

If you explain what sort of data validation you want to do then you may find people on here will be able to help you achieve the same in SSIS.

-Jamie

|||

Hello,

I have a source table which contains some invalid dates (can be null, letters, etc). I want to check to see if the date is valid. If the date is valid then I want to use it. If the date is not valid then I went to set the destination field to null.

In the past I used vbscript code like

Dim dtStart
Dim dtEnd
Dim dtSource

dtStart = CDate("1/1/1753")
dtEnd = CDate("12/31/9999")

If Not IsNull(DTSSource("MyDate")) Then
If IsDate(DTSSource("MyDate")) Then
dtSource = CDate( DTSSource("MyDate"))

If dtSource >= dtStart and dtSource <= dtEnd Then
DTSDestination("MyDate") = DTSSource("MyDate")
end if
end if
end if
Main = DTSTransformStat_OK

Thanks,

Michael

need sample code in vb.net

Hi,

I neew a small sample code in VB.NET to create a custom data flow component for SSIS.
All the examples I was able to find it are in C#.

Answer very apreciated.

Go to http://msdn2.microsoft.com/en-US/library/ms136112.aspx

Thanks,
Loonysan
http://mystutter.blogspot.com/

|||There are a number of websites that provide conversion utilities to convert C# to VB. Using those will increase the amount of sample code available to you.|||

SQL Server ships with several simple samples of SSIS components. Please install the SQL samples. The December update is available from MSDN at http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en.

-Doug

Need Novell Network Connection without logging on first

I am new to SSIS but this does not seem like it should be a hard thing to do. Here is the background:

Windows Server 2003

SQL Server 2005

SSIS

Logged in to server as Admin

I've created a SSIS package that I want to run as a Job. In it there is a Ole DB Connection to a VFP Database on a Novell Server. Client for Netware Services is started when server is but NOT logged into. When I created the package everything runs fine. Note that I had to login when I created the Data Link. I can even run it from the SQL Server Package Store. As soon as I restart the server and try to run it I get an error that the file path is invalid in both SSIS and the Package Store. I check the connection string and it looks good.

The connection is availible in My Network Places

If I open the connection and login it runs fine again.

How do I intiate the network connection and then close it again?

Cory Bonallo

Retail Services

Developer

Aren't there some NET USE commands (or NetWare equivalents) that you could use to create the connection without having to log in?
|||

I've never used anything like that. Should there be a setting or something in the SSIS connection manager? Do you have any examples? Thanks for your help!

Cory Bonallo

Retail Services

Developer

|||

Cory Bonallo wrote:

I've never used anything like that. Should there be a setting or something in the SSIS connection manager? Do you have any examples? Thanks for your help!

No, this is more in the Windows arena than SSIS. Taking the Novell client out of the picture for the moment, when you're not logged into the machine, none of your drive mappings are active since those are user-specific. Your package may work fine when you're logged it, but when it tries to run unattended with nobody logged in, it can no longer access mapped drives and fails.

This comes up from time to time here on the forum, but the search is failing me at the moment for good threads. This one applies, but isn't a stellar example.

With the Windows client, the NET USE command (try "net use /?" from the Windows command prompt) can be used to make these connections. If you're running the SSIS package from SQL Agent, you can use CmdExec steps before your package to create a connection with NET USE and one after the package to delete it.

I have no idea how having the NetWare client in the picture might change things. I don't know if NET USE will work or if there is a different command you have to use to accomplish this.

|||

Well I thought that did it. But now I can't get it to run in a job step. I cant seem to figure out the syntax or something for cmdExec.

Here is what I did:

in dos window typed "net use \\jds1\vol3 /user:admin" works great!

Created a job step as Operating System (cmd Exec) and typed in:

"net use" \\jds1\vol3 /user:admin

Got this error:

04/16/2007 11:05:45,fred,Error,1,TESTSERVER,fred,Open Novell Connection,,Executed as user: JENSENNT1\CORYB. The process could not be created for step 1 of job 0x7FE4E946E4D7E944A13A2AB6551A270B (reason: The system cannot find the file specified).The step failed.,00:00:00,0,0,,,,0

So I tried several other changes and still same error...

What am I missing?

Cory

|||Try:

"C:\WINDOWS\system32\net.exe" use \\server\share /user:admin

"net use" is not a command. "use" is an argument to net.exe.|||

Thank you Phil. The more I use this tool the dumber I feel! So now I don't get an error but it just sits on that step. I'm assuming it has something to do with a return success code. Try as I might I just can't find any examples or instructions on how to code for this. Is there a way to reture a success code. I left it a zero because the articles I found said that was the default success code. Agail thanks for your help!

Cory

|||

Cory Bonallo wrote:

Thank you Phil. The more I use this tool the dumber I feel! So now I don't get an error but it just sits on that step. I'm assuming it has something to do with a return success code. Try as I might I just can't find any examples or instructions on how to code for this. Is there a way to reture a success code. I left it a zero because the articles I found said that was the default success code. Agail thanks for your help!

Cory

I don't think its related to the return code. I think it probably isn't returning. I see you're specifying a user name but no password. The Net.exe may be prompting and waiting for a response. Try passing the password too.

"C:\WINDOWS\system32\net.exe" use \\server\share /user:admin mypassword

|||

You were right. It was the password. Now can you tell me why the connection is not persisting to the next job step? So here is what I want to do:

Step 1: open network connection

Step 2: run dtsx package that uses that connection

Step 3: Close network connection

Seems simple enough?

Cory

|||Look at either mapping a drive to that share, or using the /PERSISTENT:YES flag.|||

Phil Brammer wrote:

Look at either mapping a drive to that share, or using the /PERSISTENT:YES flag.

Or combining all three into a single step? Perhaps by putting them in a batch file?
|||

JayH wrote:

Or combining all three into a single step? Perhaps by putting them in a batch file?

Yep.|||

Thabk you both very much. What I ended up doing is adding an Excute Process Task to Open the connection, run the rest of the steps of the package and then close the connection. Thanks again to all!!!

Cory

|||

I did get the net use working in the Job as a part of an SSIS package. Now I am trying to use the same process to open a connection to Novell in the Query editor and setting up a stored procedure that a Web service can call to get a connection to an access.mdb linked server on the Novell drive. When I try to execute via xp_cmdshell the command succeeds but does not make the conncetion availible to any other session. Then when I ran it in a batch to do a simple file copy the net use command succeeded but the copy failed. When I used impersonate it did not change the user from NT AUTHORITY/? I can't a channel opened.

Cory

|||

I did get the net use working in the Job as a part of an SSIS package. Now I am trying to use the same process to open a connection to Novell in the Query editor and setting up a stored procedure that a Web service can call to get a connection to an access.mdb linked server on the Novell drive. When I try to execute via xp_cmdshell the command succeeds but does not make the conncetion availible to any other session. Then when I ran it in a batch to do a simple file copy the net use command succeeded but the copy failed. When I used impersonate it did not change the user from NT AUTHORITY/? I can't a channel opened.

Cory

Need Novell Network Connection without logging on first

I am new to SSIS but this does not seem like it should be a hard thing to do. Here is the background:

Windows Server 2003

SQL Server 2005

SSIS

Logged in to server as Admin

I've created a SSIS package that I want to run as a Job. In it there is a Ole DB Connection to a VFP Database on a Novell Server. Client for Netware Services is started when server is but NOT logged into. When I created the package everything runs fine. Note that I had to login when I created the Data Link. I can even run it from the SQL Server Package Store. As soon as I restart the server and try to run it I get an error that the file path is invalid in both SSIS and the Package Store. I check the connection string and it looks good.

The connection is availible in My Network Places

If I open the connection and login it runs fine again.

How do I intiate the network connection and then close it again?

Cory Bonallo

Retail Services

Developer

Aren't there some NET USE commands (or NetWare equivalents) that you could use to create the connection without having to log in?
|||

I've never used anything like that. Should there be a setting or something in the SSIS connection manager? Do you have any examples? Thanks for your help!

Cory Bonallo

Retail Services

Developer

|||

Cory Bonallo wrote:

I've never used anything like that. Should there be a setting or something in the SSIS connection manager? Do you have any examples? Thanks for your help!

No, this is more in the Windows arena than SSIS. Taking the Novell client out of the picture for the moment, when you're not logged into the machine, none of your drive mappings are active since those are user-specific. Your package may work fine when you're logged it, but when it tries to run unattended with nobody logged in, it can no longer access mapped drives and fails.

This comes up from time to time here on the forum, but the search is failing me at the moment for good threads. This one applies, but isn't a stellar example.

With the Windows client, the NET USE command (try "net use /?" from the Windows command prompt) can be used to make these connections. If you're running the SSIS package from SQL Agent, you can use CmdExec steps before your package to create a connection with NET USE and one after the package to delete it.

I have no idea how having the NetWare client in the picture might change things. I don't know if NET USE will work or if there is a different command you have to use to accomplish this.

|||

Well I thought that did it. But now I can't get it to run in a job step. I cant seem to figure out the syntax or something for cmdExec.

Here is what I did:

in dos window typed "net use \\jds1\vol3 /user:admin" works great!

Created a job step as Operating System (cmd Exec) and typed in:

"net use" \\jds1\vol3 /user:admin

Got this error:

04/16/2007 11:05:45,fred,Error,1,TESTSERVER,fred,Open Novell Connection,,Executed as user: JENSENNT1\CORYB. The process could not be created for step 1 of job 0x7FE4E946E4D7E944A13A2AB6551A270B (reason: The system cannot find the file specified).The step failed.,00:00:00,0,0,,,,0

So I tried several other changes and still same error...

What am I missing?

Cory

|||Try:

"C:\WINDOWS\system32\net.exe" use \\server\share /user:admin

"net use" is not a command. "use" is an argument to net.exe.|||

Thank you Phil. The more I use this tool the dumber I feel! So now I don't get an error but it just sits on that step. I'm assuming it has something to do with a return success code. Try as I might I just can't find any examples or instructions on how to code for this. Is there a way to reture a success code. I left it a zero because the articles I found said that was the default success code. Agail thanks for your help!

Cory

|||

Cory Bonallo wrote:

Thank you Phil. The more I use this tool the dumber I feel! So now I don't get an error but it just sits on that step. I'm assuming it has something to do with a return success code. Try as I might I just can't find any examples or instructions on how to code for this. Is there a way to reture a success code. I left it a zero because the articles I found said that was the default success code. Agail thanks for your help!

Cory

I don't think its related to the return code. I think it probably isn't returning. I see you're specifying a user name but no password. The Net.exe may be prompting and waiting for a response. Try passing the password too.

"C:\WINDOWS\system32\net.exe" use \\server\share /user:admin mypassword

|||

You were right. It was the password. Now can you tell me why the connection is not persisting to the next job step? So here is what I want to do:

Step 1: open network connection

Step 2: run dtsx package that uses that connection

Step 3: Close network connection

Seems simple enough?

Cory

|||Look at either mapping a drive to that share, or using the /PERSISTENT:YES flag.|||

Phil Brammer wrote:

Look at either mapping a drive to that share, or using the /PERSISTENT:YES flag.

Or combining all three into a single step? Perhaps by putting them in a batch file?
|||

JayH wrote:

Or combining all three into a single step? Perhaps by putting them in a batch file?

Yep.|||

Thabk you both very much. What I ended up doing is adding an Excute Process Task to Open the connection, run the rest of the steps of the package and then close the connection. Thanks again to all!!!

Cory

|||

I did get the net use working in the Job as a part of an SSIS package. Now I am trying to use the same process to open a connection to Novell in the Query editor and setting up a stored procedure that a Web service can call to get a connection to an access.mdb linked server on the Novell drive. When I try to execute via xp_cmdshell the command succeeds but does not make the conncetion availible to any other session. Then when I ran it in a batch to do a simple file copy the net use command succeeded but the copy failed. When I used impersonate it did not change the user from NT AUTHORITY/? I can't a channel opened.

Cory

|||

I did get the net use working in the Job as a part of an SSIS package. Now I am trying to use the same process to open a connection to Novell in the Query editor and setting up a stored procedure that a Web service can call to get a connection to an access.mdb linked server on the Novell drive. When I try to execute via xp_cmdshell the command succeeds but does not make the conncetion availible to any other session. Then when I ran it in a batch to do a simple file copy the net use command succeeded but the copy failed. When I used impersonate it did not change the user from NT AUTHORITY/? I can't a channel opened.

Cory

Need help!!!! (SP or SSIS Task)

I have written the Dynamic TSQL S-Proc. Below is what i wanted to implement in SSIS using foreach loop container as a cursor. But i am little doubtful whether I can achieve the dynamics to this level. I know everything is possible but is it advisable to go for this simple Sproc or SSIS tasks.

I have some 15 tables being populated using this SPROC.

Here is some helpful description

ENTITYNAME gives me the table i need to work

FIELDNAME gives me the field i have to work on

CHANGEDVALUE gives me the value changed in that field

( This three i get from source table which is about 9000 rows and containing 15 possible ENTITY to be work on and 100's of their respective FIELD )

while in Cursors i need to get using these above variables other variables like

FLAG

KeyName

Thrugh SQL1 I get the KeyValue

then using this KeyValue check if the data exist update else insert new data.

QUESTION: IS THIS ADVISABLE to go for SSIS task or just carry with SPROC?

/*******************************************************************************************************/

DECLARE Table_Cursor CURSOR
FOR SELECT ENTITYNAME,FIELDNAME, KEYID, CHANGEDVALUE, UPDATEUSER, UPDATEDATE
FROM dbo.ChangedDimensionStage

OPEN Table_cursor

FETCH NEXT FROM Table_cursor INTO @.ENTITY, @.FIELD, @.KEYID, @.VALUE, @.USER, @.DATE

WHILE @.@.FETCH_STATUS = 0

BEGIN

DECLARE @.FLAG NVARCHAR(50);
SET @.FLAG = (SELECT LEFT(@.ENTITY, (SELECT CHARINDEX( 'DIM', @.ENTITY) -1)) )+ 'LastUpdateFlag';

DECLARE @.KeyName NVARCHAR(50);
SET @.KeyName = (SELECT LEFT(@.ENTITY, (SELECT CHARINDEX( 'DIM', @.ENTITY) -1)) )+ 'Key'

DECLARE @.KeyValue NVARCHAR(50)

DECLARE @.SQL1 NVARCHAR (1000)

SET @.SQL1 = N'Select @.KeyValueOUT = '+ @.KeyName + ' FROM DW_Integration.dbo.MangFact WHERE ClaKey = ' + @.KEYID + ' GROUP BY ' + @.KeyName + ' HAVING SUM(TotalClaCount) > 0 OR SUM(IncidentOnlyClaCount) > 0 '

EXECUTE sp_executesql @.SQL1, N'@.KeyValueOUT INT OUTPUT', @.KeyValue OUTPUT;

DECLARE @.WC_TABLE NVARCHAR(100)
SET @.WC_TABLE = 'WorkingCopy' + @.ENTITY

DECLARE @.SQL2 nvarchar (1000);
SET @.SQL2 = 'IF EXISTS (SELECT '+ @.KeyName +' FROM ' + @.WC_TABLE + ' WHERE ' + @.KeyName + ' = ' + @.KeyValue + ' )' +
' BEGIN UPDATE ' + @.WC_TABLE + ' SET '+ @.FIELD + ' = '''+ @.VALUE + ''' WHERE ' + @.KeyName + ' = ' + @.KeyValue +'; END' +
' ELSE BEGIN
INSERT INTO '+ @.WC_TABLE + ' SELECT * FROM DW_Integration.dbo.' + @.ENTITY + ' WHERE ' + @.Flag + ' = ' + '''Y''' + ' AND '+ @.KeyName + ' = ' + @.KeyValue + ';' +
'UPDATE ' + @.WC_TABLE + ' SET '+ @.FIELD + ' = '''+ @.VALUE + ''' WHERE ' + @.KeyName + ' = ' + @.KeyValue +'; END'

EXECUTE sp_executesql @.SQL2

FETCH NEXT FROM Table_cursor INTO @.ENTITY, @.FIELD, @.KEYID, @.VALUE, @.USER, @.DATE

END

CLOSE Table_cursor
DEALLOCATE Table_cursor

This is not a striaghtforward question, and there is no straightforward answer either.

The stored proc you have provided is dreadfully inefficient, and could be done much more efficiently. But that may not be an issue with the volumes of data you are working with.

An SSIS package is another approach, but do you need to re-develop this work? What is the justification for doing so? Do you need to be able to easily update the procedure? Perhaps using external configuration? In this case, an SSIS reimplementation may be justified. Do you need to hand it over to someone else to maintain? Then again, SSIS may be useful, especially if the maintainers have little experience with T-SQL.

Nobody here is going to say to you to do it one way or another way. I would ask the person who is paying for the job what it is they are after.