Can not expand MSDB folder

Hi!

I am trying to deploy a package to SQL. However, when I open Integration services I can not expand MSDB folder. Error is; SQL Server specified in SSIS service configuration is not present or available.... Login time expired .....(MsDTSSrvr)

I have found post here that says MsDtsSrvr.ini.xml file needs to be changed from

<Name>MSDB</Name>
<ServerName>.</ServerName>

to

<Name>MSDB</Name>
<ServerName>MyServerName</ServerName>

I tried MyServerName - does not work. I also tried value from connecting to Database Engine ( MyServerName\SQL2K5LOCAL) - does not help either.

What am I doing wrong?

Also, I need some documentation on deploying packgaes using File System.

I have saved Copy of the Package as File System location.

When I doubleclick the .dtsx file, Connection Manager holds my local SQL info. Goal is to deploy this to Development and other environments. Can this be handled some other way so I do not have to modify this file on every environment?

Also, this Execute Utility Package says at the top to use this utility to run SSIS on local computer.

So far, help on MSoft sites did not really help me a lot.

this is what I am using:

Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727

Installed Edition: IDE Standard

SQL Server Analysis Services
Microsoft SQL Server Analysis Services Designer
Version 9.00.2047.00

SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.2047.00

Ljiljana wrote:

Also, I need some documentation on deploying packgaes using File System.

I have saved Copy of the Package as File System location.

When I doubleclick the .dtsx file, Connection Manager holds my local SQL info. Goal is to deploy this to Development and other environments. Can this be handled some other way so I do not have to modify this file on every environment?

You can use package configurations, which allow you to store connection strings and other information outside of the package in a file, database, or environment variable. Right-click on the background of the package when it is open in BIDS, and you'll see the configurations option on the menu.

Ljiljana wrote:

Also, this Execute Utility Package says at the top to use this utility to run SSIS on local computer.

You can use SQL Server Agent to schedule the job to run on the server.

Sorry, I can't help with the MSDB issue.

|||

Thanks John!

I am aware of the Config wizard.

I have found article by Jamie T. (www.sqlis.com/26.aspx) where he explains how to use direct configuration.

However, I am doing something wrong as I was not able to deploy this to another PC.

I went to my coworker PC and in Integration Services -File System imported my package.

When I say Run it still has data from my local PC. It will work if I modify connection string in Execute Package Utility in Integration Services to point to his PC but then I wonder where is disconnecti between this package and Environment.dtsConfig file. I thought if I change <ConfiguredValue> to his PC name in this .dtsConfig file it will pick it up from there.

I had folder c:\PackageConfiguration\Environment.dtsConfig on his local PC

|||

I have fond resolution to the issue

please look at the question asked on this forum "File System and MSDB folders gone"

sql

Can I use Custom UI for executing Package in C#?

Hi

I've a doubt. I don't know whether it is possible or not.

I've a very less knowledge in DTS packages.

When I execute a package in C#. it is displaying predefined GUI for showing process.

Can I've my own UI when executing DTS.

Thanks in advance.

Could you clarify what you mean by "When I execute a package in C#" and whether the question is about DTS 2000 or SSIS. This forum is about SSIS, BTW.|||

Thanks for the reply Michael

I'm using SSIS

I'm putting the C# code here

public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
Package package;
ConnectionManager ConMgr;

Package p;
private void Form1_Load(object sender, EventArgs e)
{
string pkg ="Package.dtsx";

Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
p = app.LoadPackage(pkg, null);

}

private void button1_Click(object sender, EventArgs e)
{
if(p!=null)

p.Execute();

}
}

When I click on Button ie) p.Execute(); is invoked I need my own UI to see the processing . Is it possible.

Thank u

|||Of course - you need to implement event sink interface (IDTSEvents) and/or logging sink interface (IDTSLogging), and call package.Execute(...) version that takes these interfaces (pass null for all other arguments). You'll get the information about the package progress and can show the UI similar (or completely different if you choose) to the one DtExecUI shows (DtExecUI uses the same interfaces).|||

Thanks michael

It is useful

and can I know abt DtExecUI for what it is useful for?.]

|||Well, it executes the packages :)

Some people use command line and some people like to use GUI to execute packages. We provide DTEXEC and DTEXECUI for each group appropriately.|||

Thank u Michael .

Thanks for ur support to know these.

And finally one , can u provide some resource to learn , how to create custom UI. and executing package.etc

