Changing connections (Does't work with "Script Task"...)

Hi,

I've a simple package which basically copies a few record from a Source connection to a Destination connection.
This package must run ten times, every time with a different Source and Destination.

I dont want to duplicate the tasks inside the package and set the right connections at design time. I'm looking for a way to parametrize the connections.

In DTS2000 I could use an "Activex Script" to access the Package's Connections and change them at design time. The new "Script Task" does not allow access to others task, so it can't be done anymore. (There's is still the "Activex Script" task on my toolbox, but i get errors trying to place it on the design surface).

Package Configurations also does not seems the right way: to use multiple configurations I should invoke the package from the command line multiple times with different command line parameters, while I wuold like to have everything in one place (ie 1 package file instead of 1 batch file, 1 package file and 10 package configurations files).

Regards,
CorradoYou are no longer allowed to access task and package properties at runtime and change things. Part of the rational is that it makes for potentially unstable and unpredicatble packages. So you know have a structured way of doing this through configurations and expressions.

Expressions can be set on connection, task and some component properties. Most UI's expose connections, or they are also available in the VS properties grid.

For example you could use an Exec SQL Task to get a recordset of connection information. You could then use the For Each Loop container to crack this, and for each iteration assign the value(s) to a variable. You can then use the variable(s) within an expression on the connection property(s).

Think expressions instead of ActiveX Script Tasks for glue code.

Changing connection transactions to database transactions

Hi there,

I have decided to move all my transaction handling from asp.net to stored procedures in a SQL Server 2000 database. I know the database is capable of rolling back the transactions just likemyTransaction.Rollback() in asp.net.
But what about exceptions? In asp.net, I am used to doing the following:

<code>
Try
'execute commands
myTransaction.Commit()
Catch ex As Exception
Response.Write(ex.Message)
myTransaction.Rollback()
End Try
</code>
Will the database inform me of any exceptions (and their messages)? Do I need to put anything explicit in my stored procedure other thanrollback transaction?

Any help is greatly appreciated

Try these links for most of your needs, the most important is Transaction Save Point, it keeps your transactions from rolling back to the beginning if there is a roll back. If you need to use triggers look into Instead Of trigger it allows you to put your transactions within the trigger. Run a search for Transaction Save Points and Instead Of triggers in the BOL(books online) Hope this helps.

http://www.codeproject.com/database/sqlservertransactions.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_1ub2.asp

|||

Thanks Caddre,

I checked those links and finally figured it out. I have taken the approach to code my own error messages and put them in an output parameter.

One more thing, is there a way to simulate errors during a transaction? At the client side, I could explicitly throw an exception that would be caught. I don't know if this is possible in stored procedures... just want to test the error handling in my stored procedure. Anyone an idea?

|||Transaction stored procs are in a separate class, you either commit or roll back, that is the reason Savepoint is very important, if there is no Savepoint you can roll back to number one from number nineteen in a twenty transaction stored proc. You could test your Savepoints. Run a search on transaction error handling in the BOL(books online). Hope this helps.

Changing connection string to data source

Can I change databases for a SqlDataSource in code behind and still use the edit,inset,delete capabilities of the SQLDataSource control?

yes u can..

for change of connection string

SqlDataSource1.ConnectionString =ConfigurationManager.ConnectionStrings[0].ToString();

or

SqlDataSource1.ConnectionString ="UR connection string";

Changing Connection String programmatically

How can I change the Connection String programmatically?
(WS2005, Windows forms, C#)
I need to change the server name, or Initial Catalog parts of the string.
My connection strings are in a field of a master SQL database table.
The design of the different databases are the same.
How can I pass it to the _connection in the dataset?
_connection and InitConnection() are private.
I know, that I can edit the MyDbDataSet.Designer.cs, but I don't
belive, that it is good solution.
private void InitConnection() {
this._connection = new System.Data.SqlClient.SqlConnection();
this._connection.ConnectionString = "Data
Source=SQL\\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security="
+
"True";
}
If the Data Source Confoguration Wizard saves the connection string,
the Settings.Designer.cs has also private properties I don't want edit.
Thanks - ZsoltSince if the SQL Server holding your connection strings is renamed or
changed, and you will not be able to get to the connection strings, that
seems like a bad idea.
Why not save the connection string(s) in the application.config file?
If you are storing connnection string information related to additional
servers, and you will use a confirmed static connection to retreive the
stored strings, just put them in a table. You may wish to have different
fields for server, database, security context, etc.. Create a stored
procedure that will provide you the concatenated string when you pass it a
parameter.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Zsolt" <Zsolt@.nowhere.nospam> wrote in message
news:1173DEA0-9696-4C12-8F58-ACA1926D85D8@.microsoft.com...
> How can I change the Connection String programmatically?
> (WS2005, Windows forms, C#)
> I need to change the server name, or Initial Catalog parts of the string.
> My connection strings are in a field of a master SQL database table.
> The design of the different databases are the same.
> How can I pass it to the _connection in the dataset?
> _connection and InitConnection() are private.
> I know, that I can edit the MyDbDataSet.Designer.cs, but I don't
> belive, that it is good solution.
> private void InitConnection() {
> this._connection = new System.Data.SqlClient.SqlConnection();
> this._connection.ConnectionString = "Data
> Source=SQL\\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated
> Security=" +
> "True";
> }
> If the Data Source Confoguration Wizard saves the connection string,
> the Settings.Designer.cs has also private properties I don't want edit.
> Thanks - Zsolt
>|||Thank you for your answer, Arnie!
The reason for using different databases of the same design is that
every year we will create new database. After creating the new years
database, we will register it in the master database, entering the
necessary connection data in a table. DataSet should use any years
database, but only one at a time.
My problem is that how can I pass it to the DataSet, because it is
private. Of course I can edit the MyDataSet.Designer.cs file,
change the body of the private void InitConnection(), and it works:
private void InitConnection() {
this._connection = new System.Data.SqlClient.SqlConnection();
// insted of:
// this._connection.ConnectionString = "Data Source=SQL\\SQLEXPRESS;Initial
Catalog=Data2006;Integrated Security=True";
// This:
this._connection.ConnectionString = MyConnStrinProvider.GetConnString();
}
But whenever I make some changes on .xsd, the changes above are lost.
Changing of an <auto-generated> file seems not good.
I think there must be better solution then this.
Thank you again,
Zsolt
"Arnie Rowland" wrote:

> Since if the SQL Server holding your connection strings is renamed or
> changed, and you will not be able to get to the connection strings, that
> seems like a bad idea.
> Why not save the connection string(s) in the application.config file?
> If you are storing connnection string information related to additional
> servers, and you will use a confirmed static connection to retreive the
> stored strings, just put them in a table. You may wish to have different
> fields for server, database, security context, etc.. Create a stored
> procedure that will provide you the concatenated string when you pass it a
> parameter.
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Zsolt" <Zsolt@.nowhere.nospam> wrote in message
> news:1173DEA0-9696-4C12-8F58-ACA1926D85D8@.microsoft.com...
>
>|||This is a good situation for putting the connection string information in th
e app.config file. You will not have to recompile and redeploy the applicati
on each year, only the app.config file.
Add an Application.config file to the solution, and then add the appSettings
section. You can add as many 'keys' as you wish. They are just name=value p
airs that the application can retrieve.
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="ConnectionString" value="DATA Source=ServerName;initial catalog=My
Database;integrated security=True" />
</appSettings>
</configuration>
Then in the application use:
for VB: Dim con As SqlConnection = New SqlConnection(System.Configuration.Co
nfigurationSettings.AppSettings("ConnectionString"))
for C#: SqlConnection con = New SqlConnection(System.Configuration.Configura
tionSettings.AppSettings("ConnectionString"))
You may wish to check further in VS Help about using an app.config file.
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Zsolt" <Zsolt@.nowhere.nospam> wrote in message news:DD87AECE-A6FF-4C8B-ABA6-B5B80B89D9A6@.mi
crosoft.com...[vbcol=seagreen]
> Thank you for your answer, Arnie!
>
> The reason for using different databases of the same design is that
> every year we will create new database. After creating the new years
> database, we will register it in the master database, entering the
> necessary connection data in a table. DataSet should use any years
> database, but only one at a time.
>
> My problem is that how can I pass it to the DataSet, because it is
> private. Of course I can edit the MyDataSet.Designer.cs file,
> change the body of the private void InitConnection(), and it works:
>
> private void InitConnection() {
> this._connection = new System.Data.SqlClient.SqlConnection();
> // insted of:
> // this._connection.ConnectionString = "Data Source=SQL\\SQLEXPRESS;Initi
al
> Catalog=Data2006;Integrated Security=True";
> // This:
> this._connection.ConnectionString = MyConnStrinProvider.GetConnString()
;
> }
>
> But whenever I make some changes on .xsd, the changes above are lost.
> Changing of an <auto-generated> file seems not good.
>
> I think there must be better solution then this.
>
> Thank you again,
> Zsolt
>
>
> "Arnie Rowland" wrote:
>|||Connection strings of different years (databases) can be stored in app.confi
g
file (among other places). User can select one, using a ComboBox. Ok.
Using the selected connect string, SqlConnection and SqlCommand can be
constructed, OK!
But DataTableAdapter's System.Data.SqlClient.SqlDataAdapter _adapter member
is declared in MyDataset.Designer.cs as private, so I can't access it. That
is my original problem.
Of course, I can edit the MyDataset.Designer.cs file, it compiles and works
well,
but it is not a clear method, and not recommended at all.
Any changes in MyDataset.xsd file overwrites my editing (program is under
construction).
That's why I am looking for better and clearer solution.
Thank you again,
--
Zsolt
"Arnie Rowland" wrote:
[vbcol=seagreen]
> This is a good situation for putting the connection string information in
the app.config file. You will not have to recompile and redeploy the applica
tion each year, only the app.config file.
> Add an Application.config file to the solution, and then add the appSettin
gs section. You can add as many 'keys' as you wish. They are just name=value
pairs that the application can retrieve.
> <?xml version="1.0" encoding="utf-8" ?>
> <configuration>
> <appSettings>
> <add key="ConnectionString" value="DATA Source=ServerName;initial ca
talog=MyDatabase;integrated security=True" />
> </appSettings>
> </configuration>
> Then in the application use:
> for VB: Dim con As SqlConnection = New SqlConnection(System.Configuration.
ConfigurationSettings.AppSettings("ConnectionString"))
> for C#: SqlConnection con = New SqlConnection(System.Configuration.Configu
rationSettings.AppSettings("ConnectionString"))
> You may wish to check further in VS Help about using an app.config file.
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Zsolt" <Zsolt@.nowhere.nospam> wrote in message news:DD87AECE-A6FF-4C8B-AB
A6-B5B80B89D9A6@.microsoft.com...

Changing Connection string in multiple packages.

Scenario:

130 dtsx packages using 4 matching connections.
3 of those connections are stored in an SSIS Configuration table in an Operational database.
The last connection is in a shared data source and points to the Operational database so the packages can grab the others.

Problem:

It's time for deployment and all of those connections must change to production servers.
The 3 are no issue, just change the ConfiguredValue in the SSIS Configuration table on the production box to point to the other production servers.
However, the fourth one... I had made an assumption that when you changed a shared data source it filtered down throughout all the packages. We all know what assumptions do to you.... So. I need a way to change all 130 connections (and be able to change ALL packages quickly and simply for other projects in the future)

Solution:

It has been suggested that we use another package to run though all of the packages and change the connection with a script task. I can live with this (and more importantly so can our DBS's who have to deploy).

I have one snippet of code to ADD a connection using a variable holding the connection string, but we dont' want to add one, just change an existing one.

Has anyone else done this? Or had a similar problem and way to fix?

We are likely to have many projects in which the connections MUST change at deployment, and the idea of going into every package to make the change is sad at best... We would be more likely to move back to a competitor's product that has a connection repository, than continue with SSIS.

Ches Weldishofer
ETL Developer
Clear Channel Communications

Ches Weldishofer wrote:

We are likely to have many projects in which the connections MUST change at deployment, and the idea of going into every package to make the change is sad at best... We would be more likely to move back to a competitor's product that has a connection repository, than continue with SSIS.

Alternatively you could use SSIS's built-in mechanism for doing this. i.e. Configurations.

You can share an XML configuration file between many packages. If your connection managers are identically named in each package then there won't be a problem.

-Jamie

|||

Yes we've discussed using an XML Configuration, however... it causes it's own issues. Our production servers are clustered so anything having to point to a file would have to be set up on many many servers.

And the same path would have to exist on all development servers, all developers workstations and all production servers. Maintenance nightmare.

It may be the way we have to go, but... not really a path we want to go down.

|||

Interesting. Are you clustering your servers that run the ETL jobs? Why can you not run all jobs on a single server? That's not rhetoric, I'm interested to know.

-Jamie

|||

Yes they are :)

Both the actual data servers and the SSIS server are clustered (and seperate). The plan is to have all packages run from one cluster so they're easier to find (compared to spread out over 40-50 servers as our dts packages are now) and easier to manage.

We've discussed using a cluster file share, but again we would have to be able to make that work on so many different servers, every developer every development server... oi.

In theory this is all great, but in practice we were hit with the changing of connections.

|||

Right. Any reason why your ETL machine can't be non-clustered but still keep your data servers as clustered? What's the advantage of clustering your ETL server?

Forgive me if I ask daft questions - I don't know much about clustering.

-Jamie

|||Failover, if the SSIS server goes down, then the second one takes over and continues to run the jobs as scheduled.|||

OK gotcha.

I assume you've read this post on clustering: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/07/06/16015.aspx

You know that packages are not dependant on the SSIS service in order to run right?

-Jamie

|||

Nope I wouldn't have read it. :) That's an ops thing. But I'll pass it on to our DBA's. I have to code to the environment I,m given.

One thing about the failover is it works even if the entire server is down, not just the Service. Say they want to put SP1 on the ssis box, this way the put failover to the second box, upgrade the first, it then falls back on the first box and they can update the second (including reboots) and we never lose a step.

|||

Which all leads us back to my original problem.

What I'm looking for is the code/syntax to change a connection in an existing package through another package to use as a deployment utility for chaning dev connections to production ones.

Any pointers as to where to look for the syntax would be greatly appreciated.

|||

This might help: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/12/31/17731.aspx

-Jamie

|||

Ches,

I have some script tasks in various packages that change connection manager databases on the fly from within a package. Here is the code:

Dim oCon As ConnectionManager
Dim oProp As DtsProperty
oCon = Dts.Connections("ConnectionManagerX")
oProp = oCon.Properties("InitialCatalog")
oProp.SetValue(oCon, Dts.Variables("DatabaseNameVariable").Value.ToString)
Dts.TaskResult = Dts.Results.Success

Obviously, this runs from within the target package and is pulling the database name from a package variable, which isn't quite what you're looking for.

I think you'd be going for something like this:

Const c_sTargetPkg As String = "This is my package"
Const c_sTargetServer As String = "Server1"
Const c_sTargetConnection As String = "Connection A"
Dim oApp As Application = New Application()
Dim oPkg As Package
Dim oCon As ConnectionManager
Dim oProp As DtsProperty

'Get administrative values from package variables
Dim sConnectionName As String = Dts.Variables("TargetConnection").Value.ToString
Dim sNewServerName As String = Dts.Variables("NewServer").Value.ToString
Dim sNewDatabaseName As String = Dts.Variables("NewDatabase").Value.ToString

'Get a handle on the target package and connection manager
oPkg = oApp.LoadFromSqlServer(c_sTargetPkg, c_sTargetServer, "", "", Nothing)
oCon = oPkg.Connections(sConnectionName)

'Change the server name
oProp = oCon.Properties("ServerName")
oProp.SetValue(oCon, sNewServerName)

'Change the database name
oProp = oCon.Properties("InitialCatalog")
oProp.SetValue(oCon, sNewDatabaseName)

'Save the package
oApp.SaveToSqlServer(oPkg, Nothing, c_sTargetServer, Nothing, Nothing)

It uses some constants and some package variables to load up a target package, adjust a particular connection manager's properties and then saves the package back to the target server. I'm sure you could add all kinds of looping or target package lists to this code to have it handle all packages within one run.

Mike Ogilvie
Pendulum, Inc.
http://www.PendulumSolutions.com

|||

Thank you Mike that's much closer to what I'm looking for, I'll start playing with that and repost when I get a final answer. (or more questions which seems likely with SSIS :)

|||I wrote my own vb app to maintain my connection strings in a data table in sql. I have all of my packages querying it and loading the connection strings to variables in memory and Iset them as an expression for the connection strings that I need.|||

Here's the code I finally used, it's inside a dataset enumerated loop to change which ever packages exist in the msdb root by name supplied in a variable (or % for all).

Worked nicely, the dba is much happier with me now that he doesnt' have to change 130 connections.

Dim c_sTargetPkg As String = Dts.Variables("DTSXName").Value.ToString
Const c_sTargetServer As String = "servername"
Const c_sTargetConnection As String = "ConnectionName"
Dim oApp As Application = New Application()
Dim oPKG As Package
Dim oCon As ConnectionManager
Dim oProp As DtsProperty

'Get administratvive values from package variables
Dim sConnectionName As String = Dts.Variables("ConnectionName").Value.ToString
Dim sNewServerName As String = Dts.Variables("NewServerName").Value.ToString
Dim sNewConnectionString As String = Dts.Variables("NewConString").Value.ToString

'Get package and connection manager
oPKG = oApp.LoadFromSqlServer(c_sTargetPkg, c_sTargetServer, "login", "password", Nothing)
oCon = oPKG.Connections(sConnectionName)

'Change Server name
oProp = oCon.Properties("ServerName")
oProp.SetValue(oCon, sNewServerName)
oProp = oCon.Properties("ConnectionString")
oProp.SetValue(oCon, sNewConnectionString)

'Save the packages
oApp.SaveToSqlServer(oPKG, Nothing, c_sTargetServer, "login", "password")

Dts.TaskResult = Dts.Results.Success

sql

changing connection string from one sql server to another using vb .net

I have developed numerous reports in a vb .net app (Windows Database App)
that is
soon to go into production. When I built each report, CR connected to sql
server on my development platform. The connection string is different than
it will be at my client.
How can I change the connection string so that it recognizes my client's
server instead of mine for each of these reports? This is not technically a
sql server question, but I am hoping some of the sql developers who review
these posts will have run into the same issue,
Thanks for any help.
Bernie YaegerConnection String data is generally stored in a centralized location...
1) config File
2) Registry
3) INI
4) Global.ASAX
hope this helps
Greg J
PDX, Oregon

