Create a table and then using it as OLE DB Destination component

I want to do the following in a package:

Create a table at the beginning of a package (using a ExecuteSQLTask component) and then use the created table as a OLE DB destination component, later on the package.

Is this possible in SSIS?

The problem I run into is that I have to point the OLE DB destination component to a table and set up mappings, however as the table does not exist until the package is running, it does not seem to be possible.

I've looked at:

http://blogs.conchango.com/jamiethomson/archive/2006/11/19/SSIS_3A00_-Using-temporary-tables.aspx

Which is slightly similar to what I want, but the table I create would not be a temp tables, and I need to set up mappings and I don't see how this is possible.

Thanks

To design and build the package you will need to create the table. Once that has been completed, the table can be dropped, and you can rely on the table created in the Exec SQL Task for the actual running of the package. You will however need to change the DelayValidation property to be True, on the Data Flow Task. This will prevent the task (and components it contains) from being validated, and failing due to the missing table. The Data Flow Task will still be validated, immediately prior to execution, but by that stage the Exec SQL Task will have completed, so the table will then exist.|||

Ok, thanks for the answer but thats whats was mentioned on the cochango link. The difference here is that I do not wish to drop the table after it has been created, I want to create it, use it and keep it...

I'm currently creating packages in C#, however the problem occurs when I make the call:

// Establish a connection

componentInstance.AcquireConnections(null);

// Initialize the metadata

componentInstance.ReinitializeMetaData();

The component is question here is a OLE DB Destination component, it throws an exception because the table does not exist in the database whilst the package is being created.

Now, I know I can set DelayValidation on the Task which contains the destination component

It would be nice if I could:

1. set DelayValidation = true on the Task which contains this component

2. Skip the componentInstance.ReinitializeMetaData() method

3. Set up mappings between the input collection and external meta columns (each of which I would have to create)

However, if I skip the method componentInstance.ReinitializeMetaData I receive there is no inputCollection available to map to, so when you call:

// Get the destination's default input and virtual input.

IDTSInput90 input = component.InputCollection[0];

IDTSVirtualInput90 vInput = input.GetVirtualInput();

there is are no columns available....

Therefore, my question is, can this be done in a C# environment, or does the same rule as creating this in the Designer apply, whereby I would have to have the table available in the database on the creation of the package in order to create the package?

Create a SP that will launch a DTS package

Hi all. How can I create a Stored Procedure that will launch a DTS package that I have already built dynamically ?

I have a string already built and I need to put it in a SP because my application is php (linux) and the database is SQL Server on a Windows machine.

I will use php to execute the stored proc, which is the only way to access it.

My string looks like this :
C:\Progra~1\Micros~3\80\Tools\Binn\ISQL.EXE -S [MyServer] -U [Username] -P [Password] -Q "ISQL_Batch 'D:\DDFIImporte\IMPICAFI.bat [user] [Schema] [Pwd] '" -n -d [database]

(Words in [] are only to show that I will put other values)

Thanks

CFGillesxp_cmdshell but it will have to execute with sysadmin permissions.|||Hi,

Can you be more specific ?? I'm a near-newby in SP building... Maybe some pseudo-code, some links or examples ?

Thanks

CFGillessql

Create a Package for export the query results to Excel

Hi,

In SQL Server,

does anybody provide the steps for create a Package for exporting the query results to Excel environment?

I just know that click the "Export", then choose the query file to export to Excel.

I want to know how to create the Package to export it.

Please let me know, thanks.1. Create new package

2. Connection from Connection menu (or right click)
* Add SQL server connection, specify the server name, authentication, and dbname
* Add Excel connection, specify the path where u want to place the excel file

3. Transform data task from Task menu (or right click)
* Add Transform data task, select the source and destination
* Double click the task
- Source tab : select the table/view or write down the query
- Destination tab : just click ok on create table script window (for sheet & column name)
- Transformation tab : Map the required columns

4. Save the DTS

5. Right click - Execute package or u can schedule it as job|||Question:

I created the package and execute the package, it works,
but, I got another problem, if I execute it for 2 times, the values will be output double in the same table,

eg.
1st execute:
Name:
Jim
Mary

2nd execute:
Name:
Jim
Mary
Jim
Mary

