Error: Could not get a list of SSIS packages from the project.

Hello,

I have a SSIS package and I am trying to build it using devenv.com from command line. It gives me this error:

Error: Could not get a list of SSIS packages from the project.

However if I open the project in Visual Studio it builds properly. In project property I have set CreateDeploymentUtility=true.

This is what I do from command line:

devenv.com MySSIS.sln /Build "Development|Default"

Any idea what I should do ?

Thanks

Deval

Hello,

There is no solution to it as far as I know if we set CreateDeploymentUtility=true. If you uncheck it then it will work fine.

Thanks

Deval

error: connection is busy

Error: 0xC0202009 at Load SQL Deals, Insert SQL Deals [12494]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Connection is busy with results for another command".

Why isn't the connection manager managing this?

How can I trace the cause? The data flow component (insert SQL deals) is just a simple INSERT statment.You can always run SQL Profiler to see what's going on.

Error. doesnt make sense. HELP!

I am new to SSIS. i am trying to port database from SQL SERVER 2000 to 2005. i am using "Transfer SQL Server Objects" for this. i am just trying to move one object for testing wether it works or not. and it is not working. i am getting this error.

[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "ERROR : errorCode=-1071636471 description=An OLE DB error has occurred. Error code: 0x80040E37. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E37 Description: "Invalid object name 'dbo.consta_AE'.". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".

Both DBs are on seprate machines. of you need any more info please let me know. that would be great help.

Thanks

Tha error means that the object 'Consta_AE' is not found in the database pointed by 'Microsoft SQL Native client'; make sure the connecion manager is pointing to the right Server and DB; also check that the object being called ' Consta_AE' exists under the schema 'dbo', otherwise provide the right schema name.|||Hi thanks for you response. problem is i have checked all these things. connections is pointing towrods right DB and object exist with dbo scheme in the right DB. but both DB are on the different machines and i am working on the third one. does this make any difference. and do i need any special permissions to do this. .|||

Make sure the user you are running the package under has permission to connect to that SQL Server and can see that table/object.

What type of connection are you using in the package to connect to those server? did you test them within BIDS?

|||

Yes i have tested these connections with BIDS. these connection i have created from the Data transform object control. yes i do have permissions and i have also created a package from import export wizard and it works fine.

Thanks

|||Haroon,
What version of SQL Server are you using? RTM? SP1? SP2?

I almost wonder if the Transfer Objects Task isn't trying to copy things in the wrong order... That is, maybe it's trying to copy a view that references the table that doesn't exist (yet.)

What settings do you have for:
CopySchema IncludeDependentObjects CopyAllObjects
|||

Hi

SQL version is

9.00.2047.00

SP1

Standard Edition

i am just trying to copy only one table object for testing . which is not working .

setting which i have are.

DropObjectFirst True

IncludeDependentObject False

CopySchema False

CopyAllObjects False

and i am just copying one object from tables and everything else in there is also false

Thanks,

|||

Try setting the CopySchema property to true.

~Matt

Error with Data Flow Task

I am having problems with the Data Flow task. It does not even show up in the list of items to drop into the SSIS project.

If I go to the Data Flow tab and hit create, I get the follow error. I have tried repairing and reinstalling, but nothing seems to clear up the error. Without rebuilding my machine, is there anyone who knows how to get the Data Flow Task reinstalled properly?

Thanks

Wayne

TITLE: Microsoft Visual Studio


Registration information about the Data Flow task could not be retrieved. Confirm that this task is installed properly on the computer.


ADDITIONAL INFORMATION:

TaskHost "{C3BF9DC1-4715-4694-936F-D3CFDA9E42C5}"' is not installed correctly on this computer. (Microsoft.DataTransformationServices.Design)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.762&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=TaskHostNotInstalled&LinkId=20476


BUTTONS:

OK

Did you search the forum?

it looks like that has been asked before:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=898604&SiteID=1

Error while using a slowly changing dimension transformation

I have a SSIS package which contains a number of slowly changing dimension transformations. While the majority work I have one which gives me the following error 'Error: The variable "System::LocaleID" is already on the read list. A variable may only be added once to either the read lock list or the write lock list. '. This error only occurs if the destination table holds data. If I truncate the table and reload the data then the package complete successful. The only difference I can see between this dimension transformation and the other dimension transforms is that the one in question has 2 business keys while the rest have 1.

Can anyone shed light on this?

Thanks

Have you installed SP1 and the hot fixes or SP2?

There was a bug in the variable handling which caused this error.
Although, the bug was not specifically for the SCD but variables in script task and derived columns and variables in general (so it would seem)|||

Yeah I've got SP2 installed.

Does this bug have a negative effect on the execution of the package? Has far has I can tell the package is completing. What is further confusing is if I empty the target dimension table I dont get the error. It only occurs on subsequent runs of the package when theres data present.

Error while using a slowly changing dimension transformation

I have a SSIS package which contains a number of slowly changing dimension transformations. While the majority work I have one which gives me the following error 'Error: The variable "System::LocaleID" is already on the read list. A variable may only be added once to either the read lock list or the write lock list. '. This error only occurs if the destination table holds data. If I truncate the table and reload the data then the package complete successful. The only difference I can see between this dimension transformation and the other dimension transforms is that the one in question has 2 business keys while the rest have 1.

Can anyone shed light on this?

Thanks

Have you installed SP1 and the hot fixes or SP2?

There was a bug in the variable handling which caused this error.
Although, the bug was not specifically for the SCD but variables in script task and derived columns and variables in general (so it would seem)|||

Yeah I've got SP2 installed.

Does this bug have a negative effect on the execution of the package? Has far has I can tell the package is completing. What is further confusing is if I empty the target dimension table I dont get the error. It only occurs on subsequent runs of the package when theres data present.

Error while trying to deploy SSIS Package

Hi,
I am trying to deploy a DTSX package to 32-bit SQL Server 2005 in a 64-bit machine from my installer.
I am getting the following error in the SaveToSQLServer method:
The SaveToSQLServer method has encountered OLE DB Error code 0x80004005 (Communication Link Failure). The SQL statement that was issued has failed.
However I am not facing this problem when I try to deploy the DTSX package to a 32-bit SQL Server 2005 in a 32-bit machine.
Can anyone tell me why this happens and how this could be solved?
Thanks,
Sandhya

It sounds like you're doing this through BIDS now, which is fine but as a debug step will Management Studio even let you import the package in? That will prove you have connectivity to the SSIS service. You'll need port 135 open to the server to your box if you haven't already done so. Another tactic is to create a deployment utility and deploy the entire project. You may already know that one but just in case, here's a video to walk you through that series of steps: http://www.jumpstarttv.com/Media.aspx?vid=28.

Brian

|||

Hi,

I tried importing the packages. I was able to do that successfully. Still when I tried to install, the packages were not gettign deployed. So I reinstalled SQL server 2005 and tried. It worked. But when I try installing my solution repeatedly, the same error occurs. Why does this happen?

Error while trying to deploy SSIS Package

Hi,
I am trying to deploy a DTSX package to 32-bit SQL Server 2005 in a 64-bit machine from my installer.
I am getting the following error in the SaveToSQLServer method:
The SaveToSQLServer method has encountered OLE DB Error code 0x80004005 (Communication Link Failure). The SQL statement that was issued has failed.
However I am not facing this problem when I try to deploy the DTSX package to a 32-bit SQL Server 2005 in a 32-bit machine.
Can anyone tell me why this happens and how this could be solved?
Thanks,
Sandhya

It sounds like you're doing this through BIDS now, which is fine but as a debug step will Management Studio even let you import the package in? That will prove you have connectivity to the SSIS service. You'll need port 135 open to the server to your box if you haven't already done so. Another tactic is to create a deployment utility and deploy the entire project. You may already know that one but just in case, here's a video to walk you through that series of steps: http://www.jumpstarttv.com/Media.aspx?vid=28.

Brian

|||

Hi,

I tried importing the packages. I was able to do that successfully. Still when I tried to install, the packages were not gettign deployed. So I reinstalled SQL server 2005 and tried. It worked. But when I try installing my solution repeatedly, the same error occurs. Why does this happen?

Error While Scheduling the SSIS Package

Hello Everyone,
When i try to scedule a job on SQL Server Management Studio it gives the Following Error:

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectkey'.(Micorsoft.SqlServer.Smo)

Can anyone tell me why ?

MS SQL Server Details:

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Hi,

Do you have this file?

C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.Smo.dll

Regards,

Janos

|||Yes, File is there on the specified path.

|||

Well, I got the same error one time, as far as I remember, I repaired my installation of SQL Server client tool.

Try to repair it please.

regards,

Janos

|||thanks janos,

i have a local server installed on my workstation along with sp2.
Do you really think reinstalling the server will work ?
|||

Well, It worked for me

Maybe you just repair your installation will be enough.

Regards,

Janos

|||Is there any option for repairing the server ?
Coz i haven't seen any while installing.
|||Did you manage to find a solution for this problem? I get the same error when trying to create an Agent job. I've repaired the SQL Native Client but to no avail. I still get the error.
|||No, not yet !!! I am still working on it.
One of the forum member advised me to repair the server but for you if it did not work, then i am doubtful.
|||You can repair the application from "Programs and Features" (Vista) or "Add/Remove Programs" (XP) in control panel|||

Hi Vick,

I just realized something. I saw the error message contains the SQL build w/o service pack, but you wrote you have SP2 installed (but maybe just for database services and SSMS or just SSIS). Have you tried to install the SP2 to the SSIS or those, where not installed?

Regards,

Janos

|||No, Its not working even after repairing the server.

Thanks
Janos
|||Sorry Vick, I'm out of ideas. I tried to recreate the same error on a virtual machine, but no luck. I'm sorry.|||Hi Janos,

Sp2 is not specific to the service you are using. It upgrades all the services of server (EE, DE, SE, etc).

Check out the link
http://www.microsoft.com/downloads/details.aspx?familyid=d07219b2-1e23-49c8-8f0c-63fa18f26d3a&displaylang=en
|||check out the post.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1793362&SiteID=1

check if you are experiencing the same.

Error while running the "Execute SQL task"

hi frnds,

Till last week my SSIS package was running fine.

now it is throwing the below mentioned error:

SSIS package "DNB_GMS_PO_Transition.dtsx" starting.

Error: 0xC0202009 at DNB_GMS_PO_Transition, Connection manager "CTSINNVLSDNB1\SQLEXPRESS.cedprod.cedprod": An OLE DB error has occurred. Error code: 0x80040E4D.

An OLE DB record is available.Source: "Microsoft SQL Native Client"Hresult: 0x80040E4DDescription: "Login failed for user 'cedprod'.".

Error: 0xC00291EC at Takingbackup of the affected tables, Execute SQL Task: Failed to acquire connection "CTSINNVLSDNB1\SQLEXPRESS.cedprod.cedprod". Connection may not be configured correctly or you may not have the right permissions on this connection.

Task failed: Takingbackup of the affected tables

Warning: 0x80019002 at DNB_GMS_PO_Transition: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "DNB_GMS_PO_Transition.dtsx" finished: Failure.

i was unable to figure it out.but i had connected correctly to Database.

if i run those queries in the Query analyser with the same DB connection its running fine.

its urgent.

please tell me the soultion.

And in that Query Analyzer session you were logging in a "CEDPROD"?|||ya i was able to log in the CEDPROD and l logged in the Query Analyser too|||Did you choose to "save the password" in the connection manager when setting it up? Have you promoted this package to SQL Server and are trying to run it there?|||ya i selected the " save my password " and its running fine in SQL Server 2000

Error while executing SSIS package from other SSIS package

Hi,

In our project we have two SSIS package.

And there is a task (Execute SSIS package) in First package that calls the execution of second package.

I m continuously receiving an error "Failed to decrypt protected XML node "PackagePassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available."

As we are running first package by job, job runs successfully logging above error

The protection level of second package is set to "EncryptSensitiveWithUserKey"

Can anybody please suggest how to handle it?

If these DTSX has been saved on the MSDB you could use 'Server Storage' as protection level.

EncryptSensitive.. is the default protection level for the packages.

|||

If protection level property of the package has EncryptSensitive WithUSerKey means that only the author of the package is allowed to run it. This KB offers diffrent alternatives to solve your issue:

An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step:

http://support.microsoft.com/kb/918760

|||

Admin,

This topic is a good candidate for a "sticky" for the forum.

This question is asked once a day... we could come up with a synopsis on package encryption

|||

The DTSX is not saved in the MSDB,

its at disk only.

Error while debugging the script task

I am getting an while trying to run the script task in debug mode in SSIS package.

"SQL Server Integration Services Script Task has encountered a problem and needs to close. We are sorry for the inconvenience."

Any idea why i am getting this error.

Thanks,

Deepak,

Did you ever find a solution to your issue? I'm having the same thing.

Thanks,
Todd

|||I don't know what the problem is, but I had the same problem as you. I found that I had some Breakpoints sit in other script tasks. When you select the Debug Menu -> Delete All Breakpoints, I assumed it deleted all breakpoints. However, it doesn't seem to clear them out of the script tasks very well. Anyway, I went into every Script Task .NET code and explicity looked and removed all breakpoints. Then, I executed the package. Finally, I added my desired breakpoints back into the particular script task I was debugging. I hope that helps.

Error while debugging the script task

I am getting an while trying to run the script task in debug mode in SSIS package.

"SQL Server Integration Services Script Task has encountered a problem and needs to close. We are sorry for the inconvenience."

Any idea why i am getting this error.

Thanks,

Deepak,

Did you ever find a solution to your issue? I'm having the same thing.

Thanks,
Todd

|||I don't know what the problem is, but I had the same problem as you. I found that I had some Breakpoints sit in other script tasks. When you select the Debug Menu -> Delete All Breakpoints, I assumed it deleted all breakpoints. However, it doesn't seem to clear them out of the script tasks very well. Anyway, I went into every Script Task .NET code and explicity looked and removed all breakpoints. Then, I executed the package. Finally, I added my desired breakpoints back into the particular script task I was debugging. I hope that helps.

Error while debugging the script task

I am getting an while trying to run the script task in debug mode in SSIS package.

"SQL Server Integration Services Script Task has encountered a problem and needs to close. We are sorry for the inconvenience."

Any idea why i am getting this error.

Thanks,

Deepak,

Did you ever find a solution to your issue? I'm having the same thing.

Thanks,
Todd

|||I don't know what the problem is, but I had the same problem as you. I found that I had some Breakpoints sit in other script tasks. When you select the Debug Menu -> Delete All Breakpoints, I assumed it deleted all breakpoints. However, it doesn't seem to clear them out of the script tasks very well. Anyway, I went into every Script Task .NET code and explicity looked and removed all breakpoints. Then, I executed the package. Finally, I added my desired breakpoints back into the particular script task I was debugging. I hope that helps.

Error while creating credentials

Hi guys

I am trying to run an SSIS package by creating a credential. When I try to create a credential thorugh the Mgmt Studio I get the following error:

An error occurred during encryption. (Microsoft SQL Server, Error: 15467)

I tried doing it with the following SQL statement and still have the same error:

CREATE CREDENTIAL JayTest WITH IDENTITY = 'domain\username', SECRET = 'password';

GO

My SQL Server 2005 instance is installed on a Windows Server 2003 box and is running under my NT Account as of now.

Thanks
Jay

Can you please indicate the SQL Server version that you are using (copy-paste the results of 'select @.@.version')?

Credential secrets are encrypted by the service master key (SMK), so it looks like this encryption is failing. We'll need a little more information to figure out what is the cause of the failure:

1) Please check if the SMK exists in your server:

use master
go
select * from sys.symmetric_keys where symmetric_key_id = 102
go

2) If the SMK exists, see if it can be used. You can try creating a master key in a new database or regenerating the SMK:

a)

create database testsmk
go
use testsmk
go
create database master key encryption by password = 'some_password'
go
use master
go
drop database testsmk
go

b)

alter service master key regenerate
go

If there is a problem with the SMK, these two should fail. Let us know what errors you get, if any.

3) Finally, check if there is a user profile directory created for the SQL Server service account.

Also, let us know if you changed the service account for SQL Server; this was not supported and would cause problems, although I would expect a decryption error instead of an encryption error in this case.

Thanks
Laurentiu

Error while converting Oracle Timestamp to Sql Server Timestamp (datetime) - "Invalid date

I am populating oracle source in Sql Server Destination. after few rows it fails it displays this error:

[OLE DB Destination [16]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description:
"Invalid date format".

I used this script component using the following code in between the adapters, However after 9,500 rows it failed again giving the same above error:

To convert Oracle timestamp to Sql Server timestamp

If Row.CALCULATEDETADATECUST_IsNull = FalseThen

If IsDate(DateSerial(Row.CALCULATEDETADATECUST.Year, Row.CALCULATEDETADATECUST.Month, Row.CALCULATEDETADATECUST.Day)) Then

dt = Row.CALCULATEDETADATECUST

Row.CALCULATEDETADATECUSTD = dt

EndIf

EndIf

I don't know if my code is right . Please inform, how i can achieve this.

What is the value of the offending row? Redirect the error rows from the SQL dest and see what the offending value is.

The fact that it gets through 9500 rows and then dies means your logic is right but there is a dodgy row.|||

Thanks for your support, Crispin,

I redirected the output at destination (Fail Point) as you said and can see that some columns are displaying the Errors instead of the actual data that gives the clue about which is invalid)