changing connection string from one sql server to another using vb .net

I have developed numerous reports in a vb .net app (Windows Database App)
that is
soon to go into production. When I built each report, CR connected to sql
server on my development platform. The connection string is different than
it will be at my client.
How can I change the connection string so that it recognizes my client's
server instead of mine for each of these reports? This is not technically a
sql server question, but I am hoping some of the sql developers who review
these posts will have run into the same issue,
Thanks for any help.
Bernie YaegerConnection String data is generally stored in a centralized location...
1) config File
2) Registry
3) INI
4) Global.ASAX
hope this helps
Greg J
PDX, Oregon

Changing connection credentials

Hi,
I would like to change the credentials that are used at run time by the
reportserver service to connect to the SQL Server instance.
Can anybody please help me?
Regards!!Cookie =EB=FA=E1:
> Hi,
> I would like to change the credentials that are used at run time by the
> reportserver service to connect to the SQL Server instance.
> Can anybody please help me?
> Regards!!
http://support.microsoft.com/?id=3D306158
Impersonate a Specific User in Code
To impersonate a specific user only when you run a particular section
of code, use the following code:
<%@. Page Language=3D"C#"%>
<%@. Import Namespace =3D "System.Web" %>
<%@. Import Namespace =3D "System.Web.Security" %>
<%@. Import Namespace =3D "System.Security.Principal" %>
<%@. Import Namespace =3D "System.Runtime.InteropServices" %>
<script runat=3Dserver>
public const int LOGON32_LOGON_INTERACTIVE =3D 2;
public const int LOGON32_PROVIDER_DEFAULT =3D 0;
WindowsImpersonationContext impersonationContext;
[DllImport("advapi32.dll")]
public static extern int LogonUserA(String lpszUserName,
String lpszDomain,
String lpszPassword,
int dwLogonType,
int dwLogonProvider,
ref IntPtr phToken);
[DllImport("advapi32.dll", CharSet=3DCharSet.Auto, SetLastError=3Dtrue)]
public static extern int DuplicateToken(IntPtr hToken,
int impersonationLevel,
ref IntPtr hNewToken);
[DllImport("advapi32.dll", CharSet=3DCharSet.Auto, SetLastError=3Dtrue)]
public static extern bool RevertToSelf();
[DllImport("kernel32.dll", CharSet=3DCharSet.Auto)]
public static extern bool CloseHandle(IntPtr handle);
public void Page_Load(Object s, EventArgs e)
{
if(impersonateValidUser("username", "domain", "password"))
{
//Insert your code that runs under the security context of a specific
user here.
undoImpersonation();
}
else
{
//Your impersonation failed. Therefore, include a fail-safe mechanism
here.
}
}
private bool impersonateValidUser(String userName, String domain,
String password)
{
WindowsIdentity tempWindowsIdentity;
IntPtr token =3D IntPtr.Zero;
IntPtr tokenDuplicate =3D IntPtr.Zero;
if(RevertToSelf())
{
if(LogonUserA(userName, domain, password, LOGON32_LOGON_INTERACTIVE,
LOGON32_PROVIDER_DEFAULT, ref token) !=3D 0)
{
if(DuplicateToken(token, 2, ref tokenDuplicate) !=3D 0)
{
tempWindowsIdentity =3D new WindowsIdentity(tokenDuplicate);
impersonationContext =3D tempWindowsIdentity.Impersonate();
if (impersonationContext !=3D null)
{
CloseHandle(token);
CloseHandle(tokenDuplicate);
return true;
}
}
}
}
if(token!=3D IntPtr.Zero)
CloseHandle(token);
if(tokenDuplicate!=3DIntPtr.Zero)
CloseHandle(tokenDuplicate);
return false;
}
private void undoImpersonation()
{
impersonationContext.Undo();
}
</script>