I want to execute the package which values will replace the previous file's values if the filename is same.

How should I do? Please let me know, thanks.|||em....i dunno how to delete data in excel destination file. but u can do it if the destination is ms access file, with additional task and workflow :
- Add a task (Execute SQL Task) as 1st step in the DTS
Existing connection : choose the ms access connection which was already created
SQL statement : DELETE * FROM <destination_tablename>

- Add on success workflow from above task to sql connection

Create a new maintenance plan

I have installed SQLServer 2005 from Small Business Server premium edition. The package has been upgraded to SP2 and all the fixes have been applied.

I want to create a new maintenance plan to automate my DB backup procedures using SQL Server Management Studio. I start Management Studio and right click on the management folder and I I see is "Refresh" there is no "Create New Maintenance" plan presented.

I have tried this using the sa built-in account and the server administrator account both of which have sysadmin priviledges. I have also tried it from a client machine with another sysadmin userid but they all give the same results.

I checked to see if SSIS was installed. The add/remove programs wizard for SQL Server shows Integration Services SP2 is installed but I went ahead anyway and re-installed without error.

It seems that either SSIS is not running or I my user roles are preventing the maintenance wizard from running. Is there some way that I can tell if, in fact, SSIS is properly installed and running?

Thanks.

To create or manage maintenance plans, you must be a member of the sysadmin fixed server role. Note that Object Explorer only displays maintenance plans if the user is a member of the sysadmin fixed server role.

See http://technet.microsoft.com/en-us/library/ms189036(SQL.90).aspxsql

Create a new maintenance plan

I have installed SQLServer 2005 from Small Business Server premium edition. The package has been upgraded to SP2 and all the fixes have been applied.

I want to create a new maintenance plan to automate my DB backup procedures using SQL Server Management Studio. I start Management Studio and right click on the management folder and I I see is "Refresh" there is no "Create New Maintenance" plan presented.

I have tried this using the sa built-in account and the server administrator account both of which have sysadmin priviledges. I have also tried it from a client machine with another sysadmin userid but they all give the same results.

I checked to see if SSIS was installed. The add/remove programs wizard for SQL Server shows Integration Services SP2 is installed but I went ahead anyway and re-installed without error.

It seems that either SSIS is not running or I my user roles are preventing the maintenance wizard from running. Is there some way that I can tell if, in fact, SSIS is properly installed and running?

Thanks.

To create or manage maintenance plans, you must be a member of the sysadmin fixed server role. Note that Object Explorer only displays maintenance plans if the user is a member of the sysadmin fixed server role.

See http://technet.microsoft.com/en-us/library/ms189036(SQL.90).aspx

Create a DTS package to retrieve records from db & schedule to send the email at

hi,
i would like to create a DTS package to retrieve records from database , this records i retrieve is from the error log table ( ERROR_LOG_TB),the scheduler will run at 9 am daily and will retrieve the records if there is a error and the error information will be capsulate and sent through email.
Can i know how to know how to graphically do in DTS ? i am running SQL Server 2000.The thing that you are looking for can be done thru a "execute sql task" and using xp_sendmail. do you have any prior experience with DTS?
-rohit|||i got some prior experience in DTS but jus not sure how to do this in graphically . programming wise i know how to do it , i just want to get some knowledge how to do it in DTS|||Get in touch with http://www.sqldts.com website which has got plenty of DTS resources and code examples.

Also refer to books online for information about DTS as a first hand help.sql

Create a delay between FTP tasks, or?

Hi,

I have a package that will be using an FTP task to send a file (file1) to an ftp site.

Once the file (file1) is received at the ftp site, a corresponding file (file2) will be placed in the ftp directory, and then I will need to ftp the corresponding file (file2) back.

However, there will be a time delay between the time that file1 is received and file2 is put in the ftp directory. Maybe 5 to 30 minutes.

What is the best way to handle this scenario? I want to ftp file2 back as soon as it's available. However, I don't know when that is, and so I was wondering if there is a way to dynamically check for the availability of the file, so that I don't have to "hard code" a wait time into my process.

Thanks!