Column 5

Error: Year, Month and day parameters describe an unrepresnatable date and time...

I doubt that the DateSerial function in the script component is unable to extract some formats..

So,

1) Does anybody know which representations and range of Oracle (timestamp) is not allowed in Sql Server (Datetime)
2) Do we have a stable code that can perform Oracle timestamp to Sql Server conversion here

Thanks

Subhash Subramanyam

|||

Hi Experts in SSIS forum,

Thanks to LoRez who has raised this question this month again.. I am still having the same problem in SSIS. I have an Oracle timestamp Source Columns that should be populated into Sql Server Datetime columns. Though I am aware of the ranges valid for both the cases, I was unable to frame it properly. . The expression (This only checks the Date Part , I wanted code that converts the time part of the Oracle timestamp into Sql Server Datetime) I have used to convert this is:

(!ISNULL(REQUESTED_ETA_DATE_CUST) && DATEPART("yyyy",REQUESTED_ETA_DATE_CUST) > 1752 && DATEPART("yyyy",REQUESTED_ETA_DATE_CUST) < 9999) ? REQUESTED_ETA_DATE_CUST : NULL(DT_DBTIMESTAMP)

Though the expression syntax is valid, it still gives error when I run the dataflow. The error is:

[Derived Column [136]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (136)" failed because error code 0xC0049067 occurred, and the error row disposition on "input column "REQUESTED_ETA_DATE_CUST" (372)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

Though http://forums.informationbuilders.com/eve/forums/a/tpc/f/1381057331/m/7121008802 helps to create a trigger, but not the way we can achieve using SSIS.

Greatly Appreciate if anybody can pointer or paste the Expression or .NET code that can convert Oracle timestamp into Sql Server Datetime without issues

Thanks

Subhash Subramanyam

|||

Dear Umachandar, Thanks for briefing up the approach.

I'd appreciate if you go one step ahead to help my case work.Oracle Timestamp in my case is of the format 'MM/DD/YYYY hh:mm:ss.nnn' and I do not have any control over oracle source. Somehow I must be able to use this to populate the Sql Server datetime. We know that the year should be grater that 1752 which we can validate using above expression. But Validating TimePart seems to be difficult. I tried casting to string, but due to omission of leading zeros makes it difficult to substring that. So do you have a solution for this?

Umachandar's Reply

Oracle timestamp range subsumes that of SQL Server's datetime range. So you will not have any issues as long as you use the ISO 8601 format to specify the values (YYYY-MM-DDT hh:mm:ss.nnn). This will ensure that the value will be stored correctly irrespective of collation settings on either servers or Oracle session setttings. You can use timestamp with appropriate precision on Oracle side (timestamp(3) is closest) to match SQL Server datetime.

Migrating values from Oracle to SQL Server is a different ballgame. You will lose precision, values etc. Oracle has more richer support and wider ranges & ANSI SQL implementation.

Thanks

Subhash Subramanyam

|||

I've put up an article for this on my blog.

Cast this Oracle timestamp Column REQUESTED_ETA_DATE_CUST as below:

Decode(trunc((Extract(YEAR from REQUESTED_ETA_DATE_CUST))/1753), 0,'01/01/1753 12:00:00 AM', TO_CHAR(REQUESTED_ETA_DATE_CUST, 'MM/DD/YYYY hh:mms AM')) as REQUESTED_ETA_DATE_CUST

Or

Go for filtering valid ranges using SSIS transform after casting into a accepted datetime format.

Error while converting Oracle Timestamp to Sql Server Timestamp (datetime) - "Invalid date

I am populating oracle source in Sql Server Destination. after few rows it fails it displays this error:

[OLE DB Destination [16]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description:
"Invalid date format".

I used this script component using the following code in between the adapters, However after 9,500 rows it failed again giving the same above error:

To convert Oracle timestamp to Sql Server timestamp

If Row.CALCULATEDETADATECUST_IsNull = False Then

If IsDate(DateSerial(Row.CALCULATEDETADATECUST.Year, Row.CALCULATEDETADATECUST.Month, Row.CALCULATEDETADATECUST.Day)) Then

dt = Row.CALCULATEDETADATECUST

Row.CALCULATEDETADATECUSTD = dt

End If

End If

I don't know if my code is right . Please inform, how i can achieve this.

What is the value of the offending row? Redirect the error rows from the SQL dest and see what the offending value is.

The fact that it gets through 9500 rows and then dies means your logic is right but there is a dodgy row.|||

Thanks for your support, Crispin,

I redirected the output at destination (Fail Point) as you said and can see that some columns are displaying the Errors instead of the actual data that gives the clue about which is invalid)

Column 5

Error: Year, Month and day parameters describe an unrepresnatable date and time...

I doubt that the DateSerial function in the script component is unable to extract some formats..

So,

1) Does anybody know which representations and range of Oracle (timestamp) is not allowed in Sql Server (Datetime)
2) Do we have a stable code that can perform Oracle timestamp to Sql Server conversion here

