Anyone else have errors when checking out SSIS package from SourceSafe or TFS?

I get errors when I check out an SSIS package from source control (both Source Safe and TFS) relating to the connection objects where I was not the original developer who checked it in. Is there a solution to this other than altering the connection login\password for every connection object in the package before deploying?

Yes, I do have problems as well when updating CVS.

For SSIS the solution is to use configuration files for your connections and Security / Protection Level property set to "DontSaveSensitive".

For Reporting services and cubes I am still a bit confused.

For reports, As soon as I open them in BIDS, I immediately edit the shared data sources to retype in the account and password. If I do not do it and go to the data page, it will loose all stored procedures parameters :-(

For Cubes, I also revisit the data sources as soon as I open the cube. Not doing so will let you work on your cube and deploy it, however you will not be able to browse, aggregate or process it.

Philippe

|||

The critical item here is to not have the Package Protection Level Property set to Encrypt[Sensitive or All]WithUserKey. That uses the developer's Windows login to encrypt information in the package, so no other developer will be able to work with it very easily, unless they log in with the same Windows account.

As Phillippe said, a common workaround is using a ProtectionLevel of "DontSaveSensitive" and configurations, which also makes it easier to deploy the packages in multiple environments. You could also use "EncryptSensitiveWithPassword" which will mean you have to enter a password for the package before running it, but it will be able to store the passwords inside the package, rather than depending on configurations. The best choice depends on your scenario.

Any way to programmatically deploy an SSAS solution?

Hi all,

I'm trying to add a nightly build/deploy process for my SSAS solution. Basically - to take my AS solution from source control, deploy it to our dev server, and process just a few partitions.

What's the best way to do this? Looking at the solution folder, the .dsv, .dim, .cube, .ds files are all xml-based, which is good, but they don't seem like XML/A. (Seem very similar to a serialized version of AMO objects, but not quite exactly)

The only way I'd guess to do this is to manually load the individual xml files into an object model, and then map them to the AMO objects (since they're pretty similar..), and deploy those to the server. Is there a more efficient way to directly de-serialize the .dim,.cube, etc files into the AMO structures and deploy?

Take a look at the Deployment Wizard which you can run from Start... Programs... Microsoft SQL Server 2005... Analysis Services... Deployment Wizard.

Also see more info including command line switches here:

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

|||

Furmangg, perfect tip on using the deployment wizard. I think this will suit our needs very well.

I was going to follow up w/ the correct method of compiling - I tried msbuild unsuccessfully, but I found this great blog post by Thomas Kejser -

http://schastar.spaces.live.com/blog/cns!12BCB785A5D8B3D4!148.entry?beid=cns!12BCB785A5D8B3D4!148&d=1&wa=wsignin1.0

Sweet!

Any suggestions on how to process very large files?