Changing a Connection in DTS Local packages

Many of our Local packages in Data Transformation Services has the job of
copying data from one database on one server to another database on another
server. The package typically has 2 Connections defined.
Now we want to move the "destination" database to a new server. Is there any
way this can be done without editing each package in the "Design Package"
GUI
Thanks in advance
Henrik.You can use UDL files for the connection; you can change the properties
programmatically; you can use the Dynamic Properties task to change settings
at runtime.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Henrik Hjøllund Hansen" <hh@.dlf.dk> wrote in message
news:bicqd4$2hmp$1@.news.cybercity.dk...
> Many of our Local packages in Data Transformation Services has the job of
> copying data from one database on one server to another database on
another
> server. The package typically has 2 Connections defined.
> Now we want to move the "destination" database to a new server. Is there
any
> way this can be done without editing each package in the "Design Package"
> GUI
>
> Thanks in advance
>
> Henrik.
>
>

changing "header rows to skip" property in flat file connection during runtime

Hi all

I have a flat file.I am trying to set the value for the property "HeaderRowsToSkip" during runtime.I have set an expression for this in my "flat file connection manager". But this is not working.The connection manager is not able to take the value during runtime.

My expression is as follows:

DataRowsToSkip : @.[user:: Var]

where "Var" is my variable which gets the value from the rowcount component and trying to set it back to the "HeaderRowsToskip" property.

