Problem running SSIS from C# program.

I am able to run it from the file system but when I try running it from SQL Server I get an error message that it cannot load package. I am using the following code.

Package package = app.LoadFromSqlServer("trans_hist", Properties.Resources.dbServerName, Properties.Resources.userId, Properties.Resources.password, null);Nobody is doing this?|||What is the error message exactly? Can you see the package in the MSDB database when looking in Management Studio?

Have you read through this? http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.application.loadfromsqlserver.aspx|||The specified package could not be loaded from the SQL Server Database /r/n

I tried both this
Package package = app.LoadFromSqlServer("trans_hist", Properties.Resources.dbServerName, Properties.Resources.userId, Properties.Resources.password, null);

and this
Package package = app.LoadFromSqlServer("\\MSDB\\Maintenance Plans\\trans_hist", "ppntt240", "id", "password", null);

I also tries adding .dtsx to the SSIS name.|||I think you may want:

"\\Maintenance Plans\\trans_hist"|||That was it, thank you.

Problem running SSIS from C# program.

I am able to run it from the file system but when I try running it from SQL Server I get an error message that it cannot load package. I am using the following code.

Package package = app.LoadFromSqlServer("trans_hist", Properties.Resources.dbServerName, Properties.Resources.userId, Properties.Resources.password, null);

Nobody is doing this?
|||What is the error message exactly? Can you see the package in the MSDB database when looking in Management Studio?

Have you read through this? http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.application.loadfromsqlserver.aspx|||The specified package could not be loaded from the SQL Server Database /r/n

I tried both this
Package package = app.LoadFromSqlServer("trans_hist", Properties.Resources.dbServerName, Properties.Resources.userId, Properties.Resources.password, null);

and this
Package package = app.LoadFromSqlServer("\\MSDB\\Maintenance Plans\\trans_hist", "ppntt240", "id", "password", null);

I also tries adding .dtsx to the SSIS name.
|||I think you may want:

"\\Maintenance Plans\\trans_hist"|||That was it, thank you.

Problem running SSIS from C# executable when kicked off by another executable.

If I kick off my C# executable from the server everything works fine but if I have another C# executable kick off the C# executable that runs the SSIS I get an error message saying it cannot run SSIS package. I am thinking it sounds like a permissions problem but not sure. Any suggestions/guidance would be greatly appreciated.

Errors I am getting.

2007-09-26 15:02:30,187 [1] ERROR reporting.Processor [(null)] - Problem with Direct Script

2007-09-26 15:02:30,843 [1] ERROR reporting.Processor [(null)] - {Microsoft.SqlServer.Dts.Runtime.DtsError, Microsoft.SqlServer.Dts.Runtime.DtsError, Microsoft.SqlServer.Dts.Runtime.DtsError, Microsoft.SqlServer.Dts.Runtime.DtsError, Microsoft.SqlServer.Dts.Runtime.DtsError}

Thanks in advance.It appears to hqve a different ID running the executable when kicked off from the first executable which is kicked off from JCL. When I run the Exe it has my id and runs fine. What is a better approach to fixing this, changing the roles on the SSIS scripts or adding the id to SQL Server? Assuming this is the problem.|||You might have to better show the whole picture. Where packages are stored, how many packages, what your "C# executables" do, and then you mention JCL, a mainframe concept. Seems like quite a few pieces here.

Do you have package logging turned on?|||

Can you add a message box in your app to display the current credentials? I'd guess that your user credentials aren't being passed from the first app to the second.

Problem running SSIS from C#

have one script that works fine. I am doing the exact same thging with this new script and it runs fine from C# on my desktop and runs fine from SS on the server but comes back with a failure when trying to run from C# on the server. Is there any additional info I can retrieve about the problem? All I am getting right now is "Failure" from the result field.

if (result == DTSExecResult.Failure)

{

Console.WriteLine("Task failed or abended");

log.Error("Problem with DTS Script");

log.Error(result);

}

else

I would first try looking at the error info in Package.Errors, especially the error description stored in each error object in the collection.|||package.errors did not really give me any useful information. Is there anything else I can look at or canm anybody think of any reason why the SSIS would not work from a C# program but works everyplace else? I have others that work and cannot find any difference between the two.|||If you have other packages that work from your C# program, then it's possible something in that particular package is causing a problem (e.g. missing dependency for a task). I would try to narrow this down by reducing that package is something simpler that works (e.g. by disabling or removing tasks), then start adding back functionality until you get an error.|||

