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

Establishing VPN Connection thru DTS package

Hi,
I have defined a DTS package which establishes VPN connection with my
client's server. When I execute this package thru SQL Enterprise
Manager (SEM) it works fine, but when I schedule it as a job or try to
execute thru SQL Query Analyzer using 'xp_cmdShell' stored proc, it
doesn't establish connection. NE ideas on what am I doing wrong?
Yr prompt response will be highly appreciated.
Thanks,
Um JunkWhen you execute it using xp_cmdshell the package is run under the context
of the SQL Server startup account (if the user connected via Query Analyzer
is a member of the sysadmin role) or the SQL Proxy account (if the user
connected via Query Analyzer is not a member of the sysadmin role).
Login to the machine using these accounts and see if you can run the
package from Enterprise Manager. This could give you some additional
information.
Rand
This posting is provided "as is" with no warranties and confers no rights.sql

Establishing VPN Connection thru DTS package

Hi,
I have defined a DTS package which establishes VPN connection with my
client's server. When I execute this package thru SQL Enterprise
Manager (SEM) it works fine, but when I schedule it as a job or try to
execute thru SQL Query Analyzer using 'xp_cmdShell' stored proc, it
doesn't establish connection. NE ideas on what am I doing wrong?
Yr prompt response will be highly appreciated.
Thanks,
Um Junk
When you execute it using xp_cmdshell the package is run under the context
of the SQL Server startup account (if the user connected via Query Analyzer
is a member of the sysadmin role) or the SQL Proxy account (if the user
connected via Query Analyzer is not a member of the sysadmin role).
Login to the machine using these accounts and see if you can run the
package from Enterprise Manager. This could give you some additional
information.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Escape character for /SET option of dtexec

I have a problem setting some variables in a package using the /SET option of dtexec. Specifically when the value I want to set contains a semi-colon. I get an error like:

Argument ""\Package.Variables[User::Delim].Properties[Value];^;"" for option "set" is not valid.

I am guessing that I will have to escape the semi-colons somehow, but with what?

Regards,
Lars

You don't show the command but from the error it looks like

/set "\Package.Variables[User::Delim].Properties[Value];^;"

instead try

/set "\Package.Variables[User::Delim].Properties[Value]";"^;"

This puts the semicolon separator outside the quotes and may allow DTExec to process the argument.

HTH,

Matt

|||

Matt,

I am afraid that didn't work either. Any other ideas of how to pass a string containing semi-colons to dtexec using the command line?

This is the output:
H:\>dtexec /SET "\Package.Variables\[User::TheVariable].Properties[Value]";"^;"
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Argument ""\Package.Variables\[User::TheVariable].Properties[Value];^;"" for opt
ion "set" is not valid.

H:\>

Regards,
Lars

|||

Sorry I should have been more explicit in my posting. You should try:

dtexec /SET "\"\Package.Variables\[User::TheVariable].Properties[Value]\";\"^;\""

The command interpreter strips quotes so the command I showed previously was what DTExec would work with but you have to escape the quotes so that they get to the DTExec parser. The above shows the actual command line with all the escaping.

HTH,

Matt

|||

The following turned out to be the proper syntax:

dtexec /SET \Package.Variables[User::TheVariable].Properties[Value];\"^;\"

Regards,
Lars

|||

The above did not work when the string contained spaces. The following has yet not failed though:

dtexec /SET \Package.Variables[User::TheVariable].Properties[Value];\""; space"\"

Regards,
Lars

Escape character for /SET option of dtexec

I have a problem setting some variables in a package using the /SET option of dtexec. Specifically when the value I want to set contains a semi-colon. I get an error like:

Argument ""\Package.Variables[User::Delim].Properties[Value];^;"" for option "set" is not valid.

I am guessing that I will have to escape the semi-colons somehow, but with what?

Regards,
Lars

You don't show the command but from the error it looks like

/set "\Package.Variables[User::Delim].Properties[Value];^;"

instead try

/set "\Package.Variables[User::Delim].Properties[Value]";"^;"

This puts the semicolon separator outside the quotes and may allow DTExec to process the argument.

HTH,

Matt

|||

Matt,

I am afraid that didn't work either. Any other ideas of how to pass a string containing semi-colons to dtexec using the command line?

This is the output:
H:\>dtexec /SET "\Package.Variables\[User::TheVariable].Properties[Value]";"^;"
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Argument ""\Package.Variables\[User::TheVariable].Properties[Value];^;"" for opt
ion "set" is not valid.

H:\>

Regards,
Lars

|||

Sorry I should have been more explicit in my posting. You should try:

dtexec /SET "\"\Package.Variables\[User::TheVariable].Properties[Value]\";\"^;\""

The command interpreter strips quotes so the command I showed previously was what DTExec would work with but you have to escape the quotes so that they get to the DTExec parser. The above shows the actual command line with all the escaping.

HTH,

Matt

|||

The following turned out to be the proper syntax:

dtexec /SET \Package.Variables[User::TheVariable].Properties[Value];\"^;\"

Regards,
Lars

|||

The above did not work when the string contained spaces. The following has yet not failed though:

dtexec /SET \Package.Variables[User::TheVariable].Properties[Value];\""; space"\"

Regards,
Lars

sql

errors while running ssis packages from the command prompt.

I have created ssis package. It has been successfully running at UI level.

