Altering a connection manager dynamically via a variable

Within an SSIS Package, we are trying to change the connection string of an output file connection manager at runtime (used for package logging).

To do this, we have defined variables with package-level scope and set the connection manager connection string to this variable. The first step of the package is to set these variables. The second step begins the rest of the package operations (moving data). The package executes successfully, but the log file is never created/appended to.

When the package is debugged, I can verify that the variables are being set correctly in the script task and that the variable values are being passed to the connection manager data sources.

Any ideas why this isn’t working?

You shouldn't use script tasks to try and change connection manager connection strings. Use this technique: http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx

-Jamie

|||Use the mthods in Jamie link that is how I do mine and it works great.

already ran aspnet_regsql but "Could not establish a connection to the database. "

i already ran aspnet_regsql but i still get below...anyone know why? thanks in advance

Could not establish a connection to the database.
If you have not yet created the SQL Server database, exit the Web Site Administration tool, use the aspnet_regsql command-line utility to create and configure the database, and then return to this tool to set the provider.

Check whether SQL Server 2005 Express is running.

Jos

|||

Hi, Firstly ensure that you have completed aspnet_regsql properly by running through and check that the tables are there by using a application such as SQL server management tool.

Then ensure that the web.config file has the correct connection string for your website.

Any problems just post back here.

Dan

|||

yes..i only had sql 2000 installed.

thanks

|||

It has happened to the best

Jos

Allow remote connection (using TCP/IP) from SQL Ce

Hi all,

Need to know if its possible to allow remote connections and access a Sql Ce database using TCP/IP.

Thanks for your help

Hach

No, this is not possible out of the box. What are you trying to acheive? Sounds like SQL Express can help you - it allows remote connections over TCP/IP.|||Your app is the host, so there is no seperate process like bigger sql brothers that listen on an endpoint. You can provide your own listeners using various techs such as wcf, sockets, remoting, etc.

Allow null in a field in Flat File Source

How could I specify in either FF Connection manager or source that it shouldnt give any error and assume blank or no value as NULL ?

Thanks,
FahadYou can't. There is no such thing in a flat file. You'll have to bring in your empty field and then use a derived column to set it to NULL, if necessary.|||Can I change type in derived column ?
I am able to get the field in STR column, Now I wanna change the type of it. Can I ?|||

Fahad349 wrote:

Can I change type in derived column ?
I am able to get the field in STR column, Now I wanna change the type of it. Can I ?

You can do all of this in a derived column. You can't change the type of a column in the dataflow, but you can CAST it to a new type in a NEW column.|||Ok, Last thing is, the source feed contains spaces instead of no value between 2 commas, and I think this makes FF Source failed, what do you think ?|||Import as string, then you can TRIM() that field later in a derived column.|||

you can go to the properties of the Flat file source and set 'RetainNulls' to 'True'.

I think that solves your problem.

|||

Saurabh Kulkarni wrote:

you can go to the properties of the Flat file source and set 'RetainNulls' to 'True'.

I think that solves your problem.

Except the OP stated that the value was blank or empty, not null (char(0)).

Allow null in a field in Flat File Source

How could I specify in either FF Connection manager or source that it shouldnt give any error and assume blank or no value as NULL ?

Thanks,
FahadYou can't. There is no such thing in a flat file. You'll have to bring in your empty field and then use a derived column to set it to NULL, if necessary.|||Can I change type in derived column ?
I am able to get the field in STR column, Now I wanna change the type of it. Can I ?|||

Fahad349 wrote:

Can I change type in derived column ?
I am able to get the field in STR column, Now I wanna change the type of it. Can I ?

You can do all of this in a derived column. You can't change the type of a column in the dataflow, but you can CAST it to a new type in a NEW column.|||Ok, Last thing is, the source feed contains spaces instead of no value between 2 commas, and I think this makes FF Source failed, what do you think ?|||Import as string, then you can TRIM() that field later in a derived column.|||

you can go to the properties of the Flat file source and set 'RetainNulls' to 'True'.

I think that solves your problem.

|||

Saurabh Kulkarni wrote:

you can go to the properties of the Flat file source and set 'RetainNulls' to 'True'.

I think that solves your problem.

Except the OP stated that the value was blank or empty, not null (char(0)).

all pooled connections were in use

I am getting an error like this

'Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool.

This may have occurred because all pooled connections were in use and max pool size was reached.'

What shuld i do