Setup a for loop to evaluate a variable such that as long as the variable is set to 0 the loop will happen. Then, using a script task, you can check to see if the file exists, and if not wait 5 seconds, or something like that. If it does, then set the for loop control variable to 1, which will allow it to exit and then execute another FTP task.|||Yes, that sounds reasonable. Do you have a sample of how one would write a script task to check for the availability of a file on a remote ftp site? I guess I could just use basic ftp commands, yes?|||This might help get your started. Does some slightly different stuff, but connectivity wise, it may get you going in the right direction.

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

Hi,

Another suggestion, you can use the file system task editor with set attributes, if it is successful your file is back. You can loop through this, maybe use a thread.sleep in a script component.

Larry

|||

LarryETL wrote:

Hi,

Another suggestion, you can use the file system task editor with set attributes, if it is successful your file is back. You can loop through this, maybe use a thread.sleep in a script component.

Larry

He needs to check a remote FTP site though.

Create 1 connection to execute multiple Packages

I want to create 1 connection because I have to execute multiple packages (2005). But when I go into the execute package task editor, the dropdown only ever has "New Connection". Is this a bug?Connections are package local. One package cannot see another package's connection managers.
We, unfortunately, don't have the notion of a file data source which it sounds like you're looking for. Basically, a way to create a connection that you can share across multiple packages.
You'll need to create a new connection per package.
K|||Kirk, not sure if I explained correctly. I'm trying to have 1 master package that executes child packages all on the same "ETL server". So I want 1 connection to be the "ETL Server" such that it can be changed in 1 place (actually, I would just have a runtime setting change it based on a config) when moving from DEV (each developer would have their own instance) to QA to PROD.

The reason I want many packages is so that multiple ETL developers can work on a component and build it without having to wait. Additionally, if everything was in 1 package, it would be huge.

Is the only way to accomplish this without having to change each package's sql server location is to reference it by its file location?|||OK, a few questions to clarify.

What kind of connection do you want the one connection to be SQL, file?
You have one package that you want to call multiple subpackages?
You want the subpackages to use the one connection?
I'm not sure what you mean by "1 connection to be the 'ETL Server' "

"Is the only way to accomplish this without having to change each package's sql server location is to reference it by its file location?"
I don't understand why you need to change the package's sql server location. Which package's SQL Server location? The parent package or the sub-packages?

I think what's confusing me here is that your description has a lot of moving parts. Can you simplify the problem into two or three root issues. I have a simple mind and can't deal with all the complexity. :)

|||Let me offer a scenario and you can tell me how it doesn't meet your needs.

You can create a parent package. It can call all the other sub-packages. When the subpackages execute, the parent packages connection is available to them. However, not at design time. I think this is the issue you're having. The sub-package doesn't have areference to the parent package at design time, so it doesn't have the connections either.

What I'd suggest is that you create a Data source per machine and have all the connections reference it. You'll still need to create a connection manager in each package, but they will all reference the same data source. Moving to different machines is no problem because you can have a "machine resident" data source file that properly points to your "ETL Server".

Is this what you're after?|||see responses below, thanks for taking the time to respond.

Q. What kind of connection do you want the one connection to be SQL, file?
A. The connection of the SSIS server where the subpackages reside.

Q. You have one package that you want to call multiple subpackages?
A. YES

Q. You want the subpackages to use the one connection?
A. YES

Q. I'm not sure what you mean by "1 connection to be the 'ETL Server' "
A. If I store all the subpackages on SQL Server, I'll bound to have different servers for different environments, etc.

Q.I don't understand why you need to change the package's sql server location. Which package's SQL Server location? The parent package or the sub-packages?
A. sub-packages

I'm not after the connections inside the subpackage from the parent packaeg, nor the connections in the parent package from the subpackage.

What I am looking for is to create 1 connection and tie it to where the subpackages are stored since it needs to be dynamic without having to spin through every execute package task in the master package and change the sql server it resides on at runtime.

Alternatively, I could create a new connection for every execute child package in my master package all pointing to the same SQL Server that is storing them, but its extremely redundant and would only be useful if I'm pointing to (LOCAL) and the user and password never changes.

Why can't the EXECUTE PACKAGE task just use one of the connections available in the "connections managers" collection? I thought that was the reason for the "connection manager" abstraction layer, so that a physical connection can be changed in one place, and all logical connections are automatically redirected?

