Any Idea how to deal with it ?
Thanks,
FahadI set the type of Flat File Source object to Unicode and problem was no more. But I still wonder whats the reason of this error ?
I have an SSIS project that is deployed to a sql server.
The setup of the project is a parent dtsx package, and many child packages.
The parent executes each of the chid packages.
If I run the parent from BIDS, calling the children that are on the SQL Server, it works fine.
If I execute it from SQL Management Console running on my laptop, connected to the SQL Server, It works.
If I remote into the SQL Server and run it from SQL Agent or SQL Management Console, the job fails with the error:
Error 0xC0202009 while preparing to load the package. SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x%1!8.8X!.
for each of the children.
This has quite suddenly stopped working, and I cannot figure out why it will work being executed from my laptop, but not on the server.
Any ideas?
Thanks
BobP
When you remote in, are you logging in with the same user/password that you use on your laptop? If not, it is probably a problem with security.|||Yes I am. That's what is stumping me. Since I am running it from SQL Management Studio logged in as the same user, it should run from both machines.
Thanks
BobP
|||The difference is that for the first two scenarios you listed above, you are likely executing them from your machine. That is, dtexec.exe on your machine is executing the packages. When you remote in, you are using the dtexec.exe on the server. This means that things like network connectivity/firewalls/etc... come into play that might not be on your local machine.What version of SQL Server do you have installed on your local workstation? Do you have the full SSIS client installed?|||
Hey Phil!
I didn't realize that if i run a sql package, logged into SSIS in SQL Management Console that it ran with the DTExec on my machine. Thanks for that bit of info.
However... Yes, I have the full SSIS installed on my machine, and the versions are:
Server 9.0.3042
Workstation: 9.0.3042
The irritating thing about this is that it started happening after it had been working for 2 months.
And as far as I know, nothing has changed on the server.
BobP
|||
BobP - BIM wrote:
However... Yes, I have the full SSIS installed on my machine, and the versions are:
Server 9.0.3042
Workstation: 9.0.3042
More specifically, Standard, Developer, or Enterprise versions? Also, which is installed on the server?|||
Sever is running enterprise 64 bit
Workstation is running developer.
Thanks!
|||
Sever is running enterprise 64 bit
Workstation is running developer.
Thanks!
Okay, so using this arrangement, when connected to Integration Services, through SSMS, you should be executing the copy on the server. That's my understanding.
Has this package run successfully before on a 64-bit machine? Do you have package logging turned on?|||
Nevermind... After a server re-boot, it is working fine now.
Thanks for all the help!
BobP
I'm trying to do a simple flat file import of a .csv file. The task keeps failing on me and I get the following error
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039
I looked up the error codes and the only information I can find is that a thread is failing. What would cause this and how can I fix it? I can open the same file in Excel without any problems. I'd really appreciate any insight that anyone has to offer.
Does your package do anything else than importing the flat file?
Could you try nail down a piece of data in your file that is causing this? You can do it by splitting the file in two and keeping the failing part until you get the minimal amount of data causing this failure.
If your data is not sensitive I would like to do some testing with your file as well.
Thanks.
|||Bob,
Thank you for the tip. I've been working on it all day and finally started coming to the same place you mentioned. I was finally able to get rid of the error by modifying the column data types in the Flat File connection.
Error 0x80070003 while loading package file "C:\VS Projects\Testing\Run Codes to DB\Run Codes to DB\SofAid_DBRefresh.dtsx". The system cannot find the path specified.
Thank You
Jaime
You should use a Package Configuration to populate a variable that specifies where the child package is located. Then use an expression on the connection manager to set the appropriate property from this variable.
You could have a configuration file on your workstation and one on the server, each specifying the appropriate path for that environment. Unless you want to hard-code the location of the file, you might want to specify it's location with an environment variable. Or you could just use an environment variable to hold to package location directly if that's the only configuration information you have.
|||That seems pretty round-about. Is there a way of telling it that the package is in the same folder, or a relative path?
|||No, there's no concept of relative paths in SSIS. Remember that a package may never even exist as a file.
|||You need to use package configuration to set the path and any other property value, dinamically at run time. Serach by package configurations in BOL and this forum.|||Thank you, that was very helpful
Has anyone ever run into this error? I have an SSIS package that works fine on our test server, but when I move it to production it's giving this error. The error is on the step that is using a variable/expression to connect to a "dynamic" database and server. It's a execute SQL command returning xml. I watched the variables in the locals and was lucky enough to find the the xml was getting this error. The exact error is:
<ROOT><?MSSQLError HResult=\"0x80040e37\" Source=\"Microsoft SQL Native Client\" Description=\"Invalid object name 'exp_a_client_vw'.\"?></ROOT>\r\n
Thanks,
Phil
The error message indicates that it can't find the 'exp_a_client_vw' object. Since you are using a dynamic connection, are you sure it is pointing to the correct database?|||I'm pretty sure it is. Like I said, it's the same package that worked on dev and it found it's way to the correct database/server. I'm watching the variables in the locals as it gets to that step/container and they are correct. I'm trying to figure out now how I can write the entire connection string to a variable and watch it.
Phil
|||Use a script task to copy the value of the expression based variable to a regular variable immediately before the Exec SQL step.|||Ok, I follow you so far. But how exactly can I tell the script task to grab the "ConnectionString" property of the my "Transform XML (SP) connection" which is the connections' exact name?
Thanks,
Phil
|||You're setting the connect string on the connection manager via an expression, right? Something like:
Code Snippet
"Data Source=" + User::DataSource + ";Initial Catalog=" + User::Catalog" + ";Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"Move that value to a new expression based variable. Then, in the connection manager, set the expression for the connection string to the new variable you created.
|||Thanks. I got it. Turns out that the SP I was running doesn't have that object. I appreciate your help.
Phil