:( :(

you can try setting a new max number of connections for the pool through the connection string.

here is an example connection string:conn.ConnectionString = "integrated security=SSPI;SERVER=YOUR_SERVER;DATABASE=YOUR_DB_NAME;Min Pool Size=5;Max Pool Size=60;Connect Timeout=2;";

see how max pool size is increased? also, here is a link to an article that may help->

http://www.15seconds.com/issue/040830.htm

hope this is helpful -- jp

|||

Try to optimize your query to work faster but if it will not help just change command timeout property to bigger number default is 30 s if I remember correctly. How long takes your query to execute in Management studio?

Thanks

|||

suji,

you'll notice in the connection string that i wrote before that there is also a connection timeout value. you could also try increasing that rather than increasing the pool size if you wish...--jp

|||

Where should I add this? is it in web.config ?

Now i have the following line related to connectionStrings in web config

<connectionStrings>
<add name="myConnectionString" connectionString="Data Source=mssql2005...............,48645\sqlexpress;Initial Catalog=sp;uid=spdmin; pwd=1234567;Trusted_Connection=no" providerName="System.Data.SqlClient"/>
</connectionStrings>

hope u can help me

|||

suji,

you can use the connection string in your web.config oreven your code is you wish. The connection string that i wrotebefore is simply the string itself. at minimum you need thatstring for the code that calls the timeout that you had before. --jp

All of the sudden, SQL Server Failed to Start.

I have had some problem with database connection through
JDBC (in an earlier post) and have been checking the Server
Network Utility. I don't think I made any change. But
after that, SQL Server dies and failed to start. here is
the error in the log file:
---
2005-11-21 19:51:30.10 server Microsoft SQL Server
2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Personal Edition on Windows NT 5.1 (Build 2600: Service
Pack 2)
2005-11-21 19:51:30.10 server Copyright (C) 1988-2000
Microsoft Corporation.
2005-11-21 19:51:30.10 server All rights reserved.
2005-11-21 19:51:30.10 server Server Process ID is 3876.
2005-11-21 19:51:30.10 server Logging SQL Server messages
in file 'C:\Program Files\Microsoft SQL
Server\MSSQL\log\ERRORLOG'.
2005-11-21 19:51:30.14 server SQL Server is starting at
priority class 'normal'(1 CPU detected).
2005-11-21 19:51:30.17 server SQL Server configured for
thread mode processing.
2005-11-21 19:51:30.17 server Using dynamic lock
allocation. [500] Lock Blocks, [1000] Lock Owner Blocks.
2005-11-21 19:51:30.18 spid3 Starting up database
'master'.
2005-11-21 19:51:30.40 server Using 'SSNETLIB.DLL'
version '8.0.194'.
2005-11-21 19:51:30.42 server Encryption requested but no
valid certificate was found. SQL Server terminating.
2005-11-21 19:51:30.42 server Error: 17826, Severity: 18,
State: 1
2005-11-21 19:51:30.42 server Could not set up
Net-Library 'SSNETLIB'..
2005-11-21 19:51:30.43 server Error: 17059, Severity: 18,
State: 0
2005-11-21 19:51:30.43 server Operating system
error -1073723998: ..
2005-11-21 19:51:30.43 server Unable to load any netlibs.
2005-11-21 19:51:30.43 server SQL Server could not spawn
FRunCM thread.
2005-11-21 19:51:30.43 spid5 Starting up database
'model'.
---
Any suggestion...?
Thanks,
pacNever mind.. :-)
packat wrote:
> I have had some problem with database connection through
> JDBC (in an earlier post) and have been checking the
> Server
> Network Utility. I don't think I made any change. But
> after that, SQL Server dies and failed to start. here is
> the error in the log file:
> ---
> 2005-11-21 19:51:30.10 server Microsoft SQL Server
> 2000 - 8.00.194 (Intel X86)
> Aug 6 2000 00:57:48
> Copyright (c) 1988-2000 Microsoft Corporation
> Personal Edition on Windows NT 5.1 (Build 2600: Service
> Pack 2)
> 2005-11-21 19:51:30.10 server Copyright (C) 1988-2000
> Microsoft Corporation.
> 2005-11-21 19:51:30.10 server All rights reserved.
> 2005-11-21 19:51:30.10 server Server Process ID is
> 3876.
> 2005-11-21 19:51:30.10 server Logging SQL Server
> messages
> in file 'C:\Program Files\Microsoft SQL
> Server\MSSQL\log\ERRORLOG'.
> 2005-11-21 19:51:30.14 server SQL Server is starting at
> priority class 'normal'(1 CPU detected).
> 2005-11-21 19:51:30.17 server SQL Server configured for
> thread mode processing.
> 2005-11-21 19:51:30.17 server Using dynamic lock
> allocation. [500] Lock Blocks, [1000] Lock Owner Blocks.
> 2005-11-21 19:51:30.18 spid3 Starting up database
> 'master'.
> 2005-11-21 19:51:30.40 server Using 'SSNETLIB.DLL'
> version '8.0.194'.
> 2005-11-21 19:51:30.42 server Encryption requested but
> no
> valid certificate was found. SQL Server terminating.
> 2005-11-21 19:51:30.42 server Error: 17826, Severity:
> 18,
> State: 1
> 2005-11-21 19:51:30.42 server Could not set up
> Net-Library 'SSNETLIB'..
> 2005-11-21 19:51:30.43 server Error: 17059, Severity:
> 18,
> State: 0
> 2005-11-21 19:51:30.43 server Operating system
> error -1073723998: ..
> 2005-11-21 19:51:30.43 server Unable to load any
> netlibs.
> 2005-11-21 19:51:30.43 server SQL Server could not
> spawn
> FRunCM thread.
> 2005-11-21 19:51:30.43 spid5 Starting up database
> 'model'.
> ---
> Any suggestion...?
> Thanks,
> pac|||Pac,
I am getting exactly the same error log message when trying to start the
MSSQLSERVER service for SQL 2005 on Win 2003 server.
What did you do to resolve it?
Please help.
--
Thanks in advance
Mgale1
"packat" wrote:

> Never mind.. :-)
> packat wrote:
>
>