Ted Lee - MSFT wrote:

If you have other packages that work from your C# program, then it's possible something in that particular package is causing a problem (e.g. missing dependency for a task). I would try to narrow this down by reducing that package is something simpler that works (e.g. by disabling or removing tasks), then start adding back functionality until you get an error.

Package runs from the C# on my desktop and runs from SQL Server from the server. It is just after moving the EXE to the server and running it on the server that the SSIS will come back as a "Failure" and bomb.

|||What is the actual error message reported from the package?sql

Problem running SSIS from C#

I am having a problem running a SSIS Script from within a C# program. Script was running fine and then one day it stopped running and is giving me the following errors and nothing has changed.

2007-07-18 14:27:52,098 [1] ERROR reporting.Processor [(null)] - Problem with DTS Script

2007-07-18 14:27:52,895 [1] ERROR reporting.Processor [(null)] - {Microsoft.SqlServer.Dts.Runtime.DtsError, Microsoft.SqlServer.Dts.Runtime.DtsError}

The SSIS runs fine from the process on my machine, from SQL Server on my machine and from SQL Server on the server. But when I run it from the C# executable I get the above errors. This process also runs other SSIS scripts and they all work fine. I am using the following code to execute the script. Can anybody give me some ideas on how to troubleshoot this problem.

Package package = app.LoadFromSqlServer("\\Maintenance Plans\\SCRA2", "ppntt240", "load_abc", "bcp123", null);

DTSExecResult result = package.Execute();

Variables vars = package.Variables;
int rowcount = Convert.ToInt32(vars["count"].Value);
String rowcount2 = "0000000000" + rowcount.ToString();

string fullcnt = rowcount2.ToString().Substring(rowcount2.Length - 10, 10);