But when i am trying to execute it from the command prompt by using dtexec utility it is showing the following error messages.

Error: 2005-12-23 17:01:57.67
Code: 0xC00470FE
Source: Data Flow Task DTS.Pipeline
Description: The product level is insufficient for component "Flat File Source" (1).
End Error
Error: 2005-12-23 17:01:57.67
Code: 0xC00470FE
Source: Data Flow Task DTS.Pipeline
Description: The product level is insufficient for component "Script Component" (9).
End Error

i have entered the command as follows.

dtexec /f "c:\somefolder\package1.dtsx"

Any points regarding this issue would be helpful.

Take a look here:

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

Same problem is being discussed.

HTH,
Ovidiu

|||

I am receiving a similar error. We are running SS2005 Enterprise Edition. We can connect to SSIS with SS Management Studio so that verifies that it's installed. Here is our error log. Did you solve your probelm, and if so, how?

*************************************Run GPS_EXTRACT_QA.dtsx*********************************************
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 5:14:34 PM
Progress: 2006-01-26 17:14:36.01
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2006-01-26 17:14:36.03
Source: Data Flow Task
Validating: 50% complete
End Progress
Progress: 2006-01-26 17:14:36.21
Source: Data Flow Task
Validating: 100% complete
End Progress
Progress: 2006-01-26 17:14:36.53
Source: Execute SQL Task
Executing query "truncate table sap_extract_staging
delete from sap".: 100% complete
End Progress
Progress: 2006-01-26 17:14:36.54
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2006-01-26 17:14:36.56
Source: Data Flow Task
Validating: 50% complete
End Progress
Progress: 2006-01-26 17:14:36.60
Source: Data Flow Task
Validating: 100% complete
End Progress
Error: 2006-01-26 17:14:36.60
Code: 0xC00470FE
Source: Data Flow Task DTS.Pipeline
Description: The product level is insufficient for component "Flat File Source" (749).
End Error
Warning: 2006-01-26 17:14:36.60
Code: 0x80019002
Source: GPS_EXTRACT_QA
Description: 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.
End Warning
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 5:14:34 PM
Finished: 5:14:36 PM
Elapsed: 2.562 seconds

|||

I am also getting same error.

any sugessions regarding this issue

sql

errors while running ssis packages from the command prompt.

I have created ssis package. It has been successfully running at UI level.

But when i am trying to execute it from the command prompt by using dtexec utility it is showing the following error messages.

Error: 2005-12-23 17:01:57.67
Code: 0xC00470FE
Source: Data Flow Task DTS.Pipeline
Description: The product level is insufficient for component "Flat File Source" (1).
End Error
Error: 2005-12-23 17:01:57.67
Code: 0xC00470FE
Source: Data Flow Task DTS.Pipeline
Description: The product level is insufficient for component "Script Component" (9).
End Error

i have entered the command as follows.

dtexec /f "c:\somefolder\package1.dtsx"

Any points regarding this issue would be helpful.

Take a look here:

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

Same problem is being discussed.

HTH,
Ovidiu

|||

I am receiving a similar error. We are running SS2005 Enterprise Edition. We can connect to SSIS with SS Management Studio so that verifies that it's installed. Here is our error log. Did you solve your probelm, and if so, how?

*************************************Run GPS_EXTRACT_QA.dtsx*********************************************
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 5:14:34 PM
Progress: 2006-01-26 17:14:36.01
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2006-01-26 17:14:36.03
Source: Data Flow Task
Validating: 50% complete
End Progress
Progress: 2006-01-26 17:14:36.21
Source: Data Flow Task
Validating: 100% complete
End Progress
Progress: 2006-01-26 17:14:36.53
Source: Execute SQL Task
Executing query "truncate table sap_extract_staging
delete from sap".: 100% complete
End Progress
Progress: 2006-01-26 17:14:36.54
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2006-01-26 17:14:36.56
Source: Data Flow Task
Validating: 50% complete
End Progress
Progress: 2006-01-26 17:14:36.60
Source: Data Flow Task
Validating: 100% complete
End Progress
Error: 2006-01-26 17:14:36.60
Code: 0xC00470FE
Source: Data Flow Task DTS.Pipeline
Description: The product level is insufficient for component "Flat File Source" (749).
End Error
Warning: 2006-01-26 17:14:36.60
Code: 0x80019002
Source: GPS_EXTRACT_QA
Description: 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.
End Warning
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 5:14:34 PM
Finished: 5:14:36 PM
Elapsed: 2.562 seconds

|||

I am also getting same error.

any sugessions regarding this issue

errors while running ssis packages from the command prompt.

I have created ssis package. It has been successfully running at UI level.

But when i am trying to execute it from the command prompt by using dtexec utility it is showing the following error messages.

Error: 2005-12-23 17:01:57.67
Code: 0xC00470FE
Source: Data Flow Task DTS.Pipeline
Description: The product level is insufficient for component "Flat File Source" (1).
End Error
Error: 2005-12-23 17:01:57.67
Code: 0xC00470FE
Source: Data Flow Task DTS.Pipeline
Description: The product level is insufficient for component "Script Component" (9).
End Error

i have entered the command as follows.

dtexec /f "c:\somefolder\package1.dtsx"

Any points regarding this issue would be helpful.

Take a look here:

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

Same problem is being discussed.

HTH,
Ovidiu