I am receiving large XML files from an external source. I need to inseert
all the <DATA> elements into a table on SQL Server. These files can be as
small as a couple k or as large as 3 megs. I would like to input all the data
using sprocs on the database server. Can I really really do that or should I
just write a client side app to read the data and insert into the table?
(An example is attached with just 3 data points. Most have tens of thousands
of data points.)
What is the most effiecent approach to handling large XML files? What
technology works the best? If there is information I should be reading on
this I do not seem to be able to locate it in MSDN.
Any help will be appreciated.
AlanS
<?xml version="1.0" encoding="utf-8" ?>
<ORGANIZATION xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://oasis.caiso.com/oasisv003.xsd">
CAISO
<REPORT_ITEM>
<HEADER>
<REPORT>AS_FINAL_MCP</REPORT>
<SYSTEM>OASIS</SYSTEM>
<TZ>PPT</TZ>
<MKT_TYPE>A</MKT_TYPE>
<UOM>US$/MW</UOM>
<INTERVAL>ENDING</INTERVAL>
<SEC_PER_INTERVAL>3600</SEC_PER_INTERVAL>
</HEADER>
<DATA>
<DATA_ITEM>GNSPIN_PRC</DATA_ITEM>
<RESOURCE_NAME>AZ2</RESOURCE_NAME>
<OPR_DATE>2004-10-29</OPR_DATE>
<INTERVAL_NUM>1</INTERVAL_NUM>
<VALUE>0.8</VALUE>
</DATA>
<DATA>
<DATA_ITEM>GNSPIN_PRC</DATA_ITEM>
<RESOURCE_NAME>AZ2</RESOURCE_NAME>
<OPR_DATE>2004-10-29</OPR_DATE>
<INTERVAL_NUM>2</INTERVAL_NUM>
<VALUE>0.8</VALUE>
</DATA>
<DATA>
<DATA_ITEM>GNSPIN_PRC</DATA_ITEM>
<RESOURCE_NAME>AZ2</RESOURCE_NAME>
<OPR_DATE>2004-10-29</OPR_DATE>
<INTERVAL_NUM>3</INTERVAL_NUM>
<VALUE>0.8</VALUE>
</DATA>
</REPORT_ITEM>
<DISCLAIMER_ITEM>
<DISCLAIMER>The contents of these pages are subject to change without
notice. Decisions based on information contained within the web site are the
visitor's sole responsibility.
</DISCLAIMER>
</DISCLAIMER_ITEM>
</ORGANIZATION>
If you have a dedicated machine with otherwise no load, you should be able
to use OpenXML.
If you have other loads on your machine or the data is often larger than a
couple of 100kB, you may want to consider the XML Bulkload object.
Best regards
Michael
"AlanS" <AlanS@.discussions.microsoft.com> wrote in message
news:BB511473-A60B-4E4F-B492-F050D8024B65@.microsoft.com...
>I am receiving large XML files from an external source. I need to inseert
> all the <DATA> elements into a table on SQL Server. These files can be as
> small as a couple k or as large as 3 megs. I would like to input all the
> data
> using sprocs on the database server. Can I really really do that or should
> I
> just write a client side app to read the data and insert into the table?
> (An example is attached with just 3 data points. Most have tens of
> thousands
> of data points.)
> What is the most effiecent approach to handling large XML files? What
> technology works the best? If there is information I should be reading on
> this I do not seem to be able to locate it in MSDN.
> Any help will be appreciated.
> AlanS
>
> <?xml version="1.0" encoding="utf-8" ?>
> <ORGANIZATION xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance"
> xsi:noNamespaceSchemaLocation="http://oasis.caiso.com/oasisv003.xsd">
> CAISO
> <REPORT_ITEM>
> <HEADER>
> <REPORT>AS_FINAL_MCP</REPORT>
> <SYSTEM>OASIS</SYSTEM>
> <TZ>PPT</TZ>
> <MKT_TYPE>A</MKT_TYPE>
> <UOM>US$/MW</UOM>
> <INTERVAL>ENDING</INTERVAL>
> <SEC_PER_INTERVAL>3600</SEC_PER_INTERVAL>
> </HEADER>
> <DATA>
> <DATA_ITEM>GNSPIN_PRC</DATA_ITEM>
> <RESOURCE_NAME>AZ2</RESOURCE_NAME>
> <OPR_DATE>2004-10-29</OPR_DATE>
> <INTERVAL_NUM>1</INTERVAL_NUM>
> <VALUE>0.8</VALUE>
> </DATA>
> <DATA>
> <DATA_ITEM>GNSPIN_PRC</DATA_ITEM>
> <RESOURCE_NAME>AZ2</RESOURCE_NAME>
> <OPR_DATE>2004-10-29</OPR_DATE>
> <INTERVAL_NUM>2</INTERVAL_NUM>
> <VALUE>0.8</VALUE>
> </DATA>
> <DATA>
> <DATA_ITEM>GNSPIN_PRC</DATA_ITEM>
> <RESOURCE_NAME>AZ2</RESOURCE_NAME>
> <OPR_DATE>2004-10-29</OPR_DATE>
> <INTERVAL_NUM>3</INTERVAL_NUM>
> <VALUE>0.8</VALUE>
> </DATA>
> </REPORT_ITEM>
> <DISCLAIMER_ITEM>
> <DISCLAIMER>The contents of these pages are subject to change without
> notice. Decisions based on information contained within the web site are
> the
> visitor's sole responsibility.
> </DISCLAIMER>
> </DISCLAIMER_ITEM>
> </ORGANIZATION>
>
|||Hello, Michael!
You wrote on Fri, 29 Oct 2004 18:30:10 -0700:
MRM> If you have other loads on your machine or the data is often larger
MRM> than a couple of 100kB, you may want to consider the XML Bulkload
MRM> object.
Since the original authers xml format is very simple I would suggest to use
SqlXmlBulkLoad object and nothing else.
With best regards, Alex Shirshov.
|||Ok. I have tried to use SQLXmlBulkLoad. But, I am having a problem. First
point though. I am told that this is an invalid connection string.
//
// sqlConnection1
//
this.sqlConnection1.ConnectionString = "workstation id=DEVELOPER;packet
size=4096;integrated security=SSPI;data source=ALANS;persist security
info=False;initial catalog=Llama";
The connection string works. I use it earlier to connect to populate a grid.
Here is my using statement;
using SQLXMLBULKLOADLib;
Here is my code
(1) SQLXMLBulkLoad objBL = new SQLXMLBulkLoad();
(2) objBL.ConnectionString = this.sqlConnection1.ConnectionString;
(3) objBL.ErrorLogFile = @."C:\APSES\error.log";
(4)objBL.Execute(@."C:\APSES\XML TestsSampleSchema.xml", @."C:\APSES\XML
TestsSampleXMLData.xml");
(5)objBL = null;
Exception occurs at line (4). Invalid Connect String.
Here is the error log
<?xml version="1.0"?>
<Result State="FAILED">
<Error><HResult>0x80040E21I32</HResult>
<Description><![CDATA[Invalid connection string.]]></Description>
<Source>XML BulkLoad for SQL Server</Source>
<Type>FATAL</Type>
</Error>
</Result State>
What is incorrect and how do I correct it?

Any solution? Cannot initialize the data source object of OLE DB provider "microsoft.jet.ol

This is a problem that never get solved, sometime I can use other way to avoid it, but havn't found a solution yet, i hope I can get some more idea here.

I am using SQL 2005, when I run

select * into #import1
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\jeff2.xls',
'select * from [jeff2$]')