Any chance of implementing this? Alternatively, implementing a FOR EACH connection enumerator (although, this would be hokey)?

Followup question would be this: If I'm executing a package from another package, what is the performance difference (if any) of executing a package in general stored in the file system VS. stored in sql server? I'm assuming that if its not stored in sql server, it needs to be brought in from the file system and compiled on its 1st use?

My goal is to create an ETL where multiple developers can work on different components at the same time. The easiest way to do this would be to create 1 package per table. A master package would call all the child packages, but the master package needs to know which ETL server all the child packages are stored in, which changes from environment to environment.
|||

"Why can't the EXECUTE PACKAGE task just use one of the connections available in the "connections managers" collection?"
It can, If all your execute package tasks are in the same package, yes, they can all use the same connection manager. Is this not working for you?

"what is the performance difference (if any) of executing a package in general stored in the file system VS. stored in sql server"
No performance difference between the two storage methods once loaded. Packages aren't compiled. They're read into the object model.

"The easiest way to do this would be to create 1 package per table. "
Really? Why is this easiest. Why limit it to one package per table. Why not just have developers develop package files and check them into sourcesafe? Then when they're done developing them, deploy to SQL Server? Not sure I follow this logic.

Please keep the threads cogent by only asking only one question per thread.

K

|||Hi,
I'm coming into this late and I think I may be misunderstanding it all but let me have a punt anyway.

You have 1 master package which is intended to call multiple child packages.
You want 1 connection manager in the master package to be used to call all child packages.
You want to cycle through a list of all the child packages and execute them using 1 Execute Package Task
If that is all correct then I think what you want to do is possible using property expressions.

Have I understood correctly?

-Jamie|||"Why can't the EXECUTE PACKAGE task just use one of the connections available in the "connections managers" collection?"
It can, If all your execute package tasks are in the same package, yes, they can all use the same connection manager. Is this not working for you?

Whew, thats all i need, maybe its a bug - using June CTP. I create a new execute package task and then create a new connection for it. Then I create a 2nd execute package task, and click on the connection dropdown, but all that is available is New Connection.

|||

Ah! You're using SQL Connections. Dah!
Yes, this is a bug!
Thanks, great find. Please file a bug.
K

CPackage::LoadFromXML Failure

My current package and all backups of previous packages are giving me this error. Anyone got any ideas? I have 1 GB free disk space. Last OS update a month ago. Ready to reinstall SSIS.

Error 1 Error loading SanDiegoRiverside.dtsx: The connection type "FLATFILE" specified for connection manager "ExtractDenormalizedErrors" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name. c:\visual studio 2005\projects\sandiegoriverside\sandiegoriverside\SanDiegoRiverside.dtsx 1 1

Error 2 Error loading SanDiegoRiverside.dtsx: Error loading value "<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts"><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">ExtractDenormalizedErrors</DTS:Property><DTS:Property DTS:Name="DTSID">{92AFE6E1-8EA4-4CE5-BDA1-" from node "DTS:ConnectionManager". c:\visual studio 2005\projects\sandiegoriverside\sandiegoriverside\SanDiegoRiverside.dtsx 1 1

Error 3 Error loading 'SanDiegoRiverside.dtsx' : The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails. . c:\visual studio 2005\projects\sandiegoriverside\sandiegoriverside\SanDiegoRiverside.dtsx 1 1

If you create a new package and then try to create a new connection managers - does it work? If not, the problem might be caused by the issue described in this thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=179671&SiteID=1
and the KB:
http://support.microsoft.com/default.aspx?scid=kb;en-us;913817|||

FYI: This was caused by Flash8 Player Installer.

Laurence

|||

We got the same in production tonight, the package have been running fine for 209 nights and have never been changed. Rerunning the package works fine, I can not reproduce the error. We are running Win2003 X64 Ent. + SQL2K5 X64 Ent SP1. SSIS and RDBMS runs on the same box.

I'm not sure I would like re-register any dll's until I know the cause. Any thoughts?

Thanks in advance!

Niklas

