ETL Package Problem

I'm going through the Integration tutorial, Lesson 1: Creating a Simple ETL Package. Everything goes fine until I run the package. I get an error about inserting Null in the CurrencyKey field which is set to not null. The flat file looks okay.

Any help would be appreciated.

SSIS package "Lesson 1.dtsx" starting.
Information: 0x4004300A at Extract Sample Currency Data, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Extract Sample Currency Data, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Extract Sample Currency Data, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Extract Sample Currency Data, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Extract Sample Currency Data, Extract Sample Currency Data [1]: The processing of file "C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Sample Data\SampleCurrencyData.txt" has started.
Information: 0x400490F4 at Extract Sample Currency Data, Lookup Currency Key [30]: component "Lookup Currency Key" (30) has cached 105 rows.
Information: 0x400490F4 at Extract Sample Currency Data, Lookup Date Key [124]: component "Lookup Date Key" (124) has cached 992 rows.
Information: 0x4004300C at Extract Sample Currency Data, DTS.Pipeline: Execute phase is beginning.
Information: 0x402090DE at Extract Sample Currency Data, Extract Sample Currency Data [1]: The total number of data rows processed for file "C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Sample Data\SampleCurrencyData.txt" is 1097.
Information: 0x402090DF at Extract Sample Currency Data, Sample OLE DB Destination [158]: The final commit for the data insertion has started.
Error: 0xC0202009 at Extract Sample Currency Data, Sample OLE DB Destination [158]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot insert the value NULL into column 'CurrencyKey', table 'AdventureWorksDW.dbo.FactCurrencyRate'; column does not allow nulls. INSERT fails.".
Information: 0x402090E0 at Extract Sample Currency Data, Sample OLE DB Destination [158]: The final commit for the data insertion has ended.
Error: 0xC0047022 at Extract Sample Currency Data, DTS.Pipeline: The ProcessInput method on component "Sample OLE DB Destination" (158) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at Extract Sample Currency Data, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0202009.
Information: 0x40043008 at Extract Sample Currency Data, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Extract Sample Currency Data, Extract Sample Currency Data [1]: The processing of file "C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Sample Data\SampleCurrencyData.txt" has ended.
Information: 0x40043009 at Extract Sample Currency Data, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Extract Sample Currency Data, DTS.Pipeline: "component "Sample OLE DB Destination" (158)" wrote 1097 rows.
Task failed: Extract Sample Currency Data
Warning: 0x80019002 at Lesson 1: The Execution method succeeded, but the number of errors raised (3) 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 "Lesson 1.dtsx" finished: Failure.
The program '[3372] Lesson 1.dtsx: DTS' has exited with code 0 (0x0).

CurrencyKey Column is set to Not Null means, it cannot take Null values.

If I remember right, CurrencyKey Column of the AdventureWorksDW database is a Primary key.

Thanks,

S Suresh

|||Then there is a problem with the tutorial. Do you know how to fix the problem?
|||

Hi kinny_k,

I was just wondering if you ever got an answer to this problem with the SSIS ETL tutorial. I've run into the same issue and I've checked and rechecked my steps and can not see anything wrong.

If you have the solution/workaround I'd appreciate the input!

-mayalu

|||

Did you review if any of the mapping in your destination componnet is missing?

Rafael Salas

|||

I had a problem with this too. It turns out that the Flat File dates are in a MM/DD/YYYY format and since my default is DD/MM/YYYY most dates aren't correctly identified.

To resolve this:

1. In the ‘Flat File Connection Manager’, changed the CurrencyDate DataType to string [DT_STR].

2. In the ‘Data Flow’ diagram, a Warning displays on the ‘Extract Sample Currency Data’ dataflow.Double click to open it. A message is displayed warning that the format has changed, click Yes and when the ‘Flat File Source Editor’ is displayed, click OK to close.

3. In the Date Lookup Transformation Editor, Reference Table tab, select 'Use results of an SQL query' and paste in:

SELECT TimeKey, FullDateAlternateKey,
CAST(CAST(CONVERT(VARCHAR(2), FullDateAlternateKey, 101) AS INT) AS VARCHAR) + '/' +
CAST(CAST(SUBSTRING(CONVERT(VARCHAR(5), FullDateAlternateKey, 101), 4,2) AS INT) AS VARCHAR) + '/' +
CAST(CAST(RIGHT(CONVERT(VARCHAR(10), FullDateAlternateKey, 101),4) AS INT) AS VARCHAR) + ' 0:00' USFormatDate
FROM dbo.DimTime

Check to output looks OK by clicking the preview button.

4. In the Date Lookup Transformation Editor, Columns tab, link the CurrencyDate to the new USFormatDate field.

I'd imagine there's a nicer way of fixing this but I'm not at all familiar with SSIS yet.

|||

Hi Tim,

Thanks for the input. I tried it and all goes well until the last step where I try to map the CurrencyDate to the new USFormatDate field where I get a data type mismatch. I guess there's something else peculiar to my setup.

At least I know where to look closer!

-Elsie

|||

Elsie,

Hum, I think I'd check the first step again. You have to change the flat file Currency Date to be a string otherwise the comparison wont work and you’ll get a type mismatch. Other than that, I'm afraid I don't know.

You could always change the FactCurrencyRate table definition to allow Null in the TimeKey field so you don't get the error. In retrospect I should have done that as it’s a lot quicker to get the lesson working.

Tim

|||It seems that the only solution is to use SSIS in a db instance with English(US) as default language.
At least, for db instances with non-English default languages (I have Russian one) nothing else works
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1566456&SiteID=1&mode=1|||

In both step 9's of the add lookup transformation, be sure to click on the box to select the(CurrencyKey) lookup value. You should have a checkbox before moving on. Then you can pam this lookup to the column in the later step.

Enjoy!

|||I am sure I verifшув that (and everything else) dozens of times,
BTW it does not address the error and the problem (incompatible datetime fields)|||

To get this fully working, following steps are required:

1. In the Sample Flat File Source Data connection manager, change CurrencyDate Data Type to String [DT_STR]

2. In the data flow task editor, double click data Flow souce 'Extract Sample Currency Data' then accept the warning

3. In Look Up Time Key task, under Reference Table tab, choose use Results of an SQL query, then use the following SQL statement, which I modified from an early post,

SELECT TimeKey, FullDateAlternateKey,
CAST(CAST(CONVERT(VARCHAR(2), FullDateAlternateKey, 101) AS INT) AS VARCHAR)+ '/'
+
CAST(CAST(SUBSTRING(CONVERT(VARCHAR(5), FullDateAlternateKey, 101), 4,2) AS INT) AS VARCHAR) + '/' +
CAST(CAST(RIGHT(CONVERT(VARCHAR(10), FullDateAlternateKey, 101),4) AS INT) AS VARCHAR) + ' 00:00:00' USFormatDate
FROM dbo.DimTime

4. In Look Up Time Key task, Link Currency date from Available Input Columns to USFormatDate in Available Look UP Columns.

5. Make sure you link the output of the look up to the destination columns.

Good luck,

Celine

|||

Another solution:

on http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

you find database for case-sensitive collation and case-insensitive collation, a attached the other database and now works.

|||

This link tells that samples download had moved to codeplex

And codeplex has hundreds of samples

Which one?

|||http://www.codeplex.com/MSFTDBProdSamples

ETL Package Problem

I'm going through the Integration tutorial, Lesson 1: Creating a Simple ETL Package. Everything goes fine until I run the package. I get an error about inserting Null in the CurrencyKey field which is set to not null. The flat file looks okay.

Any help would be appreciated.