if (result == DTSExecResult.Failure)
{
Console.WriteLine("Task failed or abended");
log.Error("Problem with DTS Script");
log.Error(package.Errors);

}
else
{
Console.WriteLine("Task ran successfully");

Can you enable logging in the package so that you can get more detail about the error?|||How do I do this?|||Right-click on the package in the designer, choose Logging, add a log provider (Text is simple to set up, but database is good too), and check the checkbox beside the package name. In the details tab, make sure you check OnError and OnWarning,|||Logging is not an option if I right click. If I select run I see logging and it ask for log provider e.g.(text file, event log or SQL Server) and Configuration String. Is this where I turn it on? What are the best options? Thanks.
|||If you open the package in BIDS, go to the control flow for the package, and right-click in an empty area (one not occupied by a task), you should see the logging option.|||Is there anything unique to the server that would cause the following errors?

PackageStart,PPNTT240,PNCNT\FF22882,SCRA2,{0D01AF9F-5C9C-4B54-942E-2B97FCF12826},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:21 PM,7/19/2007 12:35:21 PM,0,0x,Beginning of package execution.

OnError,PPNTT240,PNCNT\FF22882,Data Flow Task Direct_Prod,{F36A5050-8FC5-4A2F-A457-42AB134844CF},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E07.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E07 Description: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".

OnError,PPNTT240,PNCNT\FF22882,SCRA2,{0D01AF9F-5C9C-4B54-942E-2B97FCF12826},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E07.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E07 Description: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".

OnError,PPNTT240,PNCNT\FF22882,Data Flow Task Direct_Prod,{F36A5050-8FC5-4A2F-A457-42AB134844CF},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-1073450982,0x,component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC0202009.

OnError,PPNTT240,PNCNT\FF22882,SCRA2,{0D01AF9F-5C9C-4B54-942E-2B97FCF12826},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-1073450982,0x,component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC0202009.

OnTaskFailed,PPNTT240,PNCNT\FF22882,Data Flow Task Direct_Prod,{F36A5050-8FC5-4A2F-A457-42AB134844CF},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,0,0x,(null)
OnWarning,PPNTT240,PNCNT\FF22882,SCRA2,{0D01AF9F-5C9C-4B54-942E-2B97FCF12826},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-2147381246,0x,The Execution method succeeded, but the number of errors raised (2) 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.

PackageEnd,PPNTT240,PNCNT\FF22882,SCRA2,{0D01AF9F-5C9C-4B54-942E-2B97FCF12826},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,1,0x,End of package execution.

|||

agentf1 wrote:

Is there anything unique to the server that would cause the following errors?

OnError,PPNTT240,PNCNT\FF22882,Data Flow Task Direct_Prod,{F36A5050-8FC5-4A2F-A457-42AB134844CF},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E07.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E07 Description: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".

OnError,PPNTT240,PNCNT\FF22882,SCRA2,{0D01AF9F-5C9C-4B54-942E-2B97FCF12826},{AF032472-7840-4C2B-99D6-692FA7A2AD22},7/19/2007 12:35:27 PM,7/19/2007 12:35:27 PM,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E07.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E07 Description: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".

Looks like you have problem with you source data. Since you get out-of-range problem, my bet is that the source date format is messed up. Probably server is expecting mm/dd/yyyy and your source date is dd/mm/yyyy

Thanks.

|||Data is fine. Script runs on server outside of C#.|||Are you absolutely positive that the package is connecting to the same data sources when you run it from C#? I have a hard time believing that an error message on a data conversion error isn't caused by the data |||As John said, Check for the connections in the data flow task (Direct_Prod) when you run from the C# and in Server.

Thanks|||It is the same. It was running fine and then one day just started bombing. It runs from the server ok and runs from the C# ok when it is on my desktop. Strangest thing I have ever seen.

I recently added the logging so I am sure I have the same version.
|||I figured out my problem. The date comes in like this in most cases 07/09/2007 but comes in like this when run via C# on the server 7/9/2007. I am doing this when I build my query in the string + HolidayEndDt.ToString.Substring(0, 10) +

It is also apparently giving me problems in another spot where I compare month to see if it is EOM and it fails because it is comparing 07 to 7/ since I am using substring for that as well.

What do most people do in these instances? How do you build a query in a string that contains a date? Thanks.
|||

agentf1 wrote:

HolidayEndDt.ToString.Substring(0, 10)
What do most people do in these instances? How do you build a query in a string that contains a date? Thanks.

Assuming you HolidayEndDt is datetime type,

HolidayEndDt.ToString("yyyyMMdd") should solve your problem, and as far as i know thats one of the best way to deal with dates.

Thanks

|||

Yes, that is what I did. Thanks.

Actually I did HolidayEndDt.ToString("MM/dd/yyyy") but I guess there is 6 in one and half dozen in the other. More or less the same thing.

Problem Related to Package execution in SSIS

Hi,

I have the hierarchy of packages..

there are three levels..

first package (say L1) calls two child packages.(say L2_1 , L2_2)

the parameters are passed to L2_1 from L1 and it executes completely and successfully.

next step is L1 calls L2_2 without any parameters.. and package fails with error as execution of L2_2 failed.

when I executed L2_2 individually it successfully executes.

L2_2 itself calls 5 other packages (at level 3)..

Am not able to identify the possible reason for failure..

I suggest you turn on package logging in each package (right click on the control flow and select logging) and then come back here with specific error messages once captured.|||

Thanks for you help phil...

now I can see the errors due to which my package is failing..

sql

Problem Pulling From Access DB

We are currently moving existing SSIS packages from one server to another. The former server is 32 bit and the new server is 64 bit clustered (not sure if that's relevant or not, though).

One package in particular is giving me a headache. It pulls from an access file to sql server 2005 table. We set up the security as don't save senstive and put the connection strings in a config file. We had to set the original job up to run the package step under a proxy account to get it to work.

If we right click on the package through the SSIS Store interface and execute it runs fine. However when we try to run it through the scheduled job, it fails. I very much appears to be a permissions issue on the proxy account. The person setting up the server is somewhat new to the area of SQL Servers, so they cannot provide much feedback. We're very much in a tweak until it works position, unfortunately.

Here are the errors that I am getting, one from the history of the job, one from the SQL Server logging. Does anyone have a suggestion, and if it is a permissions issue, where to look? I've been at this for days, so I've tried several different approaches. It seems like right now that I almost got it to work, but it seems like the connection to the access db is failing. But if that's the case, why does it run fine through the proxy on the original server (using same account)? I guess that very well could come down to the 64 bit problem (saw this on some other posts). I should mention that I set up the package as Run64BitRuntime = false. Do I need to rethink this as a batch file using DTExec? That was actually our original solution on the former server, but we could never get to work.

Job History Error:

DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Cost_WorkDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2007-03-23 16:19:14.92 Code: 0xC0047017 Source: Data Flow Task DTS.Pipeline Description: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C. End Error Progress: 2007-03-23 16:19:14.92 Source: Data Flow Task Validating: 100% complete End Progress Error: 2007-03-23 16:19:14.94 Code: 0xC004700C Source: Data Flow Task DTS.Pipeline Description: One or more component failed validation. End Error Error: 2007-03-23 16:19:14.94 Code: 0xC0024107 Source: Data Flow Task Description: There were errors during task validation.... The package execution fa... The step failed.

sysdtslog90 error:

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Cost_WorkDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.

OK I solved the problem myself. This was in fact a 32-bit vs. 64-bit problem, even though the lovely errors I was getting didn't even come close to explaing that to me. I provided a link below on how to run a package in a scheduled job that hits a Jet 4.0 connection (I think excel may have this problem also).

But, I hate when people simply do that (include link), so here's how I fixed it.

Create a new scheduled job.
Add a new step.
When you edit the step, the 'Type' will be "Operating System (cmdExec)"
You can use DtExecUI to create your command line step, but here is what I used exactly - quotation mark locations are VERY important. Also, I think you may need to have the complete call on one line:

\\Server\E$\Program Files\Microsoft SQL Server (x86)\90\DTS\Binn\DTExec.exe /DTS "\MSDB_ATC\PACKAGE_NAME" /SERVER "SERVER_NAME" /CONFIGFILE "\\Server\R$\\PACKAGE_CONFIG.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

Edit command to match yours, and paste into job step edit.

Hope this helps someone, it took me DAYS to figure out.

http://msdn2.microsoft.com/en-us/library/ms141766.aspx

|||

thank you... It's realy helpful to me.

The following is my update list,

\\Server\E$\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe /DTS "\MSDB_ATC\PACKAGE_NAME" /SERVER "SERVER_NAME" /CONFIGFILE "\\Server\R$\\PACKAGE_CONFIG.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

|||Thanks Jay, this saved my day

Problem of Parent-Child package with ASP.Net

I have an ASP.NET application that calls a SSIS package. The SSIS package internally calls some other child packages. I observed that sometimes some child pacakges are not even called by the parent. and the behaviour is very indeterminate. sometimes they work fine. sometimes they don't. There is no clue available for this behaviour when the pacakges are not executed. (One general observation is that the memory consumption is very high. but that is the case always.)

I have enabled logging on all child packages. The log is not updated at all when the child packages failed to execute. i.e. the package execution does not start.

Could somebody explain why this is happening? any suggestions/ similar experiences?

Regards

Saurabh

One more observation: if i run the same parent package as a stand-alone. (i.e. not through the asp.net application) it always executes fine. it calls all the child packages very well always. what is different in the asp.net context?

Problem looping through excel workbooks(sheets) in a excel file in ssis

Hi i am new to ssis and i am trying to transfer data from excel file with multiple workbooks(tables) to oledb destination......i followed the instructions given in msdn but they r vague and do not help in achieving the result pls help me...

Quote:

Originally Posted by ashwinkpes

Hi i am new to ssis and i am trying to transfer data from excel file with multiple workbooks(tables) to oledb destination......i followed the instructions given in msdn but they r vague and do not help in achieving the result pls help me...

Use a Variable to Set the property of the work sheet($1,$2,etc) and Create the package.

Change that variable to Ursheet 1, sheet 2, sheet 3 while executing it...|||Thanks Buddy(Anil) it worked...[:)]|||

Quote:

Originally Posted by ashwinkpes

Thanks Buddy(Anil) it worked...[:)]