|||

I am receiving a similar error. We are running SS2005 Enterprise Edition. We can connect to SSIS with SS Management Studio so that verifies that it's installed. Here is our error log. Did you solve your probelm, and if so, how?

*************************************Run GPS_EXTRACT_QA.dtsx*********************************************
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 5:14:34 PM
Progress: 2006-01-26 17:14:36.01
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2006-01-26 17:14:36.03
Source: Data Flow Task
Validating: 50% complete
End Progress
Progress: 2006-01-26 17:14:36.21
Source: Data Flow Task
Validating: 100% complete
End Progress
Progress: 2006-01-26 17:14:36.53
Source: Execute SQL Task
Executing query "truncate table sap_extract_staging
delete from sap".: 100% complete
End Progress
Progress: 2006-01-26 17:14:36.54
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2006-01-26 17:14:36.56
Source: Data Flow Task
Validating: 50% complete
End Progress
Progress: 2006-01-26 17:14:36.60
Source: Data Flow Task
Validating: 100% complete
End Progress
Error: 2006-01-26 17:14:36.60
Code: 0xC00470FE
Source: Data Flow Task DTS.Pipeline
Description: The product level is insufficient for component "Flat File Source" (749).
End Error
Warning: 2006-01-26 17:14:36.60
Code: 0x80019002
Source: GPS_EXTRACT_QA
Description: 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.
End Warning
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 5:14:34 PM
Finished: 5:14:36 PM
Elapsed: 2.562 seconds

|||

I am also getting same error.

any sugessions regarding this issue

Errors when attempting to use DataReader destination

Hi,
We have an ASP.Net app that consumes data from a SSIS package. I have proved that this works by building a noddy ASP.Net app against it on my local machine.

Unfortunately the same isn't true of our project-wide solution where we get error "Package failed to execute." In our package log I can see that the things that were failing were various property expressions that are in my package. The expressions are all on the SQLStatementSource property of various Exec SQL tasks in my package.

Here's an example of just such an error:
OnError,FR23011958,CT\aeqz,SQL UPDATE StreamHistory,{30BD52FA-8077-4D40-BFA7-248173C34000},{C84EF8C0-4B15-41FC-B205-9FB6B72884AC},26/10/2005 13:46:31,26/10/2005 13:46:31,-1073647612,0x,The result of the expression ""UPDATE dbo.StreamHistory
SET StreamEndDateID = (Year(GETDATE()) * 10000) + (Month(GETDATE()) * 100) + Day(GETDATE())
, StreamEndTimeID = (datepart(hh, GETDATE()) * 100) + datepart(mi, GETDATE())
, StreamEndTMS = GETDATE()
, StatusDetailID = 1
, Duration = DATEDIFF(ss, StreamStartTMS, GETDATE())
WHERE StreamHistoryID = " + TRIM((DT_STR, 4, 1252) @.[Metadata::StreamHistoryID])

" on property "SqlStatementSource" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
Hopefully you can see that (in theory) this expression should get evaluated regardless of where the package is executed from. Remember, it is not the SQL query that fails to execute - it is the construction of the SQL statement inside an expression that fails.

So simply, has anyone experienced something similar? i.e. Expressions fail to get evaluated when a package is executed from an ASP.Net app (or elsewhere).

I'm wondering if this is a permissions thing...its damned annoying anyway.

ANY experiences at all of doing something similar would be useful.

Thanks
-Jamie

Jamie,

Would you like to share with me your package and ASP app?

BTW - I don't see how this relates to Data Reader destination. Do you mean to put another subject line instead?|||

Runying Mao wrote:

Jamie,

Would you like to share with me your package and ASP app?

BTW - I don't see how this relates to Data Reader destination. Do you mean to put another subject line instead?

Hi Runying,
Well it consumes data via a datareader. I actually wrote that initial post over the space of about an hour. At the beginning of that hour we figured the problem was in the datareader cos of the problems we've had with it lately (which you know all about ), by the end of the hour we'd learnt more about what it was - i.e. problems with expressions. I just forgot to change the thread title that's all.

We've found out more since. We've built a new windows forms app and copied the code from "Running an Existing Package using Visual Basic" in BOL into the app and used it to run our package. It worked fine.

We then copied and pasted that code line for line into a new project in our solution and it didn't work. Same code, same machine, same user credentials...different result. very very strange. So now we're pursuing the assumption that some sort of solution-wide configuration is causing a problem. Hopefully we'll find out more tomorrow but if not....I'l be on here pleading for help!!

-Jamie|||Hi, I'm working with Jamie on this problem and have found a little more about the problem, but am still no nearer a solution!

If I add a new Web site to our solution and copy in the code to call the SSIS package, everything runs correctly.

However, if I then reference one of the other assemblies in our solution the package fails to execute, even though we're not actually calling anything in the referenced assembly.|||Here's what Tim and I eventually found out.

http://blogs.conchango.com/jamiethomson/archive/2005/10/27/2315.aspx

Weird!!

-Jamie

Errors using multiple parameters in a SQL Statement

In an OLE DB Source in an SSIS package, we are having difficulties using multiple parameters in a SQL statement.

Using a single '?' works fine, but I've read that when you want to map more than 1 parameter you should use 'Parameter0, Parameter1, etc'.

The problem is that when we use Parameter0 and Parameter1 and then try to map it, it says that the query contains no parameters.