Started: 01:13:52 Error: 2007-03-22 01:13:52.67
Code: 0xC0010018
Source:
Description: Error loading value "<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts"><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">NAV_det</DTS:Property><DTS:Property DTS:Name="DTSID">{C7269043-F2DD-4AE4-A831-C4E5D0632016}</DTS" from node "DTS:ConnectionManager".
End Error
Could not load package "Import_File_To_Table.dtsx" because of error 0xC0010014.
Description: The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails.
Source:
Started: 01:13:52
Finished: 01:13:52
Elapsed: 0.188 seconds

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

|||

I am running into the same issue as LKHall ... I am confused... How come does "This was caused by Flash8 Player Installer" FIX the issue. It does not make sense to me.

Any one knows what is the root cause for this error ?

|||I have unintalled the Flash driver and reboot my PC and I am still running into the package loading failure. Help....|||

The issue seems to be related to DLLs not being registered correctly. When you install new software (the Flash Player, for example) it may register an older or newer version of a DLL, which can can break other applications. Uninstalling may not fix the problem, as that usually does not reverse the changes to the registry.

Instead you may need to re-install the application that is failing, or re-register the DLLs indicated in the articles above.

|||

Thanks John.... Can you please be more specific on what applications (or what DLLs) i need to re-install ? Do you suggest that I need to re-install SQL 2005 again?

I have used the link above to check registry and did not see any wrong registry.

|||Yes, reinstalling SQL 2005 is one way to make sure eveything is set up correctly.|||

Reinstalling SQL 2005 is a good suggestion but any other better ideas ? I have SQL 2005 SP2 installed and reinstalling the entire software is not quite easy....

Plus, I really want to know what is the root cause behind this kind of weird behavior? Doesn't MSFT SSIS team want to know this so that they can fix it in the future SPs?

CPackage::LoadFromXML Failure

My current package and all backups of previous packages are giving me this error. Anyone got any ideas? I have 1 GB free disk space. Last OS update a month ago. Ready to reinstall SSIS.

Error 1 Error loading SanDiegoRiverside.dtsx: The connection type "FLATFILE" specified for connection manager "ExtractDenormalizedErrors" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name. c:\visual studio 2005\projects\sandiegoriverside\sandiegoriverside\SanDiegoRiverside.dtsx 1 1

Error 2 Error loading SanDiegoRiverside.dtsx: Error loading value "<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts"><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">ExtractDenormalizedErrors</DTS:Property><DTS:Property DTS:Name="DTSID">{92AFE6E1-8EA4-4CE5-BDA1-" from node "DTS:ConnectionManager". c:\visual studio 2005\projects\sandiegoriverside\sandiegoriverside\SanDiegoRiverside.dtsx 1 1

Error 3 Error loading 'SanDiegoRiverside.dtsx' : The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails. . c:\visual studio 2005\projects\sandiegoriverside\sandiegoriverside\SanDiegoRiverside.dtsx 1 1

If you create a new package and then try to create a new connection managers - does it work? If not, the problem might be caused by the issue described in this thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=179671&SiteID=1
and the KB:
http://support.microsoft.com/default.aspx?scid=kb;en-us;913817|||

FYI: This was caused by Flash8 Player Installer.

Laurence

|||

We got the same in production tonight, the package have been running fine for 209 nights and have never been changed. Rerunning the package works fine, I can not reproduce the error. We are running Win2003 X64 Ent. + SQL2K5 X64 Ent SP1. SSIS and RDBMS runs on the same box.

I'm not sure I would like re-register any dll's until I know the cause. Any thoughts?

Thanks in advance!

Niklas

Started: 01:13:52 Error: 2007-03-22 01:13:52.67
Code: 0xC0010018
Source:
Description: Error loading value "<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts"><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">NAV_det</DTS:Property><DTS:Property DTS:Name="DTSID">{C7269043-F2DD-4AE4-A831-C4E5D0632016}</DTS" from node "DTS:ConnectionManager".
End Error
Could not load package "Import_File_To_Table.dtsx" because of error 0xC0010014.
Description: The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails.
Source:
Started: 01:13:52
Finished: 01:13:52
Elapsed: 0.188 seconds

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

|||

I am running into the same issue as LKHall ... I am confused... How come does "This was caused by Flash8 Player Installer" FIX the issue. It does not make sense to me.

Any one knows what is the root cause for this error ?

|||I have unintalled the Flash driver and reboot my PC and I am still running into the package loading failure. Help....|||