SSIS package "Lesson 1.dtsx" starting.
Information: 0x4004300A at Extract Sample Currency Data, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Extract Sample Currency Data, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Extract Sample Currency Data, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Extract Sample Currency Data, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Extract Sample Currency Data, Extract Sample Currency Data [1]: The processing of file "C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Sample Data\SampleCurrencyData.txt" has started.
Information: 0x400490F4 at Extract Sample Currency Data, Lookup Currency Key [30]: component "Lookup Currency Key" (30) has cached 105 rows.
Information: 0x400490F4 at Extract Sample Currency Data, Lookup Date Key [124]: component "Lookup Date Key" (124) has cached 992 rows.
Information: 0x4004300C at Extract Sample Currency Data, DTS.Pipeline: Execute phase is beginning.
Information: 0x402090DE at Extract Sample Currency Data, Extract Sample Currency Data [1]: The total number of data rows processed for file "C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Sample Data\SampleCurrencyData.txt" is 1097.
Information: 0x402090DF at Extract Sample Currency Data, Sample OLE DB Destination [158]: The final commit for the data insertion has started.
Error: 0xC0202009 at Extract Sample Currency Data, Sample OLE DB Destination [158]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot insert the value NULL into column 'CurrencyKey', table 'AdventureWorksDW.dbo.FactCurrencyRate'; column does not allow nulls. INSERT fails.".
Information: 0x402090E0 at Extract Sample Currency Data, Sample OLE DB Destination [158]: The final commit for the data insertion has ended.
Error: 0xC0047022 at Extract Sample Currency Data, DTS.Pipeline: The ProcessInput method on component "Sample OLE DB Destination" (158) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at Extract Sample Currency Data, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0202009.
Information: 0x40043008 at Extract Sample Currency Data, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Extract Sample Currency Data, Extract Sample Currency Data [1]: The processing of file "C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Sample Data\SampleCurrencyData.txt" has ended.
Information: 0x40043009 at Extract Sample Currency Data, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Extract Sample Currency Data, DTS.Pipeline: "component "Sample OLE DB Destination" (158)" wrote 1097 rows.
Task failed: Extract Sample Currency Data
Warning: 0x80019002 at Lesson 1: The Execution method succeeded, but the number of errors raised (3) 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 "Lesson 1.dtsx" finished: Failure.
The program '[3372] Lesson 1.dtsx: DTS' has exited with code 0 (0x0).

CurrencyKey Column is set to Not Null means, it cannot take Null values.

If I remember right, CurrencyKey Column of the AdventureWorksDW database is a Primary key.

Thanks,

S Suresh

|||Then there is a problem with the tutorial. Do you know how to fix the problem?
|||

Hi kinny_k,

I was just wondering if you ever got an answer to this problem with the SSIS ETL tutorial. I've run into the same issue and I've checked and rechecked my steps and can not see anything wrong.

If you have the solution/workaround I'd appreciate the input!

-mayalu

|||

Did you review if any of the mapping in your destination componnet is missing?

Rafael Salas

|||

I had a problem with this too. It turns out that the Flat File dates are in a MM/DD/YYYY format and since my default is DD/MM/YYYY most dates aren't correctly identified.

To resolve this:

1. In the ‘Flat File Connection Manager’, changed the CurrencyDate DataType to string [DT_STR].

2. In the ‘Data Flow’ diagram, a Warning displays on the ‘Extract Sample Currency Data’ dataflow.Double click to open it. A message is displayed warning that the format has changed, click Yes and when the ‘Flat File Source Editor’ is displayed, click OK to close.

3. In the Date Lookup Transformation Editor, Reference Table tab, select 'Use results of an SQL query' and paste in:

SELECT TimeKey, FullDateAlternateKey,
CAST(CAST(CONVERT(VARCHAR(2), FullDateAlternateKey, 101) AS INT) AS VARCHAR) + '/' +
CAST(CAST(SUBSTRING(CONVERT(VARCHAR(5), FullDateAlternateKey, 101), 4,2) AS INT) AS VARCHAR) + '/' +
CAST(CAST(RIGHT(CONVERT(VARCHAR(10), FullDateAlternateKey, 101),4) AS INT) AS VARCHAR) + ' 0:00' USFormatDate
FROM dbo.DimTime

Check to output looks OK by clicking the preview button.

4. In the Date Lookup Transformation Editor, Columns tab, link the CurrencyDate to the new USFormatDate field.

I'd imagine there's a nicer way of fixing this but I'm not at all familiar with SSIS yet.

|||

Hi Tim,

Thanks for the input. I tried it and all goes well until the last step where I try to map the CurrencyDate to the new USFormatDate field where I get a data type mismatch. I guess there's something else peculiar to my setup.

At least I know where to look closer!