Can anyone help with the correct way to use multiple parameters in a SQL query that's part of an OLE DB Source task?

Thanks,

Mike

You still use multiple question marks (?) as parameter markers in the query.

For OLE DB, your parameter names should be "0", "1", "2", etc... Not "Parameter0", "Parameter1", etc...|||

I used to have a lot of problems with that when the query was to big or complex (Sub queries, etc). To solve it, and I use it as a standard, I place the query inside of a variable and then use an expression to make it parameterized.

Here is how:

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

|||

Phil Brammer wrote:

You still use multiple question marks (?) as parameter markers in the query.

For OLE DB, your parameter names should be "0", "1", "2", etc... Not "Parameter0", "Parameter1", etc...

I disagree with that. Paramter0, Parameter1..etc is actually the default parameter name used by the OLE DB Source component; so yes you can use it.

|||

Rafael Salas wrote:

Phil Brammer wrote:

You still use multiple question marks (?) as parameter markers in the query.

For OLE DB, your parameter names should be "0", "1", "2", etc... Not "Parameter0", "Parameter1", etc...

I disagree with that. Paramter0, Parameter1..etc is actually the default parameter name used by the OLE DB Source component; so yes you can use it.

Kirk Haselden comments otherwise in his SSIS book... (Page 182) Granted, that's for Execute SQL Tasks.

Now I guess I'll have to go test it myself as that's what I've always used.|||Either will work. "Parameter0" or just "0".

The issue is likely as Rafael pointed out if you are using a complex query.|||

Neither Parameter0 or just 0 is working

This is the SQL in an OLE DB Source.

At one point, ?0 and ?1 caused the parameter mapping dialog to appear, but these others are not working.

Still scratching my head...

Mike

|||

Mike Batton wrote:

Neither Parameter0 or just 0 is working

This is the SQL in an OLE DB Source.

At one point, ?0 and ?1 caused the parameter mapping dialog to appear, but these others are not working.

Still scratching my head...

Mike

Can you post your query?|||

With the following options, here's what happens when I click on the parameters button:

Select * from TEW_D_DT where dt_key = Parameter0 and cal_yr_id = Parameter1

Message says that the query contains no parameters

Select * from TEW_D_DT where dt_key = ?0 and cal_yr_id = ?1

This one causes the parameters mapping dialog to open and show two parameters. The problem here is that I set them and then say "OK" to the OLE DB Source Editor and I get this:

Must delcare the scalar variable "@.P10"

Select * from TEW_D_DT where dt_key = ?

This works, but it's only one parameter.

|||

Mike Batton wrote:

With the following options, here's what happens when I click on the parameters button:

Select * from TEW_D_DT where dt_key = Parameter0 and cal_yr_id = Parameter1

Message says that the query contains no parameters

Select * from TEW_D_DT where dt_key = ?0 and cal_yr_id = ?1

This one causes the parameters mapping dialog to open and show two parameters. The problem here is that I set them and then say "OK" to the OLE DB Source Editor and I get this:

Must delcare the scalar variable "@.P10"

Select * from TEW_D_DT where dt_key = ?

This works, but it's only one parameter.

Use question marks - always, and don't number them.

select * from tew_d_dt where dt_key = ? and cal_yr_id = ?

Then, in the parameter mappings, you can use "Parameter0" or "0" for the parameter name to map to your variable.|||

Cool, that worked.

Shouldn't I also be able to use the ? in the select part of the statement?

Mike

|||

Mike Batton wrote:

Cool, that worked.

Shouldn't I also be able to use the ? in the select part of the statement?

Mike

You mean to dynamically select columns? That would not be a good idea as it will change metadata.|||

What about in the case part of a select statement... should that work or not?

Below is part of the actual query that we're trying to get to work.

Mike

select 'ent' AS level_desc,

sku.new_concept_cd AS code_1,

sku.color AS color_name,

case when sls.date_key = ?

then

sum(isnull(sls.total_quantity,0) -

(isnull(sls.store_return_total_quantity,0) +

isnull(sls.catalog_return_total_quantity,0) +

isnull(sls.concierge_return_total_quantity,0))) else 0 end as sales_lw,

sum(isnull(sls.total_quantity,0) -

(isnull(sls.store_return_total_quantity,0) +

isnull(sls.catalog_return_total_quantity,0) +

isnull(sls.concierge_return_total_quantity,0))) as sales_life,

from edw.jjwhse.tew_l_stock_status_sku sku

left outer join

edw_jj.sales.vew_r_sls_sku_loc_wk sls

on sls.sku_key = sku.sku_key

where sls.date_key <= ?

and sku.sku_type_desc = 'sales'

and sls.loc_key not in (6,9,10,11,12,13,14,106)

and sku.new_concept_cd is not null

group by sku.new_concept_cd, sku.color, sls.date_key

|||

Mike Batton wrote:

What about in the case part of a select statement... should that work or not?

Try it... I don't know. If it doesn't work, then as Rafael stated, you can build your query first as an expression in a variable. Build it there first, then use that variable in the OLE DB connector.|||

Anything that changes the structure of the dataflow pipeline will not work; this is in the select part of the query: adding/removing columns; changing data types; changing column names, etc.

Perhaps if you provide an example of what you need to accomplish; somebody around here could help you.

errors in ssis package execution