The issue seems to be related to DLLs not being registered correctly. When you install new software (the Flash Player, for example) it may register an older or newer version of a DLL, which can can break other applications. Uninstalling may not fix the problem, as that usually does not reverse the changes to the registry.

Instead you may need to re-install the application that is failing, or re-register the DLLs indicated in the articles above.

|||

Thanks John.... Can you please be more specific on what applications (or what DLLs) i need to re-install ? Do you suggest that I need to re-install SQL 2005 again?

I have used the link above to check registry and did not see any wrong registry.

|||Yes, reinstalling SQL 2005 is one way to make sure eveything is set up correctly.|||

Reinstalling SQL 2005 is a good suggestion but any other better ideas ? I have SQL 2005 SP2 installed and reinstalling the entire software is not quite easy....

Plus, I really want to know what is the root cause behind this kind of weird behavior? Doesn't MSFT SSIS team want to know this so that they can fix it in the future SPs?

CPackage::LoadFromXML Failure

My current package and all backups of previous packages are giving me this error. Anyone got any ideas? I have 1 GB free disk space. Last OS update a month ago. Ready to reinstall SSIS.

Error 1 Error loading SanDiegoRiverside.dtsx: The connection type "FLATFILE" specified for connection manager "ExtractDenormalizedErrors" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name. c:\visual studio 2005\projects\sandiegoriverside\sandiegoriverside\SanDiegoRiverside.dtsx 1 1

Error 2 Error loading SanDiegoRiverside.dtsx: Error loading value "<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts"><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">ExtractDenormalizedErrors</DTS:Property><DTS:Property DTS:Name="DTSID">{92AFE6E1-8EA4-4CE5-BDA1-" from node "DTS:ConnectionManager". c:\visual studio 2005\projects\sandiegoriverside\sandiegoriverside\SanDiegoRiverside.dtsx 1 1

Error 3 Error loading 'SanDiegoRiverside.dtsx' : The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails. . c:\visual studio 2005\projects\sandiegoriverside\sandiegoriverside\SanDiegoRiverside.dtsx 1 1

If you create a new package and then try to create a new connection managers - does it work? If not, the problem might be caused by the issue described in this thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=179671&SiteID=1
and the KB:
http://support.microsoft.com/default.aspx?scid=kb;en-us;913817|||

FYI: This was caused by Flash8 Player Installer.

Laurence

|||

We got the same in production tonight, the package have been running fine for 209 nights and have never been changed. Rerunning the package works fine, I can not reproduce the error. We are running Win2003 X64 Ent. + SQL2K5 X64 Ent SP1. SSIS and RDBMS runs on the same box.

I'm not sure I would like re-register any dll's until I know the cause. Any thoughts?

Thanks in advance!

Niklas

Started: 01:13:52 Error: 2007-03-22 01:13:52.67
Code: 0xC0010018
Source:
Description: Error loading value "<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts"><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">NAV_det</DTS:Property><DTS:Property DTS:Name="DTSID">{C7269043-F2DD-4AE4-A831-C4E5D0632016}</DTS" from node "DTS:ConnectionManager".
End Error
Could not load package "Import_File_To_Table.dtsx" because of error 0xC0010014.
Description: The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails.
Source:
Started: 01:13:52
Finished: 01:13:52
Elapsed: 0.188 seconds

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

|||

I am running into the same issue as LKHall ... I am confused... How come does "This was caused by Flash8 Player Installer" FIX the issue. It does not make sense to me.

Any one knows what is the root cause for this error ?

|||I have unintalled the Flash driver and reboot my PC and I am still running into the package loading failure. Help....|||

The issue seems to be related to DLLs not being registered correctly. When you install new software (the Flash Player, for example) it may register an older or newer version of a DLL, which can can break other applications. Uninstalling may not fix the problem, as that usually does not reverse the changes to the registry.

Instead you may need to re-install the application that is failing, or re-register the DLLs indicated in the articles above.

|||

Thanks John.... Can you please be more specific on what applications (or what DLLs) i need to re-install ? Do you suggest that I need to re-install SQL 2005 again?

I have used the link above to check registry and did not see any wrong registry.

|||Yes, reinstalling SQL 2005 is one way to make sure eveything is set up correctly.|||