I get

Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".

when I try to compile a SP with that statement in it, I get the same error, like

create stored procedure test

as begin

select * into #import1
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\jeff2.xls',
'select * from [jeff2$]')

end

so it seems the error may not relate to the real file, since at the compile stage, it should not check the real file?

On my live db, after I restart the SQL service, the statement will work, after a while, one or several days, I get the same error again. I can not restart my live db quite often for sure, so now I have another backup db server, I need run the statement on the backup server and then read the data from there.

I have the same problem at two places, both use SQL 2005.

So far there are three questions

1, why it works after restart, but only last for a while? something about memory? since the backup db seldom need restart and work fine after many days.

2, why it gives error in compile stage?

3, why two dbs in different Enviroment has the same problem

The most answer I have gathered so far is permission issue, true I got similar error if the import file is located in a place which SQL has no right to access. But in this case, it should not be.

Any other idea or suggestion?

thanks

I have the same puzzle like you.My data source is access.Do you have some other solution now.Give me a help.

|||

When I remove the password from the access file ,the problem solved.But the file must be set a password.What I can do?

Any solution? Cannot initialize the data source object of OLE DB provider "microsoft.jet.ol

This is a problem that never get solved, sometime I can use other way to avoid it, but havn't found a solution yet, i hope I can get some more idea here.

I am using SQL 2005, when I run

select * into #import1
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\jeff2.xls',
'select * from [jeff2$]')

I get

Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".

when I try to compile a SP with that statement in it, I get the same error, like

create stored procedure test

as begin