Thanks

Subhash Subramanyam

|||

Hi Experts in SSIS forum,

Thanks to LoRez who has raised this question this month again.. I am still having the same problem in SSIS. I have an Oracle timestamp Source Columns that should be populated into Sql Server Datetime columns. Though I am aware of the ranges valid for both the cases, I was unable to frame it properly. . The expression (This only checks the Date Part , I wanted code that converts the time part of the Oracle timestamp into Sql Server Datetime) I have used to convert this is:

(!ISNULL(REQUESTED_ETA_DATE_CUST) && DATEPART("yyyy",REQUESTED_ETA_DATE_CUST) > 1752 && DATEPART("yyyy",REQUESTED_ETA_DATE_CUST) < 9999) ? REQUESTED_ETA_DATE_CUST : NULL(DT_DBTIMESTAMP)

Though the expression syntax is valid, it still gives error when I run the dataflow. The error is:

[Derived Column [136]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (136)" failed because error code 0xC0049067 occurred, and the error row disposition on "input column "REQUESTED_ETA_DATE_CUST" (372)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

Though http://forums.informationbuilders.com/eve/forums/a/tpc/f/1381057331/m/7121008802 helps to create a trigger, but not the way we can achieve using SSIS.

Greatly Appreciate if anybody can pointer or paste the Expression or .NET code that can convert Oracle timestamp into Sql Server Datetime without issues

Thanks

Subhash Subramanyam

|||

Dear Umachandar, Thanks for briefing up the approach.

I'd appreciate if you go one step ahead to help my case work.Oracle Timestamp in my case is of the format 'MM/DD/YYYY hh:mm:ss.nnn' and I do not have any control over oracle source. Somehow I must be able to use this to populate the Sql Server datetime. We know that the year should be grater that 1752 which we can validate using above expression. But Validating TimePart seems to be difficult. I tried casting to string, but due to omission of leading zeros makes it difficult to substring that. So do you have a solution for this?

Umachandar's Reply

Oracle timestamp range subsumes that of SQL Server's datetime range. So you will not have any issues as long as you use the ISO 8601 format to specify the values (YYYY-MM-DDT hh:mm:ss.nnn). This will ensure that the value will be stored correctly irrespective of collation settings on either servers or Oracle session setttings. You can use timestamp with appropriate precision on Oracle side (timestamp(3) is closest) to match SQL Server datetime.

Migrating values from Oracle to SQL Server is a different ballgame. You will lose precision, values etc. Oracle has more richer support and wider ranges & ANSI SQL implementation.

Thanks

Subhash Subramanyam

|||

I've put up an article for this on my blog.

Cast this Oracle timestamp Column REQUESTED_ETA_DATE_CUST as below:

Decode(trunc((Extract(YEAR from REQUESTED_ETA_DATE_CUST))/1753), 0,'01/01/1753 12:00:00 AM', TO_CHAR(REQUESTED_ETA_DATE_CUST, 'MM/DD/YYYY hh:mms AM')) as REQUESTED_ETA_DATE_CUST

Or

Go for filtering valid ranges using SSIS transform after casting into a accepted datetime format.

Error while converting Oracle Timestamp to Sql Server Timestamp (datetime) - "Invalid date

I am populating oracle source in Sql Server Destination. after few rows it fails it displays this error:

[OLE DB Destination [16]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description:
"Invalid date format".

I used this script component using the following code in between the adapters, However after 9,500 rows it failed again giving the same above error:

To convert Oracle timestamp to Sql Server timestamp

If Row.CALCULATEDETADATECUST_IsNull = False Then

If IsDate(DateSerial(Row.CALCULATEDETADATECUST.Year, Row.CALCULATEDETADATECUST.Month, Row.CALCULATEDETADATECUST.Day)) Then

dt = Row.CALCULATEDETADATECUST

Row.CALCULATEDETADATECUSTD = dt

End If

End If

I don't know if my code is right . Please inform, how i can achieve this.

What is the value of the offending row? Redirect the error rows from the SQL dest and see what the offending value is.

The fact that it gets through 9500 rows and then dies means your logic is right but there is a dodgy row.|||

Thanks for your support, Crispin,

I redirected the output at destination (Fail Point) as you said and can see that some columns are displaying the Errors instead of the actual data that gives the clue about which is invalid)

Column 5

Error: Year, Month and day parameters describe an unrepresnatable date and time...

I doubt that the DateSerial function in the script component is unable to extract some formats..

So,

1) Does anybody know which representations and range of Oracle (timestamp) is not allowed in Sql Server (Datetime)
2) Do we have a stable code that can perform Oracle timestamp to Sql Server conversion here

