Cannot open SSIS project in BIDS

Hi,
I have been working on an SSIS project for sometime now. The project files are located on a remote server. Suddenly I am not able to open the solution I get a lot of error messages and all the data flow taks are gone. I later found out that SSIS encrypts packages, so that other users will not be able to see them. Fine, but I have been using the same windows user account for months now. What could be the problem?
This is what I get when trying to open the solution:

There were errors while the package was being loaded.
The package might be corrupted.
See the Error List for details.

And the error list also contains messages saying "Could not load from xml".

Any pointers will be much appreciated.

TIA
Can you please post the specific errors you are receiving? You can copy and paste from the error list.|||Hi,
thanks for that quick reply.
Here are the error messages from the error list.
There are 9 control flow tasks and these three messages seem to
occur for all of them.

Error 1
Error loading RecruteToTempJobs01.dtsx: Unable to instantiate
XML DOM document, please verify that MSXML binaries are properly
installed and registered.
d:\ssis\RecruteToTempJobs01.dtsx

Error 2
Error loading RecruteToTempJobs01.dtsx: Unable to load from
XML. d:\ssis\RecruteToTempJobs01.dtsx

Error 3
Error loading RecruteToTempJobs01.dtsx: Error loading a task.
The contact information for the task is "Performs high-performance data
extraction, transformation and loading;Microsoft Corporation; Microsoft
SQL Server v9; (C) 2004 Microsoft Corporation; All Rights
Reserved;http://www.microsoft.com/sql/support/default.asp;1". This happens when
loading a task fails.
d:\ssis\RecruteToTempJobs01.dtsx|||

Could you try to register xml libraries on your machine:

regsvr32 msxml3.dll

regsvr32 msxml6.dll

Try to search this forum and you will find a lot of details about issues similar to the one you are seeing.

Thanks.

|||Thanks a ton Bob, that worked!

Just curious, what could have happened to require the dlls to be registered. I didn't register them before and things were working fine.

Thanks once again!
|||

I do not remember exactly. Some setup issues...

Try to search this forum, there was a lot of posts about this.

Thanks.

|||This is quite likely a setup issue in an entirely different application. These DLLs are used by a wide range of applications, and any one of them could have a dodgy installer, stuff you may not even think about. For example I have seeen several instances of "security patches" being deployed via SMS that have broken the registration for one or more of the MSXML versions.|||

Thanks for the information Darren,

the culprit must be one of those 'security patches' in my case I guess!

Thanks.

cannot merge after triming the key values

Hi,

I am new to SSIS.I am doing a left outer jon using the merge join.The output was wrong.I realised that may be we have to trim the key values.But when i do the trim in the derived column,the output of the derived columns shows that issorted is becoming false and the sort key position has become 0.So the merge join is showing error.How can i solve this error.

Please help.

TRIM would reset the isSorted property because removing leading and trailing spaces affects the sort order. " B" will sort ahead of "A", because of the leading space. If you are querying from a relational DB, I'd recommend doing the TRIM and SORT in your source query, and set the isSorted and SortKey properties in the Advanced Editor of the OLE DB Source. If it's not a relational source, just add a sort transform after your derived column, but before the merge join.

Cannot map tinyint to a SSIS data type in resultset

If I have an executesql task that returns a result set, if the result set contains a tinyint, I can't find what SSIS data type to declare the variable as. They all cause errors.

Answered my own question have a look at my post

http://www.sqljunkies.com/WebLog/simons/archive/2006/02/24/tinyint_in_SSIS.aspx

Cannot map tinyint to a SSIS data type in resultset

If I have an executesql task that returns a result set, if the result set contains a tinyint, I can't find what SSIS data type to declare the variable as. They all cause errors.

Answered my own question have a look at my post

http://www.sqljunkies.com/WebLog/simons/archive/2006/02/24/tinyint_in_SSIS.aspx

Cannot install SSIS or Notification services

I'm trying to reinstall SQL Server 2005 STD but I cannot get Setup to install Integration Services or Notification Services. I looked in the log and this is what I found. Needless to say, it makes no sense to me. It seems that Setup is trying to find a missing source list for a certain product. I have no clue what that product is. I've spent endless hours trying to figure it out and I'm still drawing a blank. This is a printout of the log.