i have sql server 2005 standard edition(9.0.3054) with sp2, My ssis packages
were initially working fine,After installing sql server 2008 ctp, all ssis
packages stopped executing and giving error "Could not create DTS.Application
because of error 0x80040154", also import export wizard in sql 2005 is not
working.
After that i uninstalled sql server 2008 ctp and integration
service of sql 2005, and again installed integrtaion service of sql 2005 but
still i am getting error in ssis package execution.Please suggest me a
solution asap , as it is on a production server.
Thanks
Rupesh Mondal.
I had already removed and reinstalled "ssis" but it didnt worked ok,and i
already had sp2 installed on my database server.
Thanks and Regards,
Rupesh Mondal.
"John Bell" wrote:

> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
> news:CF99A5D6-B6C6-4484-9BD2-B6237A1A3D05@.microsoft.com...
> Hi Rupesh
> Error Code 0x80040154 relates to class not registered, therefore it looks
> like a SSIS component has been corrupted. The easiest solution may be to
> remove and re-install SSIS or SQL Server. If you are not on SP2 then you may
> just get away with installing that.
> John
>
>

errors in ssis package execution

i have sql server 2005 standard edition(9.0.3054) with sp2, My ssis packages
were initially working fine,After installing sql server 2008 ctp, all ssis
packages stopped executing and giving error "Could not create DTS.Application
because of error 0x80040154", also import export wizard in sql 2005 is not
working.
After that i uninstalled sql server 2008 ctp and integration
service of sql 2005, and again installed integrtaion service of sql 2005 but
still i am getting error in ssis package execution.Please suggest me a
solution asap , as it is on a production server.
Thanks
Rupesh Mondal."Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
news:CF99A5D6-B6C6-4484-9BD2-B6237A1A3D05@.microsoft.com...
>i have sql server 2005 standard edition(9.0.3054) with sp2, My ssis
>packages
> were initially working fine,After installing sql server 2008 ctp, all ssis
> packages stopped executing and giving error "Could not create
> DTS.Application
> because of error 0x80040154", also import export wizard in sql 2005 is not
> working.
> After that i uninstalled sql server 2008 ctp and integration
> service of sql 2005, and again installed integrtaion service of sql 2005
> but
> still i am getting error in ssis package execution.Please suggest me a
> solution asap , as it is on a production server.
> Thanks
> Rupesh Mondal.
Hi Rupesh
Error Code 0x80040154 relates to class not registered, therefore it looks
like a SSIS component has been corrupted. The easiest solution may be to
remove and re-install SSIS or SQL Server. If you are not on SP2 then you may
just get away with installing that.
John|||I had already removed and reinstalled "ssis" but it didnt worked ok,and i
already had sp2 installed on my database server.
Thanks and Regards,
Rupesh Mondal.
"John Bell" wrote:
> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
> news:CF99A5D6-B6C6-4484-9BD2-B6237A1A3D05@.microsoft.com...
> >i have sql server 2005 standard edition(9.0.3054) with sp2, My ssis
> >packages
> > were initially working fine,After installing sql server 2008 ctp, all ssis
> > packages stopped executing and giving error "Could not create
> > DTS.Application
> > because of error 0x80040154", also import export wizard in sql 2005 is not
> > working.
> > After that i uninstalled sql server 2008 ctp and integration
> > service of sql 2005, and again installed integrtaion service of sql 2005
> > but
> > still i am getting error in ssis package execution.Please suggest me a
> > solution asap , as it is on a production server.
> >
> > Thanks
> > Rupesh Mondal.
> Hi Rupesh
> Error Code 0x80040154 relates to class not registered, therefore it looks
> like a SSIS component has been corrupted. The easiest solution may be to
> remove and re-install SSIS or SQL Server. If you are not on SP2 then you may
> just get away with installing that.
> John
>
>|||Hi
Have you re-installed .NET? Otherwise I would re-install from scratch.
Hopefully this is not a production server!
John
"Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
news:81A467AD-B56B-44FC-92C9-D4FA96019C87@.microsoft.com...
>I had already removed and reinstalled "ssis" but it didnt worked ok,and i
> already had sp2 installed on my database server.
> Thanks and Regards,
> Rupesh Mondal.
> "John Bell" wrote:
>> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
>> news:CF99A5D6-B6C6-4484-9BD2-B6237A1A3D05@.microsoft.com...
>> >i have sql server 2005 standard edition(9.0.3054) with sp2, My ssis
>> >packages
>> > were initially working fine,After installing sql server 2008 ctp, all
>> > ssis
>> > packages stopped executing and giving error "Could not create
>> > DTS.Application
>> > because of error 0x80040154", also import export wizard in sql 2005 is
>> > not
>> > working.
>> > After that i uninstalled sql server 2008 ctp and
>> > integration
>> > service of sql 2005, and again installed integrtaion service of sql
>> > 2005
>> > but
>> > still i am getting error in ssis package execution.Please suggest me a
>> > solution asap , as it is on a production server.
>> >
>> > Thanks
>> > Rupesh Mondal.
>> Hi Rupesh
>> Error Code 0x80040154 relates to class not registered, therefore it looks
>> like a SSIS component has been corrupted. The easiest solution may be to
>> remove and re-install SSIS or SQL Server. If you are not on SP2 then you
>> may
>> just get away with installing that.
>> John
>>

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.