-Elsie

|||

Elsie,

Hum, I think I'd check the first step again. You have to change the flat file Currency Date to be a string otherwise the comparison wont work and you’ll get a type mismatch. Other than that, I'm afraid I don't know.

You could always change the FactCurrencyRate table definition to allow Null in the TimeKey field so you don't get the error. In retrospect I should have done that as it’s a lot quicker to get the lesson working.

Tim

|||It seems that the only solution is to use SSIS in a db instance with English(US) as default language.
At least, for db instances with non-English default languages (I have Russian one) nothing else works
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1566456&SiteID=1&mode=1|||

In both step 9's of the add lookup transformation, be sure to click on the box to select the(CurrencyKey) lookup value. You should have a checkbox before moving on. Then you can pam this lookup to the column in the later step.

Enjoy!

|||I am sure I verifшув that (and everything else) dozens of times,
BTW it does not address the error and the problem (incompatible datetime fields)|||

To get this fully working, following steps are required:

1. In the Sample Flat File Source Data connection manager, change CurrencyDate Data Type to String [DT_STR]

2. In the data flow task editor, double click data Flow souce 'Extract Sample Currency Data' then accept the warning

3. In Look Up Time Key task, under Reference Table tab, choose use Results of an SQL query, then use the following SQL statement, which I modified from an early post,

SELECT TimeKey, FullDateAlternateKey,
CAST(CAST(CONVERT(VARCHAR(2), FullDateAlternateKey, 101) AS INT) AS VARCHAR)+ '/'
+
CAST(CAST(SUBSTRING(CONVERT(VARCHAR(5), FullDateAlternateKey, 101), 4,2) AS INT) AS VARCHAR) + '/' +
CAST(CAST(RIGHT(CONVERT(VARCHAR(10), FullDateAlternateKey, 101),4) AS INT) AS VARCHAR) + ' 00:00:00' USFormatDate
FROM dbo.DimTime

4. In Look Up Time Key task, Link Currency date from Available Input Columns to USFormatDate in Available Look UP Columns.

5. Make sure you link the output of the look up to the destination columns.

Good luck,

Celine

|||

Another solution:

on http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

you find database for case-sensitive collation and case-insensitive collation, a attached the other database and now works.

|||

This link tells that samples download had moved to codeplex

And codeplex has hundreds of samples

Which one?

|||http://www.codeplex.com/MSFTDBProdSamples

essbase and integration services

is it possible to use integration services to extract data from essbase?

Shahab03 wrote:

is it possible to use integration services to extract data from essbase?

Do you kow if OLE DB or ADO.Net providers exist for Essbase? If so, the answer to your question is 'Yes'.

-Jamie

|||Check out http://ssis.wik.is/Data_Sources - Essbase connectivity available from Star Analytics. Click on the link on the left sidebar for more info on StarAnalytics.

essbase and integration services

is it possible to use integration services to extract data from essbase?

Shahab03 wrote:

is it possible to use integration services to extract data from essbase?

Do you kow if OLE DB or ADO.Net providers exist for Essbase? If so, the answer to your question is 'Yes'.

-Jamie

|||Check out http://ssis.wik.is/Data_Sources - Essbase connectivity available from Star Analytics. Click on the link on the left sidebar for more info on StarAnalytics.sql

Errors with SRS in Sharepoint Integration Mode

Hi Everyone,

Our project has two servers. One server is running WSS 3.0 and the SQL Reporting Services web front-end. We have a second server that operates all the databases involved (WSS Content, SRS, and our application specific DB).

We do not have a domain controller in our architecture. The WSS site is configured to use SQL authentication to connnect and modify the WSS Content database. We installed SRS, SRS service pack 2, and the Sharepoint add-in. We are using forms authentication for our WSS site. We would like to use SRS in Sharepoint Integration mode, but we are unable to create datasources, view reports, etc. SRS is configured to use the "Trusted Account" in WSS Central Admin.

When we attempt to create a new datasource, and we received an error stating the object moved and to check that SRS is in Sharepoint integration mode.

When we attempt to view a report, we get an error stating:

"An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode."