Reinstalling SQL 2005 is a good suggestion but any other better ideas ? I have SQL 2005 SP2 installed and reinstalling the entire software is not quite easy....

Plus, I really want to know what is the root cause behind this kind of weird behavior? Doesn't MSFT SSIS team want to know this so that they can fix it in the future SPs?

Couple of SQL Agent questions

Hi all,
I've got a customer that is creating a LOCAL PACKAGE/SQL AGENT Job to run a
TextImport.exe program on the server, which loads a table and creates a log
file.
They want to be able to print the log file - to a network printer, as the
final step. I do not see PRINT type tasks in the design window. Any
suggestions?
Also, what they have so far runs when they EXECUTE the package from LOCAL
PACKAGES. But when they try to START JOB from SQL AGENT it fails and they
get a message for step 1 saying:
"The step is improperly defined (it needs a valid owner and/or command) and
so could not be run. The step failed."
Thanks.Here is a suggestion.
Use the command task executing print.exe /d:lpt1 c:\filename.log
Most of the time when executing packages through Agent this is a security
issue.
Tushar
"Steve Z" <szlamany@.antarescomputing_no_spam.com> wrote in message
news:eNuHobsLEHA.2440@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I've got a customer that is creating a LOCAL PACKAGE/SQL AGENT Job to run
> a
> TextImport.exe program on the server, which loads a table and creates a
> log
> file.
> They want to be able to print the log file - to a network printer, as the
> final step. I do not see PRINT type tasks in the design window. Any
> suggestions?
> Also, what they have so far runs when they EXECUTE the package from LOCAL
> PACKAGES. But when they try to START JOB from SQL AGENT it fails and they
> get a message for step 1 saying:
> "The step is improperly defined (it needs a valid owner and/or command)
> and
> so could not be run. The step failed."
> Thanks.
>

Couple of SQL Agent questions

Hi all,
I've got a customer that is creating a LOCAL PACKAGE/SQL AGENT Job to run a
TextImport.exe program on the server, which loads a table and creates a log
file.
They want to be able to print the log file - to a network printer, as the
final step. I do not see PRINT type tasks in the design window. Any
suggestions?
Also, what they have so far runs when they EXECUTE the package from LOCAL
PACKAGES. But when they try to START JOB from SQL AGENT it fails and they
get a message for step 1 saying:
"The step is improperly defined (it needs a valid owner and/or command) and
so could not be run. The step failed."
Thanks.Here is a suggestion.
Use the command task executing print.exe /d:lpt1 c:\filename.log
Most of the time when executing packages through Agent this is a security
issue.
Tushar
"Steve Z" <szlamany@.antarescomputing_no_spam.com> wrote in message
news:eNuHobsLEHA.2440@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I've got a customer that is creating a LOCAL PACKAGE/SQL AGENT Job to run
> a
> TextImport.exe program on the server, which loads a table and creates a
> log
> file.
> They want to be able to print the log file - to a network printer, as the
> final step. I do not see PRINT type tasks in the design window. Any
> suggestions?
> Also, what they have so far runs when they EXECUTE the package from LOCAL
> PACKAGES. But when they try to START JOB from SQL AGENT it fails and they
> get a message for step 1 saying:
> "The step is improperly defined (it needs a valid owner and/or command)
> and
> so could not be run. The step failed."
> Thanks.
>

Couple of SQL Agent questions

Hi all,
I've got a customer that is creating a LOCAL PACKAGE/SQL AGENT Job to run a
TextImport.exe program on the server, which loads a table and creates a log
file.
They want to be able to print the log file - to a network printer, as the
final step. I do not see PRINT type tasks in the design window. Any
suggestions?
Also, what they have so far runs when they EXECUTE the package from LOCAL
PACKAGES. But when they try to START JOB from SQL AGENT it fails and they
get a message for step 1 saying:
"The step is improperly defined (it needs a valid owner and/or command) and
so could not be run. The step failed."
Thanks.
Here is a suggestion.
Use the command task executing print.exe /d:lpt1 c:\filename.log
Most of the time when executing packages through Agent this is a security
issue.
Tushar
"Steve Z" <szlamany@.antarescomputing_no_spam.com> wrote in message
news:eNuHobsLEHA.2440@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I've got a customer that is creating a LOCAL PACKAGE/SQL AGENT Job to run
> a
> TextImport.exe program on the server, which loads a table and creates a
> log
> file.
> They want to be able to print the log file - to a network printer, as the
> final step. I do not see PRINT type tasks in the design window. Any
> suggestions?
> Also, what they have so far runs when they EXECUTE the package from LOCAL
> PACKAGES. But when they try to START JOB from SQL AGENT it fails and they
> get a message for step 1 saying:
> "The step is improperly defined (it needs a valid owner and/or command)
> and
> so could not be run. The step failed."
> Thanks.
>