I ve even tried setting the value to the "HeaderRowsToSkip" property in the expression builder.

Its not working....

Can anyone help me out in solving this?

Thanks in advance

Regards

Suganya

Is the rowCont component part of the same data flow where your trying to write into the flat file? If so; there might be the problem (no 100% sure); but I am guessing that by the time the flat file connection manager gets 'configured' the rowcount variable has not been populated.

Try moving the logic of the row count to a different dataflow at the beginning of the control flow and see if that helps...

|||

I tried the option which you have mentioned.Its not working...Is there any other way to achieve this?

And what is meant by "flat file connection manager getting configured?".Does it mean that when i start running my package,It sets all the values and during runtime i cant change my connection manager details?.I get this doubt because the connection manager is able to skip the number of rows with the same expression being used and setting the value for the variable which am using in the expression during design time.

Thanks in advance.

Regards,

Suganya

|||

I've just had a go at this and it works fine.

Where is the rowcount component? Is it in the same data-flow?

-Jamie

|||

No...its not in the same dataflow task.As you said in the previous post i have changed it another data flow task.

My package has the following tasks and components

1.Dataflow task(1) containing a flatfile ,a rowcount and an OLE DB destination

2.Dataflow task(2) containing a flat file and an OLE DB destination

Only for the flat file available in the data flow task(2),I have set the expression and am not using the same connection manager which i ve used in the dataflow task(1).