Fine.you can post some alternatives if you have..

Problem inserting data into onw of my tables

i created an SSIS package to look for data in a table on another system and compare it with the table i have in system 2, if there are any changes to system 1 then it must apply them to system 2

My Problem:

It scans through my table and finds all the correct records to insert, but when it has to insert the new data into System 2 table i keep on getting violation and contraint errors because of the primary key and foreign key constraints. how can i get around this, or does anyone have an alternative solution for me.

Total Specification Requirements:

i have 2 systems both running SQL Server. everytime data gets updates in system 1, the same change needs to be made in system 2. The databases and tables are identical.

Any Help would be graetlty appreciated

Kind Regards

Carel Greaves

Carel,

There is nothing magic in SSIS that helps you with data issues like PK/FK violations. With the few details you are providing about your package I just can think in a few tips:

Know your data. If data is being duplicate by the inserts, you need to know why. Same for the FK. I don't know how you are performing the inserts; but is you are doing it in a data flow; at the very least you could enable error redirection in your OLE Destination component and capture the row(s) that generate the exceptions; so you have a better understanding of the situation. Perhaps the 'check if row exists' logic is not accurate. See this for some approaches: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1|||

Thanks Rafael

More info on my package.

I have a database called HISDirectory, inside HISDirectory is a Table called HISMemberData

