Help with Connections

Hi everybody,

I need a little help...

I created several packages, that after their creation should not be changed again; that's why I created a Package Configuration File in which I set all the Connections I need, so that this is the only file I have to modify.

I set there also the path of the connections for my log file and for my error files (which are both text files), but if I change their paths in the configuration file this change doesn't affect the package, and the path of the log remains the same.

That's an image of one of those packages....

The fact is that I cannot change the ConnectionString of the connection called "log_pkg", but it always remains the same...

How can I resolve this problem?

Thanks everyone

In general this should work. I just did a quick test where I had a package already which contained a Text Log Provider so I added an XML type configuration to the package, on just the connetion string. If I edit the config file and change the name of the file, rerun the package the, the new name is created.

this was section of the config file I was editing, specifically the name in the string "ftp_Log2.txt"

- <Configuration ConfiguredType="Property" Path="\Package.Connections[ftp_Log.txt].Properties[ConnectionString]" ValueType="String">

<ConfiguredValue>C:\_work\SSISPackages\ProtectionLevelTest\ftp_Log2.txt</ConfiguredValue>

</Configuration>

You are doing something similar and it does not work?

When you execute in BI studio, do you see a message similar to the followingin the output window indicating the config file was read?

Information: 0x40016041 at ftptesting: The package is attempting to configure from the XML file "C:\SSISPackages\ftpconfig.dtsConfig".

Help W/Disabling FK Constraints for Batch Operations

HI all,

I'm trying to have a SProc that will initialize a database for me. This db is in development (I'm primarily writing SSIS packages, atm), and I constantly need to truncate the tables, then re-add a dummy/unknown row (PK/Identity value = 1). Of course, I need triggers not to fire (got that part working), and FK constraints to be bypassed temporarily -- that's the problem.

Here's where I'm at:

-
CREATE PROCEDURE [dbo].[_InitializeDB]
AS

SET NOCOUNT ON

DECLARE @.name varchar(255)
DECLARE @.sql nvarchar(255)

DECLARE tables CURSOR FOR SELECT [name] FROM [sysobjects] WHERE [type]='U' AND [name]<>'sysdiagrams'

OPEN tables
FETCH NEXT FROM tables INTO @.name
WHILE @.@.FETCH_STATUS=0
BEGIN
SET @.sql = 'ALTER TABLE ['+ @.name + '] NOCHECK CONSTRAINT ALL'
EXEC sp_executeSQL @.sql
SET @.sql = 'DISABLE TRIGGER ALL ON [' + @.name + ']'
EXEC sp_executeSQL @.sql
SET @.sql = 'TRUNCATE TABLE [' + @.name + ']'
EXEC sp_executesql @.sql
BEGIN TRY
SET @.sql = 'INSERT INTO [' + @.name + '] (Active) VALUES (0)'
EXEC sp_executeSQL @.sql
END TRY
BEGIN CATCH
PRINT @.sql + ':'
PRINT ERROR_MESSAGE()
END CATCH
SET @.sql = 'ENABLE TRIGGER ALL ON [' + @.name + ']'
EXEC sp_executeSQL @.sql
SET @.sql = 'ALTER TABLE ['+ @.name + '] CHECK CONSTRAINT ALL'
EXEC sp_executeSQL @.sql
FETCH NEXT FROM tables INTO @.name
END

CLOSE tables
DEALLOCATE tables
-

Running this Sproc produces (for the first ref'd table):

Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'Person' because it is being referenced by a FOREIGN KEY constraint.

If you dont wanna be bothered by the FK Constraints you have to drop them do your work and recreate them after you did your work.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||TRUNCATE TABLE doesn't check the state of the FK constraint so this method will not work. You will have to drop and recreate the FK constraint for the truncate table to work.