am i doing anything wrong?

Regards,

Suganya

|||

All except one thing. Why have you got a an OLE DB Destination in the first data-flow? Where are you inserting the data to? You can terminate the data-flow with the rowcount compoennt - that seems to be all you need.

-Jamie

|||

Suganya Mahadevan wrote:

I tried the option which you have mentioned.Its not working...Is there any other way to achieve this?

And what is meant by "flat file connection manager getting configured?".Does it mean that when i start running my package,It sets all the values and during runtime i cant change my connection manager details?.

Yes; and the behavior you described proof that. The connection manager gets set up using the available values when the execution starts; at that point he only value available for the rowcount variable is the one given at design time; as no rows have started to flow. That is why I was suggesting moving the rowcount logic to a previous stage.

But I got a question for you; is the row count variable being populated based on the same file you are trying to skip the rows from? That would not cause to skip all the rows every time? (Unless you are using different files)

|||

Its not working.I have removed the OLE DB destination from my dataflow task(1) and now trying to get the rowcount value from dataflow task(1) and set the value to the flat file connection manager's "HeaderRowsToSkip" in dataflow task(2).

My flatfile in dataflow task(1) has 100 records and my flatfile in dataflow task has 5000 records.My rowcount variable in dataflow task(1) has 100 as its value now(as i have 100 records in flatfile).So my flatfile in dataflow task(2) should skip first 100 rows and insert 4900 rows into the destination.But its not working that way.Its inserting all the 5000 records into the destination.