|||

Hi

I've one more doubt.

Executing package is a single threaded or multi threaded?

Can I catch the names of the currently transfering table when Executing package?

Thanks in advance.

|||Execution is multithreaded, you'll need to gather information you need from the event and pass it to the UI thread, and probably call package.Execute() on a different thread too to avoid locking up the UI.

There are many events and log information, see Books Online for details. Creative use of this information joined with the package information (e.g. transform properties) may give you most of the information you need.

I don't have any sample, sorry.|||

Thanks alot Michael.

Can I set Excel connection manager's data source as a variable?

I do not know the Excel file name to load in design time.

Would like to pass the value to a variable in the package in run time?

How to do this?

Thanks,

Guangming

Property Expressions is the feature you want. You can assign and expression to most properties, including the DataSource property of a connection. The expression would just be the name of your variable, e.g.

@.[User:MyVar]

Some UIs expose expressions as a separate tab, or just expand expressions from the normal properties grid.

|||I set ExcelFilePath to a variable:

varExcelFileFullPath.

I set an initial value ( \\test\testShare\aaab.xls ) to it when designing the package. It works

Later I run it inside SQL server as below. It failed.

exec xp_cmdshell 'dtexec /SQL "\NE_LoadExcel_Test_direct" /SERVER ** /USER ***** /PASSWORD ****

/CONNECTION "DataSource-Excel";"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\test\testshare\aaab2.xls;Extended Properties=""EXCEL 8.0;HDR=YES"";"

/CONNECTION "Test.NGAEarth";"Data Source=Test;Initial Catalog=NGAEarth;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"

/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW /LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.NGAEarth"

/set "\Package.Variables[User::varPreSQLAction].Properties[Value]";"delete from Features where type = ''aaab''"

/set "\Package.Variables[User::varExcelWorkbookName].Properties[Value]";"Sheet1$"

/set "\Package.Variables[User::varDestinationTableName].Properties[Value]";"Features"

/set "\Package.Variables[User::varExcelFileFullPath].Properties[Value]";"\\test\testShare\aaab2.xls"

'

Actually, I tried to change other variables. E.g., I changed the file name from aaab2.xls to aaab.xls, Excel worksheet name from Sheet1$ to Sheet2$.

It seems the package never get the parameters I set as above. It always gets the values I set in design time. The errors I got are:

Error: 2006-02-01 15:54:23.58

Code: 0xC0202009

Source: Extract AdHoc Data from Excel Excel Source [649]

Description: An OLE DB error has occurred. Error code: 0x80040E37.

End Error

Error: 2006-02-01 15:54:23.58

Code: 0xC02020E8

Source: Extract AdHoc Data from Excel Excel Source [649]

Description: Opening a rowset for "Sheet1$" failed. Check that the object exists in the database.

End Error

Error: 2006-02-01 15:54:23.58

Code: 0xC004706B

Source: Extract AdHoc Data from Excel DTS.Pipeline

Description: "component "Excel Source" (649)" failed validation and returned validation status "VS_ISBROKEN".

End Error

|||

Finally I found the problem:

The way to put the command string is not right after dtexec /SQL.

If there is not ENTER and all commands in one line (as it is a dos command), everything is OK!

Is it COOL!?

Guangming

Can I republish a web site from within a DTS package

I have a DTS package that updates information in a database for one of our clients. This information then needs to be republished to the websites which is done by going to a URL

Example: http://webrepub.DomainName.com/apps/publish.cfm?temp=1&ID=1101.

Is there a way from within the DTS package to execute/call the URL that performs the republishing of the websites with out opening a browser on the server.

TIAWrite a script to do your thing and call that by an 'OS command' from the DTS. That's probably the quickest and most robust sollution.

Gr,
Yveau

Can I remove the ability of sa to create users?