select * into #import1
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\\ws8\web\jeff2.xls',
'select * from [jeff2$]')

end

so it seems the error may not relate to the real file, since at the compile stage, it should not check the real file?

On my live db, after I restart the SQL service, the statement will work, after a while, one or several days, I get the same error again. I can not restart my live db quite often for sure, so now I have another backup db server, I need run the statement on the backup server and then read the data from there.

I have the same problem at two places, both use SQL 2005.

So far there are three questions

1, why it works after restart, but only last for a while? something about memory? since the backup db seldom need restart and work fine after many days.

2, why it gives error in compile stage?

3, why two dbs in different Enviroment has the same problem

The most answer I have gathered so far is permission issue, true I got similar error if the import file is located in a place which SQL has no right to access. But in this case, it should not be.

Any other idea or suggestion?

thanks

I have the same puzzle like you.My data source is access.Do you have some other solution now.Give me a help.

|||

When I remove the password from the access file ,the problem solved.But the file must be set a password.What I can do?

any differents and advices about connection manager

first set up data source from solution explorer,then new connection from data source from connection managers

directly new ole db connection or ado.net connection etc by right clicking from connection managers

At the end doesn't matter how you created it; the connection manager will work exactly the same. The only diffrence is that with the first approach you only have to create a connection manager once and then can use it in multiple packages.

Rafael Salas

Answer: Shared Datasource

Hey ladies and gentlemen,
I am trying to create a shared data source and am getting the following error -

An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'Best_Data_Corp'. (rsErrorOpeningConnection)
Login failed for user 'XvTVTVTVXOP.

Background information:
- I have a sql server 2000 with sp3 on a windows 2003 server which is within a network.
- I have reportings services on a web server thats outside my network and configured through dmz so its accessible through the network and it can see within the network.
- I am using reporting services 2000...

Any one seen this error and what where your fixes if any. I have been working on this problem for about a week now...I have seen post with people with similar problems but no one has posted a resolution.

Thanks in advance...

Check if you have access to this datasource. When you added this ds as a shared datasource, did you 'test connection' ?|||Check that the views being used by your report have DBO ownerhips. VS 2005 has a habit of creating views with the developer as owner. Things then work fine on your development box but not when deployed.

Answer: Shared Datasource

Hey ladies and gentlemen,
I am trying to create a shared data source and am getting the following error -

An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'Best_Data_Corp'. (rsErrorOpeningConnection)
Login failed for user 'XvTVTVTVXOP.

Background information:
- I have a sql server 2000 with sp3 on a windows 2003 server which is within a network.
- I have reportings services on a web server thats outside my network and configured through dmz so its accessible through the network and it can see within the network.
- I am using reporting services 2000...

Any one seen this error and what where your fixes if any. I have been working on this problem for about a week now...I have seen post with people with similar problems but no one has posted a resolution.

Thanks in advance...

Check if you have access to this datasource. When you added this ds as a shared datasource, did you 'test connection' ?|||Check that the views being used by your report have DBO ownerhips. VS 2005 has a habit of creating views with the developer as owner. Things then work fine on your development box but not when deployed.

ANSWER: Re: SQLEXPRESS Database Issue - Cannot open database ASPNETDB.MDF requested by the login

Hello Guys

This is my connection string

<add name ="ASPNETDBConnectionString1" connectionString ="Data Source= .\SQLEXPRESS; Integrated Security = True; DataBase = ASPNETDB.MDF; User ID = MyWindowsUserName; Password = MyWindowsPassword; User Instance = False; Connect Timeout = 30" providerName ="System.Data.SqlClient"/>

I tried to research on the internet and i got a solution on changing the permissions for this database to enable user SystemName/ASPNET, but iam not able to access this ASPNETDB.MDF from SqlServer and if i go to server explorer in vs2005, i dint know where to chage the permissions.

Can anyone help me on this.

Thanks a lot

The ASPNET account needs to have access both to the server and to the ASPNETDB database. It seems you have granted server access to the account, but not database access.