But the same scenario is working when i set the value during design time and i have specified this in my previous post.

|||

No its 2 different files.

My package has the following tasks and components

1.Dataflow task(1) containing a flatfile ,a rowcount and an OLE DB destination

2.Dataflow task(2) containing a flat file and an OLE DB destination

Only for the flat file available in the data flow task(2),I have set the expression and am not using the same connection manager which i ve used in the dataflow task(1).

My flatfile in dataflow task(1) has 100 records and my flatfile in dataflow task has 5000 records.My rowcount variable in dataflow task(1) has 100 as its value now(as i have 100 records in flatfile).So my flatfile in dataflow task(2) should skip first 100 rows and insert 4900 rows into the destination.But its not working that way.Its inserting all the 5000 records into the destination.

What is the problem with my package?

|||

Suganya Mahadevan wrote:

Its not working.I have removed the OLE DB destination from my dataflow task(1) and now trying to get the rowcount value from dataflow task(1) and set the value to the flat file connection manager's "HeaderRowsToSkip" in dataflow task(2).

My flatfile in dataflow task(1) has 100 records and my flatfile in dataflow task has 5000 records.My rowcount variable in dataflow task(1) has 100 as its value now(as i have 100 records in flatfile).So my flatfile in dataflow task(2) should skip first 100 rows and insert 4900 rows into the destination.But its not working that way.Its inserting all the 5000 records into the destination.

But the same scenario is working when i set the value during design time and i have specified this in my previous post.

Could you set a break point in the second dataflow and then execute the package; when it breaks; could you check the value of the rowcount variable to see if it's properly populated?

Let's make sure first the variable is correctly populated...

|||

Yes.Am able to see the value of the variable getting changed.I used script task to populate the value of the variable.Its getting set properly.(I am not doing any manipulation inside the script task except trying to populate the value in msg box.)

By the way,I tried enabling the breakpoint on "Variable Value Changed Event". It was not hit actually.So i tried using the script task for seeing my variable value.

Did i set the breakpoint correctly...or should i enable the breakpoint for some other event to see the value?

|||

Suganya Mahadevan wrote:

Yes.Am able to see the value of the variable getting changed.I used script task to populate the value of the variable.Its getting set properly.(I am not doing any manipulation inside the script task except trying to populate the value in msg box.)

By the way,I tried enabling the breakpoint on "Variable Value Changed Event". It was not hit actually.So i tried using the script task for seeing my variable value.

Did i set the breakpoint correctly...or should i enable the breakpoint for some other event to see the value?

Put the breakpoint on the OnPreExecute event of the second data-flow.

When it breaks, drag the variable into a Watch window and you will be able to see the value.

-Jamie

|||

Thanks Jamie.The value is getting set.

But flat file is not skipping the number of rows.

Any solution for this?

Regards

Suganya

|||

Can you share the package and the source files? Paste them up here if so.

-Jamie

|||

It is not possible for me to share the package here.Can u give ur mail id so that i could send my package and source code?

-Suganya

changing "header rows to skip" property in flat file connection during runtime

Hi all

I have a flat file.I am trying to set the value for the property "HeaderRowsToSkip" during runtime.I have set an expression for this in my "flat file connection manager". But this is not working.The connection manager is not able to take the value during runtime.

My expression is as follows:

DataRowsToSkip : @.[user:: Var]

where "Var" is my variable which gets the value from the rowcount component and trying to set it back to the "HeaderRowsToskip" property.

I ve even tried setting the value to the "HeaderRowsToSkip" property in the expression builder.

Its not working....

Can anyone help me out in solving this?

Thanks in advance

Regards

Suganya