HI, I know what I should do is jus change the password, but there are over
100 jobs and DTS package connections that run with sa auth, and that will
take a long time, unless someone knows how that can be automated to change
all sa users to something else, sorry that's another question. The issue I
have is that I came into a Job where security setting were left wide open,
and as I try to provide security, and tighten things down, I have users who
know the sa PW and can go in create a new local ID and set themselves as SA,
System Administrator. Although they should be delt with by admin, that's
another story... SO question is how can I disallow sa from creating
logins, AND how can I change all job, and DTS connections (anything that
uses sa creds) to something else? PLEASE... Thanks you.You basically can't deny anything to SA. Giving out the SA password is a
VERY bad thing to do. Your choices are to change it or fire everyone who
knows it.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:ueC1Pm5xGHA.2168@.TK2MSFTNGP06.phx.gbl...
> HI, I know what I should do is jus change the password, but there are
> over 100 jobs and DTS package connections that run with sa auth, and that
> will take a long time, unless someone knows how that can be automated to
> change all sa users to something else, sorry that's another question. The
> issue I have is that I came into a Job where security setting were left
> wide open, and as I try to provide security, and tighten things down, I
> have users who know the sa PW and can go in create a new local ID and set
> themselves as SA, System Administrator. Although they should be delt with
> by admin, that's another story... SO question is how can I disallow sa
> from creating logins, AND how can I change all job, and DTS connections
> (anything that uses sa creds) to something else? PLEASE... Thanks you.
>|||Thanks Roger, That is what I thought.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:uCJGrG8xGHA.4960@.TK2MSFTNGP05.phx.gbl...
> You basically can't deny anything to SA. Giving out the SA password is a
> VERY bad thing to do. Your choices are to change it or fire everyone who
> knows it.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "WANNABE" <breichenbach AT istate DOT com> wrote in message
> news:ueC1Pm5xGHA.2168@.TK2MSFTNGP06.phx.gbl...
>> HI, I know what I should do is jus change the password, but there are
>> over 100 jobs and DTS package connections that run with sa auth, and that
>> will take a long time, unless someone knows how that can be automated to
>> change all sa users to something else, sorry that's another question.
>> The issue I have is that I came into a Job where security setting were
>> left wide open, and as I try to provide security, and tighten things
>> down, I have users who know the sa PW and can go in create a new local ID
>> and set themselves as SA, System Administrator. Although they should be
>> delt with by admin, that's another story... SO question is how can I
>> disallow sa from creating logins, AND how can I change all job, and DTS
>> connections (anything that uses sa creds) to something else? PLEASE...
>> Thanks you.
>

Can I remove the ability of sa to create users?

HI, I know what I should do is jus change the password, but there are over
100 jobs and DTS package connections that run with sa auth, and that will
take a long time, unless someone knows how that can be automated to change
all sa users to something else, sorry that's another question. The issue I
have is that I came into a Job where security setting were left wide open,
and as I try to provide security, and tighten things down, I have users who
know the sa PW and can go in create a new local ID and set themselves as SA,
System Administrator. Although they should be delt with by admin, that's
another story... SO question is how can I disallow sa from creating
logins, AND how can I change all job, and DTS connections (anything that
uses sa creds) to something else? PLEASE... Thanks you.You basically can't deny anything to SA. Giving out the SA password is a
VERY bad thing to do. Your choices are to change it or fire everyone who
knows it.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"WANNABE" <breichenbach AT istate DOT com> wrote in message
news:ueC1Pm5xGHA.2168@.TK2MSFTNGP06.phx.gbl...
> HI, I know what I should do is jus change the password, but there are
> over 100 jobs and DTS package connections that run with sa auth, and that
> will take a long time, unless someone knows how that can be automated to
> change all sa users to something else, sorry that's another question. The
> issue I have is that I came into a Job where security setting were left
> wide open, and as I try to provide security, and tighten things down, I
> have users who know the sa PW and can go in create a new local ID and set
> themselves as SA, System Administrator. Although they should be delt with
> by admin, that's another story... SO question is how can I disallow sa
> from creating logins, AND how can I change all job, and DTS connections
> (anything that uses sa creds) to something else? PLEASE... Thanks you.
>|||Thanks Roger, That is what I thought.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:uCJGrG8xGHA.4960@.TK2MSFTNGP05.phx.gbl...
> You basically can't deny anything to SA. Giving out the SA password is a
> VERY bad thing to do. Your choices are to change it or fire everyone who
> knows it.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "WANNABE" <breichenbach AT istate DOT com> wrote in message
> news:ueC1Pm5xGHA.2168@.TK2MSFTNGP06.phx.gbl...
>

Can I read from multiple table at run time

Hi,

My Issue is that, I have to create a package which should read from multiple table from a RDBMS Source.

Right now I have different Source adapters for each table. I want to reduce the number of source adapters.

Can I