Integration services -
-
MSI (s) (F0:B0) [02:33:01:437]: Machine policy value 'DisableUserInstalls' is 0
MSI (s) (F0:B0) [02:33:02:188]: End dialog not enabled
MSI (s) (F0:B0) [02:33:02:188]: Original package ==> G:\Servers\Setup\SqlRun_DTS.msi
MSI (s) (F0:B0) [02:33:02:188]: Package we're running from ==> C:\WINDOWS\Installer\188966.msi
MSI (s) (F0:B0) [02:33:02:339]: APPCOMPAT: looking for appcompat database entry with ProductCode '{EE8CFFD9-6E29-4DC3-A967-7348D5F41F44}'.
MSI (s) (F0:B0) [02:33:02:339]: APPCOMPAT: no matching ProductCode found in database.
MSI (s) (F0:B0) [02:33:02:339]: MSCOREE already loaded, using loaded copy
MSI (s) (F0:B0) [02:33:02:379]: Couldn't find local patch ''. Looking for it at its source.
MSI (s) (F0:B0) [02:33:02:379]: Resolving Patch source.
MSI (s) (F0:B0) [02:33:02:379]: User policy value 'SearchOrder' is 'nmu'
MSI (s) (F0:B0) [02:33:02:379]: User policy value 'DisableMedia' is 0
MSI (s) (F0:B0) [02:33:02:379]: Machine policy value 'AllowLockdownMedia' is 0
MSI (s) (F0:B0) [02:33:02:379]: SOURCEMGMT: Media enabled only if package is safe.
MSI (s) (F0:B0) [02:33:02:379]: SOURCEMGMT: Looking for sourcelist for product {8B7E2749-624E-4C61-92FC-2BC18307FF24}
MSI (s) (F0:B0) [02:33:02:379]: Note: 1: 1706 2: {8B7E2749-624E-4C61-92FC-2BC18307FF24} 3:
MSI (s) (F0:B0) [02:33:02:379]: SOURCEMGMT: Failed to resolve source
MSI (s) (F0:B0) [02:33:02:399]: Note: 1: 1708
MSI (s) (F0:B0) [02:33:02:399]: Note: 1: 2729
MSI (s) (F0:B0) [02:33:02:409]: Note: 1: 2729
MSI (s) (F0:B0) [02:33:02:409]: Product: Microsoft SQL Server 2005 Integration Services -- Installation failed.

MSI (s) (F0:B0) [02:33:02:439]: MainEngineThread is returning 1612
The installation source for this product is not available. Verify that the source exists and that you can access it.
G:\Servers\Setup\SqlRun_DTS.msi
MSI (c) (B8:88) [02:33:02:439]: Decrementing counter to disable shutdown. If counter >= 0, shutdown will be denied. Counter after decrement: -1
MSI (c) (B8:88) [02:33:02:439]: MainEngineThread is returning 1612
=== Verbose logging stopped: 5/19/2006 2:33:02 ===

--
Notification services -
--
MSI (s) (F0:30) [02:33:07:566]: Machine policy value 'DisableUserInstalls' is 0
MSI (s) (F0:30) [02:33:08:087]: End dialog not enabled
MSI (s) (F0:30) [02:33:08:087]: Original package ==> G:\Servers\Setup\SqlRun_NS.msi
MSI (s) (F0:30) [02:33:08:087]: Package we're running from ==> C:\WINDOWS\Installer\18898f.msi
MSI (s) (F0:30) [02:33:08:167]: APPCOMPAT: looking for appcompat database entry with ProductCode '{37E9AD9F-3217-4229-B5A5-7A0C82364C6C}'.
MSI (s) (F0:30) [02:33:08:167]: APPCOMPAT: no matching ProductCode found in database.
MSI (s) (F0:30) [02:33:08:167]: MSCOREE already loaded, using loaded copy
MSI (s) (F0:30) [02:33:08:187]: Couldn't find local patch ''. Looking for it at its source.
MSI (s) (F0:30) [02:33:08:187]: Resolving Patch source.
MSI (s) (F0:30) [02:33:08:187]: User policy value 'SearchOrder' is 'nmu'
MSI (s) (F0:30) [02:33:08:187]: User policy value 'DisableMedia' is 0
MSI (s) (F0:30) [02:33:08:187]: Machine policy value 'AllowLockdownMedia' is 0
MSI (s) (F0:30) [02:33:08:187]: SOURCEMGMT: Media enabled only if package is safe.
MSI (s) (F0:30) [02:33:08:187]: SOURCEMGMT: Looking for sourcelist for product {65A498A6-83B9-4B91-99EA-1BA88D73F0AA}
MSI (s) (F0:30) [02:33:08:187]: Note: 1: 1706 2: {65A498A6-83B9-4B91-99EA-1BA88D73F0AA} 3:
MSI (s) (F0:30) [02:33:08:187]: SOURCEMGMT: Failed to resolve source
MSI (s) (F0:30) [02:33:08:187]: Note: 1: 1708
MSI (s) (F0:30) [02:33:08:187]: Note: 1: 2729
MSI (s) (F0:30) [02:33:08:187]: Note: 1: 2729
MSI (s) (F0:30) [02:33:08:187]: Product: Microsoft SQL Server 2005 Notification Services -- Installation failed.