Is the rowCont component part of the same data flow where your trying to write into the flat file? If so; there might be the problem (no 100% sure); but I am guessing that by the time the flat file connection manager gets 'configured' the rowcount variable has not been populated.

Try moving the logic of the row count to a different dataflow at the beginning of the control flow and see if that helps...

|||

I tried the option which you have mentioned.Its not working...Is there any other way to achieve this?

And what is meant by "flat file connection manager getting configured?".Does it mean that when i start running my package,It sets all the values and during runtime i cant change my connection manager details?.I get this doubt because the connection manager is able to skip the number of rows with the same expression being used and setting the value for the variable which am using in the expression during design time.

Thanks in advance.

Regards,

Suganya

|||

I've just had a go at this and it works fine.

Where is the rowcount component? Is it in the same data-flow?

-Jamie

|||

No...its not in the same dataflow task.As you said in the previous post i have changed it another data flow task.

My package has the following tasks and components

1.Dataflow task(1) containing a flatfile ,a rowcount and an OLE DB destination

2.Dataflow task(2) containing a flat file and an OLE DB destination

Only for the flat file available in the data flow task(2),I have set the expression and am not using the same connection manager which i ve used in the dataflow task(1).

am i doing anything wrong?

Regards,

Suganya

|||

All except one thing. Why have you got a an OLE DB Destination in the first data-flow? Where are you inserting the data to? You can terminate the data-flow with the rowcount compoennt - that seems to be all you need.

-Jamie

|||

Suganya Mahadevan wrote:

I tried the option which you have mentioned.Its not working...Is there any other way to achieve this?

And what is meant by "flat file connection manager getting configured?".Does it mean that when i start running my package,It sets all the values and during runtime i cant change my connection manager details?.

Yes; and the behavior you described proof that. The connection manager gets set up using the available values when the execution starts; at that point he only value available for the rowcount variable is the one given at design time; as no rows have started to flow. That is why I was suggesting moving the rowcount logic to a previous stage.

But I got a question for you; is the row count variable being populated based on the same file you are trying to skip the rows from? That would not cause to skip all the rows every time? (Unless you are using different files)

|||

Its not working.I have removed the OLE DB destination from my dataflow task(1) and now trying to get the rowcount value from dataflow task(1) and set the value to the flat file connection manager's "HeaderRowsToSkip" in dataflow task(2).

My flatfile in dataflow task(1) has 100 records and my flatfile in dataflow task has 5000 records.My rowcount variable in dataflow task(1) has 100 as its value now(as i have 100 records in flatfile).So my flatfile in dataflow task(2) should skip first 100 rows and insert 4900 rows into the destination.But its not working that way.Its inserting all the 5000 records into the destination.

But the same scenario is working when i set the value during design time and i have specified this in my previous post.

|||

No its 2 different files.

My package has the following tasks and components

1.Dataflow task(1) containing a flatfile ,a rowcount and an OLE DB destination

2.Dataflow task(2) containing a flat file and an OLE DB destination

Only for the flat file available in the data flow task(2),I have set the expression and am not using the same connection manager which i ve used in the dataflow task(1).

My flatfile in dataflow task(1) has 100 records and my flatfile in dataflow task has 5000 records.My rowcount variable in dataflow task(1) has 100 as its value now(as i have 100 records in flatfile).So my flatfile in dataflow task(2) should skip first 100 rows and insert 4900 rows into the destination.But its not working that way.Its inserting all the 5000 records into the destination.

What is the problem with my package?

|||

Suganya Mahadevan wrote:

Its not working.I have removed the OLE DB destination from my dataflow task(1) and now trying to get the rowcount value from dataflow task(1) and set the value to the flat file connection manager's "HeaderRowsToSkip" in dataflow task(2).

My flatfile in dataflow task(1) has 100 records and my flatfile in dataflow task has 5000 records.My rowcount variable in dataflow task(1) has 100 as its value now(as i have 100 records in flatfile).So my flatfile in dataflow task(2) should skip first 100 rows and insert 4900 rows into the destination.But its not working that way.Its inserting all the 5000 records into the destination.

But the same scenario is working when i set the value during design time and i have specified this in my previous post.

Could you set a break point in the second dataflow and then execute the package; when it breaks; could you check the value of the rowcount variable to see if it's properly populated?

Let's make sure first the variable is correctly populated...

|||

Yes.Am able to see the value of the variable getting changed.I used script task to populate the value of the variable.Its getting set properly.(I am not doing any manipulation inside the script task except trying to populate the value in msg box.)

By the way,I tried enabling the breakpoint on "Variable Value Changed Event". It was not hit actually.So i tried using the script task for seeing my variable value.

Did i set the breakpoint correctly...or should i enable the breakpoint for some other event to see the value?

|||

Suganya Mahadevan wrote:

Yes.Am able to see the value of the variable getting changed.I used script task to populate the value of the variable.Its getting set properly.(I am not doing any manipulation inside the script task except trying to populate the value in msg box.)

By the way,I tried enabling the breakpoint on "Variable Value Changed Event". It was not hit actually.So i tried using the script task for seeing my variable value.

Did i set the breakpoint correctly...or should i enable the breakpoint for some other event to see the value?