All of the sudden, SQL Server Failed to Start.

I have had some problem with database connection through
JDBC (in an earlier post) and have been checking the Server
Network Utility. I don't think I made any change. But
after that, SQL Server dies and failed to start. here is
the error in the log file:
2005-11-21 19:51:30.10 server Microsoft SQL Server
2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Personal Edition on Windows NT 5.1 (Build 2600: Service
Pack 2)
2005-11-21 19:51:30.10 server Copyright (C) 1988-2000
Microsoft Corporation.
2005-11-21 19:51:30.10 server All rights reserved.
2005-11-21 19:51:30.10 server Server Process ID is 3876.
2005-11-21 19:51:30.10 server Logging SQL Server messages
in file 'C:\Program Files\Microsoft SQL
Server\MSSQL\log\ERRORLOG'.
2005-11-21 19:51:30.14 server SQL Server is starting at
priority class 'normal'(1 CPU detected).
2005-11-21 19:51:30.17 server SQL Server configured for
thread mode processing.
2005-11-21 19:51:30.17 server Using dynamic lock
allocation. [500] Lock Blocks, [1000] Lock Owner Blocks.
2005-11-21 19:51:30.18 spid3 Starting up database
'master'.
2005-11-21 19:51:30.40 server Using 'SSNETLIB.DLL'
version '8.0.194'.
2005-11-21 19:51:30.42 server Encryption requested but no
valid certificate was found. SQL Server terminating.
2005-11-21 19:51:30.42 server Error: 17826, Severity: 18,
State: 1
2005-11-21 19:51:30.42 server Could not set up
Net-Library 'SSNETLIB'..
2005-11-21 19:51:30.43 server Error: 17059, Severity: 18,
State: 0
2005-11-21 19:51:30.43 server Operating system
error -1073723998: ..
2005-11-21 19:51:30.43 server Unable to load any netlibs.
2005-11-21 19:51:30.43 server SQL Server could not spawn
FRunCM thread.
2005-11-21 19:51:30.43 spid5 Starting up database
'model'.
Any suggestion...?
Thanks,
pac
Never mind.. :-)
packat wrote:
> I have had some problem with database connection through
> JDBC (in an earlier post) and have been checking the
> Server
> Network Utility. I don't think I made any change. But
> after that, SQL Server dies and failed to start. here is
> the error in the log file:
> 2005-11-21 19:51:30.10 server Microsoft SQL Server
> 2000 - 8.00.194 (Intel X86)
> Aug 6 2000 00:57:48
> Copyright (c) 1988-2000 Microsoft Corporation
> Personal Edition on Windows NT 5.1 (Build 2600: Service
> Pack 2)
> 2005-11-21 19:51:30.10 server Copyright (C) 1988-2000
> Microsoft Corporation.
> 2005-11-21 19:51:30.10 server All rights reserved.
> 2005-11-21 19:51:30.10 server Server Process ID is
> 3876.
> 2005-11-21 19:51:30.10 server Logging SQL Server
> messages
> in file 'C:\Program Files\Microsoft SQL
> Server\MSSQL\log\ERRORLOG'.
> 2005-11-21 19:51:30.14 server SQL Server is starting at
> priority class 'normal'(1 CPU detected).
> 2005-11-21 19:51:30.17 server SQL Server configured for
> thread mode processing.
> 2005-11-21 19:51:30.17 server Using dynamic lock
> allocation. [500] Lock Blocks, [1000] Lock Owner Blocks.
> 2005-11-21 19:51:30.18 spid3 Starting up database
> 'master'.
> 2005-11-21 19:51:30.40 server Using 'SSNETLIB.DLL'
> version '8.0.194'.
> 2005-11-21 19:51:30.42 server Encryption requested but
> no
> valid certificate was found. SQL Server terminating.
> 2005-11-21 19:51:30.42 server Error: 17826, Severity:
> 18,
> State: 1
> 2005-11-21 19:51:30.42 server Could not set up
> Net-Library 'SSNETLIB'..
> 2005-11-21 19:51:30.43 server Error: 17059, Severity:
> 18,
> State: 0
> 2005-11-21 19:51:30.43 server Operating system
> error -1073723998: ..
> 2005-11-21 19:51:30.43 server Unable to load any
> netlibs.
> 2005-11-21 19:51:30.43 server SQL Server could not
> spawn
> FRunCM thread.
> 2005-11-21 19:51:30.43 spid5 Starting up database
> 'model'.
> Any suggestion...?
> Thanks,
> pac
|||Pac,
I am getting exactly the same error log message when trying to start the
MSSQLSERVER service for SQL 2005 on Win 2003 server.
What did you do to resolve it?
Please help.
Thanks in advance
Mgale1
"packat" wrote:

> Never mind.. :-)
> packat wrote:
>
>