I have a feeling this has to do with the Windows Service Account and the Web Service Identity for SRS. What type of account should we use since we do not have a domain?

Thank you,

Michael milazzo

As explained here, in Trusted Account mode, WSS sends the requests to the Report Server under the identity of the SharePoint application. By default, the IIS 6.0 application pool runs under NT AUTHORITY\Network Service. Since you don't have a domain setup, try changing the application domain identity to a local Windows user account which has the same name and password as a privileged account on the Report Server that has access to the report catalog. If you had domain setup, the request could have gone under Domain\WSS machine name$ and you could have granted access to that account.

Errors from training mining models in SSIS

I am in the process of creating an Integration Services package to automate the process of training mining models and getting predictions. Until recently, I have been processing the models directly from Business Intelligence Studio without any problems. However, when I try to use the exact same training set as an input to the Data Mining Model Training destination, I get several errors. Here is the output:

[Mining Models [1]] Error: Parser: An error occurred during pipeline processing.

[Mining Models [1]] Error: Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.

[Mining Models [1]] Error: Errors in the OLAP storage engine: An error occurred while the 'CPT MODIFIER' attribute of the 'BCCA DMS ~MC-CLAIM LIN~5' dimension from the 'BCCA LRG DMS TEST' database was being processed.

[Mining Models [1]] Error: File system error: The record ID is incorrect. Physical file: . Logical file: .

[Mining Models [1]] Error: Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.

[Mining Models [1]] Error: Errors in the OLAP storage engine: An error occurred while the 'BILL TYPE' attribute of the 'BCCA DMS ~MC-CLAIM LIN~5' dimension from the 'BCCA LRG DMS TEST' database was being processed.

[Mining Models [1]] Error: File system error: The record ID is incorrect. Physical file: . Logical file: .

[DTS.Pipeline] Error: The ProcessInput method on component "Mining Models" (1) failed with error code 0x80004005. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

I have not been able to find an answer as to why this is happening. I found a post regarding a similar problem with processing an OLAP cube in SSIS, but it seems that the author of that post never found an answer. Has anyone else here seen similar errors when processing mining models from Integration Services?

Also, if I process the mining models manually then try to run only predictions in SSIS, I get many of the same errors. I'll keep looking into the problem myself, but I would be very grateful if someone in this forum could shed some light on this issue.

Can you try to sort your input ascendingly by the column that ends up being mapped to the model's key column?

if this does not work, could you please post some additional information:

- what algorithm is used by your mining model?

- are there other mining modles in the same mining structure? If so, what algorithms do they use?

- the columns of your mining model, their data type and content type

- the structure of the Integration Services pipeline (datatypes for the columns and ther mappings)

|||

Bogdan,

Thanks for your quick reply! I was actually able to fix this problem by eliminating one of the columns from my mining structure. It was a varchar field, but it turns out that there was no useful information in that column (every row contained a blank entry). SSIS was throwing an error when trying to build a dimension for that field. Obviously it's no loss to eliminate a column with no real values in it, but I'm still a little confused as to why it would process through an Analysis Services project but not in SSIS.

|||SSIS has different data handling and transformation logic than AS - it may just be that the conversion from the database type to the internal type failed in SSIS but normally works in AS.

Error: SSIS Runtime Object could not be created.

I am using SQL Server 2005 Version 9.00.1399.06 (Intel X86).

Whether I try to start a new Integration Services project in Visual Studio or try to import data into a database I receive the following error:

SSIS Runtime Object could not be created. Verify the DTS.dll is available and registered. The wizard can not continue and will terminate.

Unable to cast COM of type 'Microsoft.SqlServer.DTS.Runtime.Wrapper.PackageNeutralClass' to interface type
'Microsoft .SqlServer.DTS.Runtime.Wrapper.IDTSContainer90'
This operation failed because the QueryInterface call on the COM component for the interface failed due to the following error: Library not registered. (Exception from HRESULT: 0X80028010) (TYPE_E_LIBNOTREGISTERED)).

I have no idea why I am getting this error and would appreciate any help. I'm sure some of you SQL Server Gurus will know right away.

This is setup problem, please reinstal the product or consult SQL Setup forum.