To grant database access, connect to SQL Server using Management Studio and execute the following SQL statements:

use ASPNETDB
go
create user [SystemName\ASPNET]
go

Thanks
Laurentiu

|||

hi,

Thanks for your info abt the adding of aspnet account in the ASPNETDB Db.

I have created a website using VS 2005, and my SQL Server 2005 -STD Edition(NOT SQLEXPRESS). When ever i try to connect to the database i get this following error : Cannot open database ASPNETDB.MDF requested by the login, login failed. Login failed for user DOMAIN|USERNAME.

FYI, I have added my DOMAIN\username in the Security - Logins of the Database. I have also done the user mappings. That screen reads as follows :
1. Master Database -

Still I get this error. Please help me understand that whether any other permissions is required in the database for me.

Thanks

krans001

|||

hi,

I get this error when i try to execute a website just having a DEFAULT.aspx in which i have added a webpartsmanager. the personalization property of the webpartmanager is set to TRUE and USER. The following steps I did:

1. Installed the Microsoft SQL Server Management Studio 9.00.1399.00 ( STANDERD EDITION).

2. from the C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 installed the aspnet_regsql and followed the steps in installing the aspnetdb database.

In the usermappings i have provided permissions to me "DOMAIN\USERNAME" to the following db: 1. master 2. msdb 3. tempdb 4. aspnetdb

3. in the edit global configuration removed the USER INSTANCE=TRUE from the following
Data Source=.\MSSQLSERVER2005;Initial Catalog=aspnetdb.mdf;Integrated Security=True

4. edited the machine.config file under the C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG
as
<add name="LocalSqlServer" connectionString="Data Source=.\MSSQLSERVER2005;Initial Catalog=aspnetdb.mdf;Integrated Security=True" providerName="System.Data.SqlClient" />

After doing this i tried to execute the website that i have created which holds a default.aspx page in which i have the webpartmanager. I just wanted to view this file in the browser. As soon as i do this i get this error.

Cannot open database "aspnetdb.mdf" requested by the login. The login failed. Login failed for user 'DOMAIN\USERNAME'.

Thanks in advance for the solution. Please help.. if anyone has a solution please send it immediately to my email id : sriranga.kr@.gmail.com

|||

hi

all i did was removed the .mdf in the connection string and run the same. It works like a charm..

great...

|||

hi sreeksdave

all you need to do is to change the following :

<add name ="ASPNETDBConnectionString1" connectionString ="Data Source= .\SQLEXPRESS; Integrated Security = True; DataBase = ASPNETDB.MDF; User ID = MyWindowsUserName; Password = MyWindowsPassword; User Instance = False; Connect Timeout = 30" providerName ="System.Data.SqlClient"/>

AS

<add name ="ASPNETDBConnectionString1" connectionString ="Data Source= .\SQLEXPRESS; Integrated Security = True; Initial Catalog= ASPNETDB" providerName ="System.Data.SqlClient"/>

and see how it works..

Do let me know..

Regards

scotty

|||I think this should be rather aspnetdb than aspnetdb.mdf, if you use a user instance you have to specify the filename but with attached database you can just type in the name of the database.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

Remember that both files ASPNETDB.MDF and aspnetdb_log.LDF are in a folder call App_Data in you project solution, just make sure that you give the right access privileges to your local MACHINE\ASPNET account

Hope this will help

|||

Hi all,

Thanks for all your contribution. It has worked for me and i have also helped few people.

Thanks & Regards

Raaj

|||

Hello Ranga,

Thanks a lot yar.

Hats off

ANSWER: Re: SQLEXPRESS Database Issue - Cannot open database ASPNETDB.MDF requested by the login

Hello Guys

This is my connection string

<add name ="ASPNETDBConnectionString1" connectionString ="Data Source= .\SQLEXPRESS; Integrated Security = True; DataBase = ASPNETDB.MDF; User ID = MyWindowsUserName; Password = MyWindowsPassword; User Instance = False; Connect Timeout = 30" providerName ="System.Data.SqlClient"/>