MSI (s) (F0:30) [02:33:08:197]: MainEngineThread is returning 1612
The installation source for this product is not available. Verify that the source exists and that you can access it.
G:\Servers\Setup\SqlRun_NS.msi
MSI (c) (B8:DC) [02:33:08:207]: Decrementing counter to disable shutdown. If counter >= 0, shutdown will be denied. Counter after decrement: -1
MSI (c) (B8:DC) [02:33:08:207]: MainEngineThread is returning 1612

During your first install, the MSIs were cached to the specified location (G:\servers\setup\...). Apparently those files were subsequently deleted or perhaps the drive is no longer accessible. Are you reinstalling from the original media or are you using add/remove programs?|||

Paul, I noticed these lines in the error log.

Integration services -
-
MSI (s) (F0:B0) [02:33:02:188]: Original package ==> G:\Servers\Setup\SqlRun_DTS.msi
MSI (s) (F0:B0) [02:33:02:188]: Package we're running from ==> C:\WINDOWS\Installer\188966.msi
--
Notification services -
--
MSI (s) (F0:30) [02:33:08:087]: Original package ==> G:\Servers\Setup\SqlRun_NS.msi
MSI (s) (F0:30) [02:33:08:087]: Package we're running from ==> C:\WINDOWS\Installer\18898f.msi

However, I can't find the C:\WINDOWS\Installer folder on my computer. Is this significant?

|||

The c:\windows\installer folder is ordinarily hidden. To see it, go to Windows Explorer tools\folder options and under the view tab turn on both "Display the contents of system folders" and "Show hidden files and folders". Once you've done that, the folder and its contents should be visible.

Paul

Cannot insert a variable in SSIS Package designer

Hello,
This morning I got an odd problem, I just added a new package to my dtproj and I cannot add a variable. Am I missing anything. ?

here is the image
Sometimes I find that VS forgets where it is, as though it looses the context that the SSIS designer is active, so SSIS specific stuff is just not available. When this happens try clicking on the package design surface to set the focus back to SSIS, then re-open the Variables window or other window that is giving you trouble.|||I'm having a similar problem. I cannot even open the Variables window. If I right click the design surface and select Variables, nothing happens. I've tried creating a new package and starting from scratch but I get the same problem. I even uninstalled SQL Server and all of the client tools and reinstalled and applied all of the service packs but I still get nothing when I try to create a variable. I'm willing to try just about anything at this point.|||I had a similar problem with Visual Studio once, and using the /resetuserdata switch fixed it. It resets all your VS settings back as if you had just installed it. See this link for a little more information: http://msdn2.microsoft.com/en-us/library/bb245788(vs.80).aspx|||Thanks a million!!!! That worked.

Cannot insert a variable in SSIS Package designer

Hello,
This morning I got an odd problem, I just added a new package to my dtproj and I cannot add a variable. Am I missing anything. ?

here is the image
Sometimes I find that VS forgets where it is, as though it looses the context that the SSIS designer is active, so SSIS specific stuff is just not available. When this happens try clicking on the package design surface to set the focus back to SSIS, then re-open the Variables window or other window that is giving you trouble.|||I'm having a similar problem. I cannot even open the Variables window. If I right click the design surface and select Variables, nothing happens. I've tried creating a new package and starting from scratch but I get the same problem. I even uninstalled SQL Server and all of the client tools and reinstalled and applied all of the service packs but I still get nothing when I try to create a variable. I'm willing to try just about anything at this point.|||I had a similar problem with Visual Studio once, and using the /resetuserdata switch fixed it. It resets all your VS settings back as if you had just installed it. See this link for a little more information: http://msdn2.microsoft.com/en-us/library/bb245788(vs.80).aspx|||Thanks a million!!!! That worked.

Cannot import Oracle data with OLE DB

I have a column in an Oracle source system with data type NUMBER(38,2). The value "-0.01" is causing problems when trying to import into a SSIS data-flow.

The only way I can import this into my data-flow is by using a Datareader connection manager using the ODBC Data Provider. My DSN is using the Oracle ODBC driver.

If I try and use the "Native OLE DB\Microsoft OLE DB Provider for Oracle" I get an error: "The data value cannot be converted for reasons other than sign mismatch or data overflow"

Judging by this post: http://microsoftdw.blogspot.com/2005/11/final-storyhow-to-get-data-out-of.html there aren't really any other combinations to try that will bring my data in as I want it.

I don't want to use ODBC though as:

Its an old technology Its slow I have to deploy an additional DSN.