You can also try running 'regsvr32 dts.dll', which will probably fix this particular problem.|||

I had the same problem and I tried running regsvr32 dts.dll and I got this error.

dts.dll in not an executable file and no registration helper is registered for this file type.

Please advise.

i also tried to re-install SP1 but Integration Services and Reporting Services were greyed out. I wonder if I had to re-install everything all over again..please advise.

|||

Running resvr32 dts.dll from the dos command prompt worked for me.

Kim

Error: SSIS Runtime Object could not be created.

I am using SQL Server 2005 Version 9.00.1399.06 (Intel X86).

Whether I try to start a new Integration Services project in Visual Studio or try to import data into a database I receive the following error:

SSIS Runtime Object could not be created. Verify the DTS.dll is available and registered. The wizard can not continue and will terminate.

Unable to cast COM of type 'Microsoft.SqlServer.DTS.Runtime.Wrapper.PackageNeutralClass' to interface type
'Microsoft .SqlServer.DTS.Runtime.Wrapper.IDTSContainer90'
This operation failed because the QueryInterface call on the COM component for the interface failed due to the following error: Library not registered. (Exception from HRESULT: 0X80028010) (TYPE_E_LIBNOTREGISTERED)).

I have no idea why I am getting this error and would appreciate any help. I'm sure some of you SQL Server Gurus will know right away.

This is setup problem, please reinstal the product or consult SQL Setup forum.

You can also try running 'regsvr32 dts.dll', which will probably fix this particular problem.|||

I had the same problem and I tried running regsvr32 dts.dll and I got this error.

dts.dll in not an executable file and no registration helper is registered for this file type.

Please advise.

i also tried to re-install SP1 but Integration Services and Reporting Services were greyed out. I wonder if I had to re-install everything all over again..please advise.

|||

Running resvr32 dts.dll from the dos command prompt worked for me.

Kim

|||

Run regsvr32.exe with the other dll's located in the folder of dts.dll (c:\Program Files\Microsoft SQL Server\90\DTS\Binn). Some will be accepted by regsvr32.exe and some not. It's not important. When I tried regsrv32 dts.dll only I had the problem. After running regsvr32 with all the dll everything was OK.

Error: SSIS Runtime Object could not be created.

I am using SQL Server 2005 Version 9.00.1399.06 (Intel X86).

Whether I try to start a new Integration Services project in Visual Studio or try to import data into a database I receive the following error:

SSIS Runtime Object could not be created. Verify the DTS.dll is available and registered. The wizard can not continue and will terminate.

Unable to cast COM of type 'Microsoft.SqlServer.DTS.Runtime.Wrapper.PackageNeutralClass' to interface type
'Microsoft .SqlServer.DTS.Runtime.Wrapper.IDTSContainer90'
This operation failed because the QueryInterface call on the COM component for the interface failed due to the following error: Library not registered. (Exception from HRESULT: 0X80028010) (TYPE_E_LIBNOTREGISTERED)).

I have no idea why I am getting this error and would appreciate any help. I'm sure some of you SQL Server Gurus will know right away.

This is setup problem, please reinstal the product or consult SQL Setup forum.

You can also try running 'regsvr32 dts.dll', which will probably fix this particular problem.|||

I had the same problem and I tried running regsvr32 dts.dll and I got this error.

dts.dll in not an executable file and no registration helper is registered for this file type.

Please advise.

i also tried to re-install SP1 but Integration Services and Reporting Services were greyed out. I wonder if I had to re-install everything all over again..please advise.

|||

Running resvr32 dts.dll from the dos command prompt worked for me.

Kim

Error: Server Agent cant find dts file

Im trying to schedule an integration sevice package with no anvile. I have tried both running it in Agent under Integration services and operation system (CmdExec) and still wont work. Under CmdExec, the command is /FILE "C:\Documents and Settings\ebuah\My Documents\Visual Studio 2005\Projects\Swamp_Solution\FIRSTTIME_DOWNLOAD\SSIS_ACT.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF. In the advance tab, I checked outfile to a log.txt file so as to read the log if there was any errors. The account running the job is the same account that sql server agent services is running which would only mean I really dont need any proxy account to run the job. When i run the job under CmdExec, i get the error