errors from a child dts package

I am executing a child dts package from an activex script. While I get no errors when executing the child package independently, I get errors when executing it thru an activex script. Can anyone help?

Also, can anyone give me a sample code to display error messages from each step in a log file ?
Thanks

Here is the activex script I use:

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************
Function Main()

Main = DTSTaskExecResult_Success
Dim SQLConn
Dim DB2Conn
SQLStr = "DRIVER=SQL Server;SERVER=GPDSQLIRV01;DATABASE=DailySales"
DB2Str = "DSN=SYSHDB2P"

Set SQLConn = CreateObject("ADODB.Connection")
Set DB2Conn = CreateObject("ADODB.Connection")

SQLConn.Open SQLStr, "script", "script"
DB2Conn.Open DB2Str, "hgrprd1", "ddcsgr1"

db2sql = "SELECT MAX(INSERT_TIME) AS INSERT_TIME FROM H.SA48 WHERE REC_TYPE = 'D' "
set db2sqltxt48 = db2conn.execute(db2sql)

SSsql = "SELECT INSERT_TIME FROM SA48_date_tbl"
set SSsqltxt48 = sqlconn.execute(SSsql)

db2sql49 = "SELECT AS_OF_DT FROM H.SA49 "
set db2sqltxt49 = db2conn.execute(db2sql49)

SSsql49 = "SELECT AS_OF_DT FROM SA49_date_tbl"
set SSsqltxt49 = sqlconn.execute(SSsql49)



If db2sqltxt48("insert_time") <> SSsqltxt48("insert_time") And db2sqltxt49("as_of_dt") <> SSsqltxt49("as_of_dt") then

Dim oPkg, oStep
Dim sServer, sUID, sPWD, iSecurity , sPkgPWD, sPkgName, sErrMsg
Set oPkg = CreateObject("DTS.Package")

' Assign parameters
sServer = "GPDSQLIRV01"
sUID = "script"
sPWD = "script"
iSecurity = DTSSQLStgFlag_UseTrustedConnection
sPkgPWD = ""
sPkgName = "DTS_Load_ProductTables"

' Load Child Package
oPkg.LoadFromSQLServer sServer, sUID, sPWD, iSecurity , sPkgPWD, "", "", sPkgName


oPkg.Execute

For Each oStep In oPkg.Steps
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
Main = DTSTaskExecResult_Failure
MsgBox "hello"
End If
Next

oPkg.Uninitialize
Set oStep = Nothing
Set oPkg = Nothing


End If

db2sqltxt48.close
SSsqltxt48.close
db2sqltxt49.close
SSsqltxt49.close

db2conn.close
sqlconn.close

Set db2sqltxt48 = Nothing
Set SSsqltxt48 = Nothing
Set db2sqltxt49 = Nothing
Set SSsqltxt49 = Nothing

Set db2conn = Nothing
Set sqlconn = Nothing


End Function......and the reason most of this isn't a stored procedure is?

What's the p[ackage suppose to do?|||Originally posted by Brett Kaiser
......and the reason most of this isn't a stored procedure is?

What's the p[ackage suppose to do?

Brett

The child package has several steps. Each step is downloading data from a DB2 table on the mainframe to a corresponding table on the SQLServer

Thanks|||Are you doing a lot of manipulation?

Is the whole process built already?

I usually transfer everything to a staging enviroment and then manipulate the data after it migrated.

Just seems easier..

As fa as logging errors, can you echo them out to a text file...do inserts in to a table might not work in the evnt you have to roll back..|||Originally posted by Brett Kaiser
Are you doing a lot of manipulation?

Is the whole process built already?

I usually transfer everything to a staging enviroment and then manipulate the data after it migrated.

Just seems easier..

As fa as logging errors, can you echo them out to a text file...do inserts in to a table might not work in the evnt you have to roll back..

Brett
No, no processing at all. Just select from the table on the mainframe and insert into the one on the SQLSERVER.
Regarding error messages coming from each step of the child package, can you give some sample code to capture the error code and message, and echo it to a text file in the parent package?

Thanks
Anil

Errors during DTS package

We have been getting an error on a DTS package
that is trying to load about 800,000 records.
It used to work without problems.
Now.. It will fail anywhere between record 8000
and record 160,000.
It is not one set record. Here is the error message.
[DBNETLIB][CONNECTIONWRITE (SEND()).]
GENERAL NETWORK ERROR
It will get this error even when the server itself
runs the package, and the file is on the server's
disk drive.
Does anyone have any ideas?Looks to me like the target SQL Server has re-set the sql connection due to
severe error of some sort - either sql engine error or windows error
perhaps. Have you checked the server logs on the target server - both SQL &
Windows logs to see if there's any further information available?
This could be such a wide variety of things - even disk failure or table /
index corruption within SQL Server. You might try running a dbcc checkdb on
the target server as well as part of your investigations.
Regards,
Greg Linwood
SQL Server MVP
"Scot" <sstyer@.alltel.net> wrote in message
news:46df01c42bce$62d54d60$a401280a@.phx.gbl...
> We have been getting an error on a DTS package
> that is trying to load about 800,000 records.
> It used to work without problems.
> Now.. It will fail anywhere between record 8000
> and record 160,000.
> It is not one set record. Here is the error message.
> [DBNETLIB][CONNECTIONWRITE (SEND()).]
> GENERAL NETWORK ERROR
> It will get this error even when the server itself
> runs the package, and the file is on the server's
> disk drive.
> Does anyone have any ideas?
>|||We aren't seeing much in the event log. Nothing to
indicate a major problem. The server log is just
showing the process being killed. Is there any way We
can get a more meaningful error.