I tried to research on the internet and i got a solution on changing the permissions for this database to enable user SystemName/ASPNET, but iam not able to access this ASPNETDB.MDF from SqlServer and if i go to server explorer in vs2005, i dint know where to chage the permissions.

Can anyone help me on this.

Thanks a lot

The ASPNET account needs to have access both to the server and to the ASPNETDB database. It seems you have granted server access to the account, but not database access.

To grant database access, connect to SQL Server using Management Studio and execute the following SQL statements:

use ASPNETDB
go
create user [SystemName\ASPNET]
go

Thanks
Laurentiu

|||

hi,

Thanks for your info abt the adding of aspnet account in the ASPNETDB Db.

I have created a website using VS 2005, and my SQL Server 2005 -STD Edition(NOT SQLEXPRESS). When ever i try to connect to the database i get this following error : Cannot open database ASPNETDB.MDF requested by the login, login failed. Login failed for user DOMAIN|USERNAME.

FYI, I have added my DOMAIN\username in the Security - Logins of the Database. I have also done the user mappings. That screen reads as follows :
1. Master Database -

Still I get this error. Please help me understand that whether any other permissions is required in the database for me.

Thanks

krans001

|||

hi,

I get this error when i try to execute a website just having a DEFAULT.aspx in which i have added a webpartsmanager. the personalization property of the webpartmanager is set to TRUE and USER. The following steps I did:

1. Installed the Microsoft SQL Server Management Studio 9.00.1399.00 ( STANDERD EDITION).

2. from the C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 installed the aspnet_regsql and followed the steps in installing the aspnetdb database.

In the usermappings i have provided permissions to me "DOMAIN\USERNAME" to the following db: 1. master 2. msdb 3. tempdb 4. aspnetdb

3. in the edit global configuration removed the USER INSTANCE=TRUE from the following
Data Source=.\MSSQLSERVER2005;Initial Catalog=aspnetdb.mdf;Integrated Security=True

4. edited the machine.config file under the C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG
as
<add name="LocalSqlServer" connectionString="Data Source=.\MSSQLSERVER2005;Initial Catalog=aspnetdb.mdf;Integrated Security=True" providerName="System.Data.SqlClient" />

After doing this i tried to execute the website that i have created which holds a default.aspx page in which i have the webpartmanager. I just wanted to view this file in the browser. As soon as i do this i get this error.

Cannot open database "aspnetdb.mdf" requested by the login. The login failed. Login failed for user 'DOMAIN\USERNAME'.

Thanks in advance for the solution. Please help.. if anyone has a solution please send it immediately to my email id : sriranga.kr@.gmail.com

|||

hi

all i did was removed the .mdf in the connection string and run the same. It works like a charm..

great...

|||

hi sreeksdave

all you need to do is to change the following :

<add name ="ASPNETDBConnectionString1" connectionString ="Data Source= .\SQLEXPRESS; Integrated Security = True; DataBase = ASPNETDB.MDF; User ID = MyWindowsUserName; Password = MyWindowsPassword; User Instance = False; Connect Timeout = 30" providerName ="System.Data.SqlClient"/>

AS

<add name ="ASPNETDBConnectionString1" connectionString ="Data Source= .\SQLEXPRESS; Integrated Security = True; Initial Catalog= ASPNETDB" providerName ="System.Data.SqlClient"/>

and see how it works..

Do let me know..

Regards

scotty

|||I think this should be rather aspnetdb than aspnetdb.mdf, if you use a user instance you have to specify the filename but with attached database you can just type in the name of the database.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

Remember that both files ASPNETDB.MDF and aspnetdb_log.LDF are in a folder call App_Data in you project solution, just make sure that you give the right access privileges to your local MACHINE\ASPNET account

Hope this will help

|||

Hi all,

Thanks for all your contribution. It has worked for me and i have also helped few people.

Thanks & Regards

Raaj

|||

Hello Ranga,

Thanks a lot yar.

Hats off