Message
Executed as user: CYPRESS\Administrator. The process could not be created for step 1 of job 0x529C255F20199B40A83858B1CCC875F2 (reason: The system cannot find the file specified). The step failed.

when I run the same package under integeration system, the error I get in the logg file is

<record>
<event>OnError</event>
<message>System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)</message>
<computer>SPIDERMAN</computer>
<operator>CYPRESS\Administrator</operator>
<source>ACT TABLE 250</source>
<sourceid>{99b92f4c-686d-47a6-b136-4d1ec698c837}</sourceid>
<executionid>{CD95BB1A-7FE4-468B-904D-C02B3FEBF1B0}</executionid>
<starttime>4/17/2006 1:20:47 PM</starttime>
<endtime>4/17/2006 1:20:47 PM</endtime>
<datacode>-1073450910</datacode>
<databytes>0x</databytes>
</record>

If you ask me, I dont know whats going on but I am very fustrated. Have been working on this for 4 days now. I really really need help.

The error is right there. "Data source name not found".

I would guess that either you haven't deployed the ODBC DSN on that machine or the account you are running it as doesn't have permission to see it.

-Jamie

|||I am running on a 64x machine on which i installed the 64x version of SSIS. The odbc driver in installed in the wow folder (32 bit). My packages are set up to execute in 32bit so they can see the odbc driver. Do you think that under server agent, its not able to see the 32bit odbc driver. (by the way, the account i running under is an admin account, has rights to everything)

Also, in the process of debugging, I tried a few things to eliminate causing senarios. I created a simple parkage that doesnt use the odbc drive. It does a staight pull from one sql server table to another using ole connection . When i run that under Sever Agent with Integration services, it runs smoothly. For the same simple package, when i run under CmdExec, I get the error: The process could not be created for step 1 of job 0xF791BD51E4709E48AD83FB56038ECF95 (reason: The system cannot find the file specified). The step failed. So, my question is what is wrong. It runs fine under integeration services but errors out under CmdExec.

From my first test, I made an assumption that only running under intergration service in Sever Agent will work since its passed the test. Based on that, I changed my simple package to include an odbc source connection (pulling from a remote DB/4 FC server with 32bit DB/4 odbc driver installed in wow on 64bit). When I run the package in Agent under integration services, I got an error: The package execution failed. The step failed. Luckly, a detailed error information by the package log indicated

<message>System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)</message>

which was my intial error. So Jamie, I think you are right that it has something to do with the DNS. Since the odbc drive is registered in the 32bit DNS, Server Agent is not able to see it.

If you can help me answer my trial error as to how a package could runs fine under intergeration services but breakes under CmdExec (does the command sting have to be different) and also if there is a way to get around this driver issue. Thanks.|||I tried a 32bit install of sql server on a 32bit machine and the job run just fine so the problem is that Sever Agent on 64bit machine can only see the 64x DNS and not 32 but in SSIS, the IDE give you the option to run in 64bit debugging mode or not, and when you choose not to run in 64 mode, it able to see the 32bit DNS. That was why it was able to run in SSIS just fine but not in Server Agent. In summary, make sure your driver has a 64bit version before developing packages to be run in Server Agent on a 64bit machine.|||I am having the same problem. It looks like when I run the SSIS package through the SQL Server Management Studio or through Visual Studio, the package honors the 64 bit runtime option. But, when I run it through the SQL Server Agent, it doesn't. I did some checking, and it looks like the package file doesn't even contain that flag. Is there some way to alter the command line in the job step to force the package to run in 32 bit mode?|||You could just use the CmdExec job step and specify the 32 bit dtexec program. This has other benefits, in that you have job step logging for CmdExec steps, unlike the SSIS subssytem. This can be very usefull in troubleshooting, e.g. Scheduled Packages
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html)|||Thanks. That seems to be working.

Error: Server Agent cant find dts file