>--Original Message--
>Looks to me like the target SQL Server has re-set the sql
connection due to
>severe error of some sort - either sql engine error or
windows error
>perhaps. Have you checked the server logs on the target
server - both SQL &
>Windows logs to see if there's any further information
available?
>This could be such a wide variety of things - even disk
failure or table /
>index corruption within SQL Server. You might try running
a dbcc checkdb on
>the target server as well as part of your investigations.
>Regards,
>Greg Linwood
>SQL Server MVP
>"Scot" <sstyer@.alltel.net> wrote in message
>news:46df01c42bce$62d54d60$a401280a@.phx.gbl...
>
>.
>|||Have you got the package logging configured under Properties / Logging /
Errorfile?
Regards,
Greg Linwood
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:4d2d01c42c66$7df1ee30$a401280a@.phx.gbl...[vbcol=seagreen]
> We aren't seeing much in the event log. Nothing to
> indicate a major problem. The server log is just
> showing the process being killed. Is there any way We
> can get a more meaningful error.
>
>
> connection due to
> windows error
> server - both SQL &
> available?
> failure or table /
> a dbcc checkdb on

Errors during DTS package

We have been getting an error on a DTS package
that is trying to load about 800,000 records.
It used to work without problems.
Now.. It will fail anywhere between record 8000
and record 160,000.
It is not one set record. Here is the error message.
[DBNETLIB][CONNECTIONWRITE (SEND()).]
GENERAL NETWORK ERROR
It will get this error even when the server itself
runs the package, and the file is on the server's
disk drive.
Does anyone have any ideas?
Looks to me like the target SQL Server has re-set the sql connection due to
severe error of some sort - either sql engine error or windows error
perhaps. Have you checked the server logs on the target server - both SQL &
Windows logs to see if there's any further information available?
This could be such a wide variety of things - even disk failure or table /
index corruption within SQL Server. You might try running a dbcc checkdb on
the target server as well as part of your investigations.
Regards,
Greg Linwood
SQL Server MVP
"Scot" <sstyer@.alltel.net> wrote in message
news:46df01c42bce$62d54d60$a401280a@.phx.gbl...
> We have been getting an error on a DTS package
> that is trying to load about 800,000 records.
> It used to work without problems.
> Now.. It will fail anywhere between record 8000
> and record 160,000.
> It is not one set record. Here is the error message.
> [DBNETLIB][CONNECTIONWRITE (SEND()).]
> GENERAL NETWORK ERROR
> It will get this error even when the server itself
> runs the package, and the file is on the server's
> disk drive.
> Does anyone have any ideas?
>
|||We aren't seeing much in the event log. Nothing to
indicate a major problem. The server log is just
showing the process being killed. Is there any way We
can get a more meaningful error.

>--Original Message--
>Looks to me like the target SQL Server has re-set the sql
connection due to
>severe error of some sort - either sql engine error or
windows error
>perhaps. Have you checked the server logs on the target
server - both SQL &
>Windows logs to see if there's any further information
available?
>This could be such a wide variety of things - even disk
failure or table /
>index corruption within SQL Server. You might try running
a dbcc checkdb on
>the target server as well as part of your investigations.
>Regards,
>Greg Linwood
>SQL Server MVP
>"Scot" <sstyer@.alltel.net> wrote in message
>news:46df01c42bce$62d54d60$a401280a@.phx.gbl...
>
>.
>
|||Have you got the package logging configured under Properties / Logging /
Errorfile?
Regards,
Greg Linwood
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:4d2d01c42c66$7df1ee30$a401280a@.phx.gbl...[vbcol=seagreen]
> We aren't seeing much in the event log. Nothing to
> indicate a major problem. The server log is just
> showing the process being killed. Is there any way We
> can get a more meaningful error.
>
> connection due to
> windows error
> server - both SQL &
> available?
> failure or table /
> a dbcc checkdb on

Errors during DTS package