Can anyone tell me why the other options don't work? Why does Microsoft OLE DB Provider for Oracle have a problem with "-0.01"?

-Jamie

When I tried to reproduce the issue reported by you, I encountered different set of problems. I tried to copy data from a Oracle table with NUMBER(38,2) column to a SQL Server 2005 table with a NUMERIC (38,2) column using OraOLEDB (and later using MSDORA) OLE DB connection manager at the source. I dont get any error messages when I execute the package. However, no data is transferred and I do not see any data when I preview the data at the source. May be I am missing something that you did that produced the error message you got.|||

Well I can't even preview the data so I think my problem is more deep seated. My errors occur in the source adapter and hence I think I have a problem in the middleware.

-Jamie

Cannot import file from Mainframe

Hello Everybody!
Im trying to migrate my SQL 2000 packages that′s currently working in the company production enviroment to SSIS packages. So, in the 2000 version i got the flat file from Mainframe and i had to do a trick to transform all the columns to match the same size as the example above:

1432 1138734217 1144256628<CRLF>
1432 1138734217<CRLF>
1433 1136657788 1122441177<CRLF>
1433 1125554545 1122441177<CRLF>
1433 1192925544 1122441177<CRLF>

So, when i import the file, first of all i have to transform the Text file to another texfile file fixing the size to 32..... but you can see that in the second row im receiving a CRLF and if i try to import without the trick, the preview of the ragged file show me the columns desorganized...

Someone knows how to import it without transform to another text file with fixed length?
Thanks
Cleber

Hi

Have you already tried with a Flat File Connection Manager using the Ragged Right format and the {CR}{LF} Header Row delimiter ?

I've quickly tried and it seems to be ok.

Cheers,

|||Sorry!

I tried this way but the mainframe did not send a header :(

Thanks for the help!

Cannot have two flat file destinations in a package (possible bug)?

During my development of a ssis package i've noticed that when creating two control flows that pulling data from seperate tables, each going to its own flat file, that the second keeps the attributes of the column names from the first. So when I create my second flat file, not only does it have the names of its correct columns but has the name of the the first flat file.

I'm hoping that I've explained the correctly. I'll provide more info "OR" I can provide the code to package if anyone would like.

Bogey1 wrote:

During my development of a ssis package i've noticed that when creating two control flows that pulling data from seperate tables, each going to its own flat file, that the second keeps the attributes of the column names from the first. So when I create my second flat file, not only does it have the names of its correct columns but has the name of the the first flat file.

I'm hoping that I've explained the correctly. I'll provide more info "OR" I can provide the code to package if anyone would like.

You can only have one data flow per data flow task.|||You can have more than one Data Flow Task per package and multiple Data Connections. Try that way. Two Data Connections and two Data Flows Task.|||

Bogey1 wrote:

During my development of a ssis package i've noticed that when creating two control flows that pulling data from seperate tables, each going to its own flat file, that the second keeps the attributes of the column names from the first. So when I create my second flat file, not only does it have the names of its correct columns but has the name of the the first flat file.

I'm hoping that I've explained the correctly. I'll provide more info "OR" I can provide the code to package if anyone would like.

Are you using the same Flat File Connection Manager for both Flat File Destinations? It sounds as though you are.

-Jamie

Cannot have two flat file destinations in a package (possible bug)?

During my development of a ssis package i've noticed that when creating two control flows that pulling data from seperate tables, each going to its own flat file, that the second keeps the attributes of the column names from the first. So when I create my second flat file, not only does it have the names of its correct columns but has the name of the the first flat file.

I'm hoping that I've explained the correctly. I'll provide more info "OR" I can provide the code to package if anyone would like.

Bogey1 wrote:

During my development of a ssis package i've noticed that when creating two control flows that pulling data from seperate tables, each going to its own flat file, that the second keeps the attributes of the column names from the first. So when I create my second flat file, not only does it have the names of its correct columns but has the name of the the first flat file.

I'm hoping that I've explained the correctly. I'll provide more info "OR" I can provide the code to package if anyone would like.

You can only have one data flow per data flow task.|||You can have more than one Data Flow Task per package and multiple Data Connections. Try that way. Two Data Connections and two Data Flows Task.|||

Bogey1 wrote:

During my development of a ssis package i've noticed that when creating two control flows that pulling data from seperate tables, each going to its own flat file, that the second keeps the attributes of the column names from the first. So when I create my second flat file, not only does it have the names of its correct columns but has the name of the the first flat file.

I'm hoping that I've explained the correctly. I'll provide more info "OR" I can provide the code to package if anyone would like.

Are you using the same Flat File Connection Manager for both Flat File Destinations? It sounds as though you are.

-Jamie