Thanks

Subhash Subramanyam

|||

Hi Experts in SSIS forum,

Thanks to LoRez who has raised this question this month again.. I am still having the same problem in SSIS. I have an Oracle timestamp Source Columns that should be populated into Sql Server Datetime columns. Though I am aware of the ranges valid for both the cases, I was unable to frame it properly. . The expression (This only checks the Date Part , I wanted code that converts the time part of the Oracle timestamp into Sql Server Datetime) I have used to convert this is:

(!ISNULL(REQUESTED_ETA_DATE_CUST) && DATEPART("yyyy",REQUESTED_ETA_DATE_CUST) > 1752 && DATEPART("yyyy",REQUESTED_ETA_DATE_CUST) < 9999) ? REQUESTED_ETA_DATE_CUST : NULL(DT_DBTIMESTAMP)

Though the expression syntax is valid, it still gives error when I run the dataflow. The error is:

[Derived Column [136]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (136)" failed because error code 0xC0049067 occurred, and the error row disposition on "input column "REQUESTED_ETA_DATE_CUST" (372)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

Though http://forums.informationbuilders.com/eve/forums/a/tpc/f/1381057331/m/7121008802 helps to create a trigger, but not the way we can achieve using SSIS.

Greatly Appreciate if anybody can pointer or paste the Expression or .NET code that can convert Oracle timestamp into Sql Server Datetime without issues

Thanks

Subhash Subramanyam

|||

Dear Umachandar, Thanks for briefing up the approach.

I'd appreciate if you go one step ahead to help my case work.Oracle Timestamp in my case is of the format 'MM/DD/YYYY hh:mm:ss.nnn' and I do not have any control over oracle source. Somehow I must be able to use this to populate the Sql Server datetime. We know that the year should be grater that 1752 which we can validate using above expression. But Validating TimePart seems to be difficult. I tried casting to string, but due to omission of leading zeros makes it difficult to substring that. So do you have a solution for this?

Umachandar's Reply

Oracle timestamp range subsumes that of SQL Server's datetime range. So you will not have any issues as long as you use the ISO 8601 format to specify the values (YYYY-MM-DDT hh:mm:ss.nnn). This will ensure that the value will be stored correctly irrespective of collation settings on either servers or Oracle session setttings. You can use timestamp with appropriate precision on Oracle side (timestamp(3) is closest) to match SQL Server datetime.

Migrating values from Oracle to SQL Server is a different ballgame. You will lose precision, values etc. Oracle has more richer support and wider ranges & ANSI SQL implementation.

Thanks

Subhash Subramanyam

|||

I've put up an article for this on my blog.

Cast this Oracle timestamp Column REQUESTED_ETA_DATE_CUST as below:

Decode(trunc((Extract(YEAR from REQUESTED_ETA_DATE_CUST))/1753), 0,'01/01/1753 12:00:00 AM', TO_CHAR(REQUESTED_ETA_DATE_CUST, 'MM/DD/YYYY hh:mms AM')) as REQUESTED_ETA_DATE_CUST

Or

Go for filtering valid ranges using SSIS transform after casting into a accepted datetime format.