Put the breakpoint on the OnPreExecute event of the second data-flow.

When it breaks, drag the variable into a Watch window and you will be able to see the value.

-Jamie

|||

Thanks Jamie.The value is getting set.

But flat file is not skipping the number of rows.

Any solution for this?

Regards

Suganya

|||

Can you share the package and the source files? Paste them up here if so.

-Jamie

|||

It is not possible for me to share the package here.Can u give ur mail id so that i could send my package and source code?

-Suganya

sql

changes to bcp arguments in sql server 2005

Hello All
Seems the arguments have changed for bcp in sql server 2005(CTP). -T use to
be for the trusted connection now it is text or image size. There are new
arguments like -V[security_option] and -Z[security_mechanism]. Does any one
know how to use these new parameters and if you can still do trusted
connections for logining in with BCPShelly,
Please see:
http://www.aspfaq.com/sql2005/show.asp?id=1
For your question however, it looks as -T is still to specify a Trusted
Connection.
Please see:
http://msdn2.microsoft.com/en-us/library/ms162802
HTH
Jerry
"shelly" <shelly@.discussions.microsoft.com> wrote in message
news:9161D896-CFC7-4F5D-A16F-E5B109227AA4@.microsoft.com...
> Hello All
> Seems the arguments have changed for bcp in sql server 2005(CTP). -T use
> to
> be for the trusted connection now it is text or image size. There are new
> arguments like -V[security_option] and -Z[security_mechanism]. Does any
> one
> know how to use these new parameters and if you can still do trusted
> connections for logining in with BCPsql

Change TIME OUT on Qry?

Below is my error msg. I am trying to save updates made to a table in the designer. my connection time out is set to 0 but i am still getting this msg:

/*
Tuesday, March 28, 20066:32:46 PM
User:
Server: EVILONE
Database: DMS
Application:
*/

'Detail' table
- Unable to modify table.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.Is the table in use? Could somebody have it open? Check the current processes.|||No the db is on my laptop and i am the only

Change the File Name in File connection Manager

Hi,

How can I dynamically change the file name in File connection Manager in SSIS package?

I can do this in SQL 2000 but how to achieve the same thing in SQL 2005. I have to generate 10 different excel file and just need to change the file name in connection manager for excel file

Thanks

Shafiq

You'll need to put an expression on the ConnectionString property of the flat file connection manager. Step 3 in here demos this: http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx

In fact, that whole post maps pretty well to what you seem to want to do.

-Jamie

|||

Jamie,

Your article is good, but some how the expression tab of For Each loop does not have connectionstring property in the dropdown list.

I am using Excel Connection Manager for my files.

Please Guide

|||

ConnectionString is a property of the Flat File Connection Manager. Not of the ForEach loop.

-Jamie

Change SSIS File System Connection

Hi,

i've been asked to move our ssis project to another machine
and i've a lot of file system connection whice i must rename them to the new path in the other machine.
there is away to rename the file system connection dynamic or i must go and rename one by one ?
thanks.You'll have to edit them one by one, but the good news is that when you are editing each one, you can use expressions to dynamically create the file path. You can also use package configurations to allow you to set the "base path" via an environment variable, XML file, etc...

You may also be able to open the .dtsx file, though, and use Search/Replace to alter the file paths.|||

Ok thanks i will use Search/Replace to alter the file paths but after it i will have to enter each package and test connection so it will work.

there is away to bypass it?

thanks.

|||Why do you have to test it so that it will work? If the file format is the same, changing the directory shouldn't have any impact.|||

what i meant was that if i move my project to another machine i have to enter each package and there enter to each connection to sql server or odbc edit them,test connection and save the package.

there is any way to skip those levels?

|||

Best solution is to take your own time and make use of config files so that you dont have to bang your head on this in future.

Also i dont think you need to change the settings for ODBC and SQL connections unless and until you plan to execute the package on a different server.

change ssis connection.

Hi,

i have SSIS proj whice now it connect to server A (whice is our test server)

now i want to modified our project that when we want it'll connect to server B (Our Prod server).

now i'm looking for something global that will switch the connection strings whenever i want.

what i find so far is to declare var for each package and give the var default value and to create script to change the value of the var.

is there a solution that will be for all the project and i don't have to change each package (i have alot of pckgs).

Thanks in advanced.

Configurations, as referenced in your other posts.

Change SQL Server Connection from Enterprise Manager

How can we change the login userid by SQL Server Enterprise Manager. I
login with my Windows userid but the last time I used a SQL Server account it
still uses that account when start SQL Server Enterprise Manager.
How do I change Enterpise Manager to use my windows userid.
Thanks,
Hi Joe,
In Enterprise Manager, right click on the registered server
that you want to change the login for. Select Edit SQL
Server Registration Properties. You can change the login
information from there.
-Sue
On Wed, 26 Jan 2005 13:03:09 -0800, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:

>How can we change the login userid by SQL Server Enterprise Manager. I
>login with my Windows userid but the last time I used a SQL Server account it
>still uses that account when start SQL Server Enterprise Manager.
>How do I change Enterpise Manager to use my windows userid.
>Thanks,