Im trying to schedule an integration sevice package with no anvile. I have tried both running it in Agent under Integration services and operation system (CmdExec) and still wont work. Under CmdExec, the command is /FILE "C:\Documents and Settings\ebuah\My Documents\Visual Studio 2005\Projects\Swamp_Solution\FIRSTTIME_DOWNLOAD\SSIS_ACT.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF. In the advance tab, I checked outfile to a log.txt file so as to read the log if there was any errors. The account running the job is the same account that sql server agent services is running which would only mean I really dont need any proxy account to run the job. When i run the job under CmdExec, i get the error

Message
Executed as user: CYPRESS\Administrator. The process could not be created for step 1 of job 0x529C255F20199B40A83858B1CCC875F2 (reason: The system cannot find the file specified). The step failed.

when I run the same package under integeration system, the error I get in the logg file is

<record>
<event>OnError</event>
<message>System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)</message>
<computer>SPIDERMAN</computer>
<operator>CYPRESS\Administrator</operator>
<source>ACT TABLE 250</source>
<sourceid>{99b92f4c-686d-47a6-b136-4d1ec698c837}</sourceid>
<executionid>{CD95BB1A-7FE4-468B-904D-C02B3FEBF1B0}</executionid>
<starttime>4/17/2006 1:20:47 PM</starttime>
<endtime>4/17/2006 1:20:47 PM</endtime>
<datacode>-1073450910</datacode>
<databytes>0x</databytes>
</record>

If you ask me, I dont know whats going on but I am very fustrated. Have been working on this for 4 days now. I really really need help.

The error is right there. "Data source name not found".

I would guess that either you haven't deployed the ODBC DSN on that machine or the account you are running it as doesn't have permission to see it.

-Jamie

|||I am running on a 64x machine on which i installed the 64x version of SSIS. The odbc driver in installed in the wow folder (32 bit). My packages are set up to execute in 32bit so they can see the odbc driver. Do you think that under server agent, its not able to see the 32bit odbc driver. (by the way, the account i running under is an admin account, has rights to everything)

Also, in the process of debugging, I tried a few things to eliminate causing senarios. I created a simple parkage that doesnt use the odbc drive. It does a staight pull from one sql server table to another using ole connection . When i run that under Sever Agent with Integration services, it runs smoothly. For the same simple package, when i run under CmdExec, I get the error: The process could not be created for step 1 of job 0xF791BD51E4709E48AD83FB56038ECF95 (reason: The system cannot find the file specified). The step failed. So, my question is what is wrong. It runs fine under integeration services but errors out under CmdExec.

From my first test, I made an assumption that only running under intergration service in Sever Agent will work since its passed the test. Based on that, I changed my simple package to include an odbc source connection (pulling from a remote DB/4 FC server with 32bit DB/4 odbc driver installed in wow on 64bit). When I run the package in Agent under integration services, I got an error: The package execution failed. The step failed. Luckly, a detailed error information by the package log indicated

<message>System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)</message>

which was my intial error. So Jamie, I think you are right that it has something to do with the DNS. Since the odbc drive is registered in the 32bit DNS, Server Agent is not able to see it.

If you can help me answer my trial error as to how a package could runs fine under intergeration services but breakes under CmdExec (does the command sting have to be different) and also if there is a way to get around this driver issue. Thanks.|||I tried a 32bit install of sql server on a 32bit machine and the job run just fine so the problem is that Sever Agent on 64bit machine can only see the 64x DNS and not 32 but in SSIS, the IDE give you the option to run in 64bit debugging mode or not, and when you choose not to run in 64 mode, it able to see the 32bit DNS. That was why it was able to run in SSIS just fine but not in Server Agent. In summary, make sure your driver has a 64bit version before developing packages to be run in Server Agent on a 64bit machine.|||I am having the same problem. It looks like when I run the SSIS package through the SQL Server Management Studio or through Visual Studio, the package honors the 64 bit runtime option. But, when I run it through the SQL Server Agent, it doesn't. I did some checking, and it looks like the package file doesn't even contain that flag. Is there some way to alter the command line in the job step to force the package to run in 32 bit mode?|||You could just use the CmdExec job step and specify the 32 bit dtexec program. This has other benefits, in that you have job step logging for CmdExec steps, unlike the SSIS subssytem. This can be very usefull in troubleshooting, e.g. Scheduled Packages
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html)|||Thanks. That seems to be working.