counting records

Hi all,
I have a DTS package that gets information out of a Pervasive db and
drops it into SQL.
I need a count on how many distinct loan number are associated with
specific actions (ie Closing Date, Funded Date etc).
I have 2 tables: 1 contains a reference number and description (ie 170
= Closing Cost, 210 = Funded Date etc) and another table that contains
the reference number and loan number.
So I need a result set that says 20 loan have a closing date, 400 loans
have a funded date etc. There are over 590 reference numbers and over
1.5 million records.
Any help/advice would be immensely apprieciated!!
Thanks,
TonyTry,
select r.ref_desc, count(*) as cnt
from reference as r inner join loan as l
on r.ref_id = l.ref_id
group by r.ref_desc
go
AMB
"Tony" wrote:

> Hi all,
> I have a DTS package that gets information out of a Pervasive db and
> drops it into SQL.
> I need a count on how many distinct loan number are associated with
> specific actions (ie Closing Date, Funded Date etc).
> I have 2 tables: 1 contains a reference number and description (ie 170
> = Closing Cost, 210 = Funded Date etc) and another table that contains
> the reference number and loan number.
> So I need a result set that says 20 loan have a closing date, 400 loans
> have a funded date etc. There are over 590 reference numbers and over
> 1.5 million records.
> Any help/advice would be immensely apprieciated!!
> Thanks,
> Tony
>|||Hi Tony
You don't post ddl but you can do something like (untested):
SELECT [loan number], SUM(CASE WHEN [Closing Date] IS NOT NULL THEN 1 ELSE 0
END) AS NumClosed
SUM(CASE WHEN [Funded Date]IS NOT NULL THEN 1 ELSE 0 END) AS NumFunded
FROM #MyTable
GROUP BY [loan number]
John
"Tony" wrote:

> Hi all,
> I have a DTS package that gets information out of a Pervasive db and
> drops it into SQL.
> I need a count on how many distinct loan number are associated with
> specific actions (ie Closing Date, Funded Date etc).
> I have 2 tables: 1 contains a reference number and description (ie 170
> = Closing Cost, 210 = Funded Date etc) and another table that contains
> the reference number and loan number.
> So I need a result set that says 20 loan have a closing date, 400 loans
> have a funded date etc. There are over 590 reference numbers and over
> 1.5 million records.
> Any help/advice would be immensely apprieciated!!
> Thanks,
> Tony
>|||Hi John,
I thought of this but I have over 590 reference fields...don't feel
like writing a sum/case for each one|||Hi John,
I thought of this but I have over 590 reference fields...don't feel
like writing a sum/case for each one|||On 26 Oct 2005 12:23:47 -0700, Tony wrote:

>Hi John,
>I thought of this but I have over 590 reference fields...don't feel
>like writing a sum/case for each one
Hi Tony,
Did you try Allejandro's suggestion?
If that doesn't help you, then please refer to www.aspfaq.com/5006 to
find out what extra information you need to post in order to help us
help you.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi
590 columns in a table seems a large number. It is already causing you
problems by creating unwillingness try a solution!
You can use the information_schema.columns to create the sql for you.
e.g.
USE NORTHWIND
SELECT 'CASE WHEN ' + QUOTENAME (COLUMN_NAME) + ' IS NULL THEN 0 ELSE 1
END AS [SUM_' + COLUMN_NAME + '],'
from informatioN_schema.columns
where table_name = 'orders'
ORDER BY ORDINAL_POSITION
John
Tony wrote:
> Hi John,
> I thought of this but I have over 590 reference fields...don't feel
> like writing a sum/case for each one