I have two Servers with the same database. One server is situated in Australia and the other in SA.

Everytime a new member is added to HISMemberData in SA, it must take that same value and insert it into the HISMemberData Table in Australia.

the table only has 4 fields. ID, MemberID, HealthID, DateSend.

There is a KEY on MemberID and on HealthID

If i use the

INSERT INTO HISMemberData (MemberID, HealthID, DateSend)

VALEUS (123, 123, 123)

then it works fine, but i can't do a:

INSERT INTO HISMemberData (MemberID, HealthID, DateSend)

SELECT * FROM tmpTableWithCorrectValues

I have tried that method from the guys link you sent me before but it crashes with the same problem every way that i try.

Even if i could just get a SQL query to help me with the population of this table. I have created a SSIS package to import the table from the other side into a new table and then try to populate the table locally, but i just seem too stupid to get it done.

Please Help

Kind Regards

Carel Greaves

|||

Ypou should be able to solve this using the approach described in the link I gave you. You need to make sure the lookup componnet is properly set up; this is: The join conditions should uniquely identify if the row exists in the target table and the error out put should set to redirect the errors ( a row not found in the lookup is treated as an error); then the error out put are you inserts and the other output are your discard or update rows.

Make sure the PK and FK definitions are identical in both tables.

Problem inserting data into onw of my tables

i created an SSIS package to look for data in a table on another system and compare it with the table i have in system 2, if there are any changes to system 1 then it must apply them to system 2

My Problem:

It scans through my table and finds all the correct records to insert, but when it has to insert the new data into System 2 table i keep on getting violation and contraint errors because of the primary key and foreign key constraints. how can i get around this, or does anyone have an alternative solution for me.

Total Specification Requirements:

i have 2 systems both running SQL Server. everytime data gets updates in system 1, the same change needs to be made in system 2. The databases and tables are identical.

Any Help would be graetlty appreciated

Kind Regards

Carel Greaves

Carel,

There is nothing magic in SSIS that helps you with data issues like PK/FK violations. With the few details you are providing about your package I just can think in a few tips:

Know your data. If data is being duplicate by the inserts, you need to know why. Same for the FK. I don't know how you are performing the inserts; but is you are doing it in a data flow; at the very least you could enable error redirection in your OLE Destination component and capture the row(s) that generate the exceptions; so you have a better understanding of the situation. Perhaps the 'check if row exists' logic is not accurate. See this for some approaches: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1|||

Thanks Rafael

More info on my package.

I have a database called HISDirectory, inside HISDirectory is a Table called HISMemberData

I have two Servers with the same database. One server is situated in Australia and the other in SA.

Everytime a new member is added to HISMemberData in SA, it must take that same value and insert it into the HISMemberData Table in Australia.

the table only has 4 fields. ID, MemberID, HealthID, DateSend.

There is a KEY on MemberID and on HealthID

If i use the

INSERT INTO HISMemberData (MemberID, HealthID, DateSend)

VALEUS (123, 123, 123)

then it works fine, but i can't do a:

INSERT INTO HISMemberData (MemberID, HealthID, DateSend)

SELECT * FROM tmpTableWithCorrectValues

I have tried that method from the guys link you sent me before but it crashes with the same problem every way that i try.

Even if i could just get a SQL query to help me with the population of this table. I have created a SSIS package to import the table from the other side into a new table and then try to populate the table locally, but i just seem too stupid to get it done.

Please Help

Kind Regards

Carel Greaves

|||

Ypou should be able to solve this using the approach described in the link I gave you. You need to make sure the lookup componnet is properly set up; this is: The join conditions should uniquely identify if the row exists in the target table and the error out put should set to redirect the errors ( a row not found in the lookup is treated as an error); then the error out put are you inserts and the other output are your discard or update rows.

Make sure the PK and FK definitions are identical in both tables.