1) Create a connection manager and set the connection string from a variable. Set the variable from a script task. Put both of these into for loop container (no of tables).

Issues

a) Most of the table has same schema, but few have different, so all tables which has same schema will work.

For different schema anyway I have to create different data source.

b) Will Destination column mapping work.

If any one has already worked on it let me know.

Thanks

Dharmbir

Hi Dharmbir,

I think you are on the right track and I have succeeded with a similar requirement in the past.

Your variable SQL statement will need to return a consistent list of columns back to SSIS. Eg if your first iteration through the loop ran:

select col_a, col_b from table_1

... but your second iteration was just

select col_a from table_2

... this would fail. To avoid this, rejig your second sql statement along these lines:

select col_a, 'any string' as col_b from table_2

This technique will trick SSIS into thinking it is receiving the same answer set each time. Beware of datatype mismatches.

Good luck.

Mike

Can I read from multiple table at run time

Hi,

My Issue is that, I have to create a package which should read from multiple table from a RDBMS Source.

Right now I have different Source adapters for each table. I want to reduce the number of source adapters.

Can I

1) Create a connection manager and set the connection string from a variable. Set the variable from a script task. Put both of these into for loop container (no of tables).

Issues

a) Most of the table has same schema, but few have different, so all tables which has same schema will work.

For different schema anyway I have to create different data source.

b) Will Destination column mapping work.

If any one has already worked on it let me know.

Thanks

Dharmbir

Hi Dharmbir,

I think you are on the right track and I have succeeded with a similar requirement in the past.

Your variable SQL statement will need to return a consistent list of columns back to SSIS. Eg if your first iteration through the loop ran:

select col_a, col_b from table_1

... but your second iteration was just

select col_a from table_2

... this would fail. To avoid this, rejig your second sql statement along these lines:

select col_a, 'any string' as col_b from table_2

This technique will trick SSIS into thinking it is receiving the same answer set each time. Beware of datatype mismatches.

Good luck.

Mike

Can I make DTS..........

Can I make a DTS package that its destination place can be variable/input?
Because I want its DTS package can be called by each client to export
client's data. (client's data use Foxpro).
Or May I make Stored procedure to instead it? Give me solution? And give me
its listing code? or not, give me some refferences to be learned by me.Adi,
Yes it's possible. Here is the procedure
1) Create two global variables in your DTS package one each for
destination server and destination database (assuming your destination
is SQL Server) say strServer & strDB
2) Have dynamic propeties task object positioned at the begining in
your package (u need to have this before destination connection in
order) , this task can be used to assign values present in the global
variables to various DTS objects , in your case it will be destination
connection object.
Check BOL or www.sqldts.com for reference on dynamic properties task or
global variables.
3)Now, we can pass different values to these global variables during
run time using SQLAgent Job.Create separate job for each client, you
can call same DTS package thru different jobs and pass different values
for global variables.
eg:dtsrun /E /N"<packageName>" /S<Server Name> /A "<Global Variable
name eg: strServerName>":"8"="<Actual Server Name>" /A "<Global
Variable name eg: dbName>":"8"="<Actual dbName>"
check for dtsrun utility in SQL Server BOL or sqldts.com

Can I load only specific rows of data?

Is there a way to load only specific rows of data into a table in a SSIS package?

I am copying data from 2 separate ingres databases tables that have the same table structure into one SQLServer table using the ADO.NET DataReader Source for ODBC.

Let's say table1 from db1 and table2 from db2 for example.

table1 in db1 contains two rows:

MemberID - 1
Name - Rob

MemberID - 2
Name - James

table2 in db2 contains one row:

MemberID - 1
Name - Rob
MemberID - 2
Name - JAMES

I would like my SSIS job to load all data from table1 in db1 and then only load the data from table2 in db2 where the data in the row is different (as in 'JAMES').

Is there a data flow transformation that can help me do this?

I previously did this in 2000 DTS but had to load the data into 2 separate 'holding' tables and use T-SQL to update the table1 in db1 and was hoping I cuold cut out these steps in SSIS.

I was hoping that SSIS might have some new quick way to do this (lookups maybe?)

Can you help? Thank You

You can use the Lookup transform or the Merge Join transform. T-SQL is still a valid option if it works best for you.

Jamie did a nice comparison here-

Get all from Table A that isn't in Table B
(http://www.sqlis.com/default.aspx?311)|||Thank you

I shall have a read of the article and give it a try