We have been getting an error on a DTS package
that is trying to load about 800,000 records.
It used to work without problems.
Now.. It will fail anywhere between record 8000
and record 160,000.
It is not one set record. Here is the error message.
[DBNETLIB][CONNECTIONWRITE (SEND()).]
GENERAL NETWORK ERROR
It will get this error even when the server itself
runs the package, and the file is on the server's
disk drive.
Does anyone have any ideas?Looks to me like the target SQL Server has re-set the sql connection due to
severe error of some sort - either sql engine error or windows error
perhaps. Have you checked the server logs on the target server - both SQL &
Windows logs to see if there's any further information available?
This could be such a wide variety of things - even disk failure or table /
index corruption within SQL Server. You might try running a dbcc checkdb on
the target server as well as part of your investigations.
Regards,
Greg Linwood
SQL Server MVP
"Scot" <sstyer@.alltel.net> wrote in message
news:46df01c42bce$62d54d60$a401280a@.phx.gbl...
> We have been getting an error on a DTS package
> that is trying to load about 800,000 records.
> It used to work without problems.
> Now.. It will fail anywhere between record 8000
> and record 160,000.
> It is not one set record. Here is the error message.
> [DBNETLIB][CONNECTIONWRITE (SEND()).]
> GENERAL NETWORK ERROR
> It will get this error even when the server itself
> runs the package, and the file is on the server's
> disk drive.
> Does anyone have any ideas?
>|||We aren't seeing much in the event log. Nothing to
indicate a major problem. The server log is just
showing the process being killed. Is there any way We
can get a more meaningful error.
>--Original Message--
>Looks to me like the target SQL Server has re-set the sql
connection due to
>severe error of some sort - either sql engine error or
windows error
>perhaps. Have you checked the server logs on the target
server - both SQL &
>Windows logs to see if there's any further information
available?
>This could be such a wide variety of things - even disk
failure or table /
>index corruption within SQL Server. You might try running
a dbcc checkdb on
>the target server as well as part of your investigations.
>Regards,
>Greg Linwood
>SQL Server MVP
>"Scot" <sstyer@.alltel.net> wrote in message
>news:46df01c42bce$62d54d60$a401280a@.phx.gbl...
>> We have been getting an error on a DTS package
>> that is trying to load about 800,000 records.
>> It used to work without problems.
>> Now.. It will fail anywhere between record 8000
>> and record 160,000.
>> It is not one set record. Here is the error message.
>> [DBNETLIB][CONNECTIONWRITE (SEND()).]
>> GENERAL NETWORK ERROR
>> It will get this error even when the server itself
>> runs the package, and the file is on the server's
>> disk drive.
>> Does anyone have any ideas?
>
>.
>|||Have you got the package logging configured under Properties / Logging /
Errorfile?
Regards,
Greg Linwood
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:4d2d01c42c66$7df1ee30$a401280a@.phx.gbl...
> We aren't seeing much in the event log. Nothing to
> indicate a major problem. The server log is just
> showing the process being killed. Is there any way We
> can get a more meaningful error.
>
> >--Original Message--
> >Looks to me like the target SQL Server has re-set the sql
> connection due to
> >severe error of some sort - either sql engine error or
> windows error
> >perhaps. Have you checked the server logs on the target
> server - both SQL &
> >Windows logs to see if there's any further information
> available?
> >
> >This could be such a wide variety of things - even disk
> failure or table /
> >index corruption within SQL Server. You might try running
> a dbcc checkdb on
> >the target server as well as part of your investigations.
> >
> >Regards,
> >Greg Linwood
> >SQL Server MVP
> >
> >"Scot" <sstyer@.alltel.net> wrote in message
> >news:46df01c42bce$62d54d60$a401280a@.phx.gbl...
> >> We have been getting an error on a DTS package
> >> that is trying to load about 800,000 records.
> >> It used to work without problems.
> >> Now.. It will fail anywhere between record 8000
> >> and record 160,000.
> >> It is not one set record. Here is the error message.
> >>
> >> [DBNETLIB][CONNECTIONWRITE (SEND()).]
> >> GENERAL NETWORK ERROR
> >>
> >> It will get this error even when the server itself
> >> runs the package, and the file is on the server's
> >> disk drive.
> >>
> >> Does anyone have any ideas?
> >>
> >
> >
> >.
> >

Errors Deploying an SSIS package

Hi All,

I hope someone can help becuase this problem is issue us several headaches.

We are currently trying to deploy an SSIS package to a production server. The deployment goes fine, the package runs ok when executed manually. The issues start when we try and execute it under the SQL agent.

Having gone back to the drawing board and spent much of the day reading various articles and applying the various options (especially those within the MS KB article 918760), we are still no closer to a resolution.

The SSIS package was created under an Administrator, and the SQL agent runs under a different Domain Admin account.

When we set up the Schedule to read from SQL Server or the SSIS Store the standard "Executed as user: DOMAIN\USERNAME. The package execution failed. The step failed" in the history.

We tried to create the package as a file access and now get "Package could not be found" even though you can browse to i in the schedule list. The Domain account as full access to the folder where the package resides.

Has anyone else come across this issue, or have a workable solution?

Many TIA.

But does the SQL Server service account have access to everything? Not the Agent user, but the SQL Server service account?|||

Phil,

Thanks for the assist, adding access rights the SQL Server Account ot the database helped, and it seems to have fixed one issue in that it now adds the /Decrypt item to the when saved as EncryptWithPassword, and stores the information.

We have decided to run the package from the SQL server rather than a file, However we still get the same permission error when running the job in SQL agent.

Any more ideas?

|||

This KB article maqy help

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

|||

Thanks for the Pointer Rafael, but we have already tried to apply the workrounds mentioned in the articles with no joy.

I'm coming to the conclusion that MS have really over done the security model for SSIS, I still pondering what the point of of the EncryptWithUserKey setting is as most jobs will generally need to be scheduled and run under the SQLAgent account.

errors but finished with success

My ssis package errors out because one of the database connection failed. I successfully logged error but also indicated that package finished successfully. My confusion is if a sheduling software schedules this package, what would be return code sent by dtexe... . would it be success or failure? In this scnerio i want it to return failure so that appropriate team can be contacted.

thanks,

kushpaw

Hi Kushpaw,

There is a property on the tasks in the control flow entitled FailPackageOnFailure. If you set this to true on the tasks that fail then the entire package will fail. The returned value should reflect this.

Hope this helps,

Grant