Change in connection string

I have a SSIS Package which I developed on my own my database server.

But now the package is to deployed on 3-4 different computers pointing it to different databases.

I do not want to change the connection string for each and every instance because in future there may be more number of instances running.

I tried using the Configuration file and add that file in to the configuration window but that also did not work.

Please can anybody help me?
We're going to need more information here... You said you tried using a configuration file, "but that also did not work."

How did you set it up? What exactly happened that "did not work"? If running locally for testing, you'll have to get out of the package and reopen it for it to pick up the new configuration if that makes a difference.|||

Phil Brammer wrote:

We're going to need more information here... You said you tried using a configuration file, "but that also did not work."

How did you set it up? What exactly happened that "did not work"? If running locally for testing, you'll have to get out of the package and reopen it for it to pick up the new configuration if that makes a difference.

[Apologies for jumping in on this thread. If I get a reply then I'll delete it later.]

Phil,

Please could you email me - (jamieDOTthomsonATconchangoDOTcom). Just got something I want to ask you offline.

thanks

Jamie

|||I have file named dbConfig.dtsConfig. It had the following code in it

<DTSConfiguration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[SERVERNAMEdbname].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=Av-int-sj-db01\instance_b;Initial Catalog=AvaTaxAccount;User ID=avaservice;password=kennwort.2005;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False</ConfiguredValue>
</Configuration>
</DTSConfiguration>

While I double click the package exe the Execute Package Utility pop ups and in that I select the configuration option and add the above file in it.

Then try executing the package. Its gives an error message of the login not found.

Is this the right way to change the connection string while executing the package.

Change Column Order

How does one go about changing the column order of the data moving thru an SSIS package? It'd be nice to able to do this at any point in the package for readability, but where I really need it is in the Flat File Output. I need to deliver the columns in a specific order, and I can't figure out how to do that.

This seems to be such a basic question, but I can't find it in Search anywhere. My apologies if it's already been answered.The column order of a flat file is defined within the connection. Changing column order within the pipeline itself would be a bad ideas as this require moving data around, which costs. A way to do this as a view for readability may well be nice, but may end up being confusing as well.

The default behaviour of the Flat File Destination means you create a new connection within the UI, and that connection will happily define the columns according to the buffer, which is just perfect 99% of the time. For your case you have two workarounds-

1 - Add your Flat File Connection, set file and delimiters. Select the Advanced tab, and add your columns as you want in the correct order. This connection can then be selected, and you map the columns. You could do this within the Flat File Destination, as this will create all columns to start with, but cannot change order, which is a shame, so you will have to remove and insert columns to get the correct order overall.

2 - A simple method is to create a flat file that represents what you want to produce from SSIS. Then you can add a new Flat File Connection, and select the sample file, and allow the columns to be generated from this file. You may still wish to fine tune this through the Advanced tab, but it should do the bulk of the work.

Why not log some Feedback on MSDN for the ability to change column order within the Flat File connection as that is really what you want I belive.|||I had the exact same question...and I too couldn't believe that it wasn't simple to re-order the outputs being written to a flat file.

I am pulling data from a database using a SQL Query then adding columns with the Derived Column transform. I want the derived columns to go at the beginning of the output file, not the end.

I like your idea of reverse engineering a sample file...I will give that a shot.

I am currently evaluating ETL tools for converting data from various database types and structures to complex (multiple record types) flat files. Overall, I am pretty impressed with SSIS although I have noticed that some tasks are not as straightforward as they are in Sagent and Informatica.

|||If you are not already aware there are a couple of whitepapers that may help your evaluation-

Microsoft SQL Server: Forrester Report: Microsoft Addresses Enterprise ETL
(http://www.microsoft.com/sql/technologies/integration/foresterreport.mspx)
Well that's one. The other was a review of SSIS compared to Informatica, by Conchango. I can't find it now in the wake of the site updates for RTM. Hopefully it will reappear or someone will post a link. Searching microsoft.com only gives a broken link.

Don't forget you get a free RDBMS, OLAP and Reporting System when you buy SSIS :)|||

Very funny...you never know when that RDBMS may come in handy ; )

Thanks for the Forrester link.

I found the Conchango review....thanks for the lead.
http://download.microsoft.com/download/1/0/3/103fd39e-3ca4-4db7-a087-1263dc6ed0b1/CompIntTools.pdf

Our enterprise product is built pretty much from front to back on Microsoft technology so SSIS will be hard to beat if it can get the job done efficiently.

Catching errors in SSIS Backup Database Task

Hi,

In my SSIS package, I have a backup database task. When I run the package with DestinationAutoFolderPath set to a folder ("Network Service" account has full permission on this folder) and DestinationCreationType set to Auto, the task works just fine creating a backup with its own name. (similar to database_date<count>).

But what I want is in my front-end I am allowing the user to specify the name of the backup file. So I want the task to create the backup file in the name I supply. I set the DestinationCreationType to manual and in the application code added the DestinationManualList with the path from the UI.

Now the pacakge runs fine but does not take any backup. There is no errors as well. If I set the FailPackageOnFailure and FailParentOnFailure to true, then I am getting the DTSExecResult.Failure but I am not getting the actual error from the backup database task.

Am I missing anything here?

Thanks in advance,
Srikanth.

How are you executing the package?

What does the logfile (I assume that you have one) say?

-Jamie

|||

Thanks a lot for the reply.

I enabled the log in package and the backup database task. The following is the text appears in the log file:

#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message
OnPreValidate,SERVER211,SERVER211\Administrator,DICoreDataPackage,{8B470140-EB79-4A15-A2A7-10590065310C},{844D2D28-16D8-42C8-9E1F-3E1506544BE5},8/29/2006 11:31:28 AM,8/29/2006 11:31:28 AM,0,0x,(null)
PackageStart,SERVER211,SERVER211\Administrator,DICoreDataPackage,{8B470140-EB79-4A15-A2A7-10590065310C},{844D2D28-16D8-42C8-9E1F-3E1506544BE5},8/29/2006 11:31:28 AM,8/29/2006 11:31:28 AM,0,0x,Beginning of package execution.

Diagnostic,SERVER211,SERVER211\Administrator,DICoreDataPackage,{8B470140-EB79-4A15-A2A7-10590065310C},{844D2D28-16D8-42C8-9E1F-3E1506544BE5},8/29/2006 11:31:28 AM,8/29/2006 11:31:28 AM,0,0x,Based on the system configuration, the maximum concurrent executables are set to 3.

OnPreExecute,SERVER211,SERVER211\Administrator,DICoreDataPackage,{8B470140-EB79-4A15-A2A7-10590065310C},{844D2D28-16D8-42C8-9E1F-3E1506544BE5},8/29/2006 11:31:28 AM,8/29/2006 11:31:28 AM,0,0x,(null)
PackageEnd,SERVER211,SERVER211\Administrator,DICoreDataPackage,{8B470140-EB79-4A15-A2A7-10590065310C},{844D2D28-16D8-42C8-9E1F-3E1506544BE5},8/29/2006 11:31:29 AM,8/29/2006 11:31:29 AM,1,0x,End of package execution.

The thing is, I configured the DestinationCreationType to "manual" and added my backup file name through coding to DestinationManualList ArrayList:

if (backupTask.DestinationManualList == null)
backupTask.DestinationManualList = new ArrayList();

backupTask.DestinationManualList.Add(m_SSISArgs.DatabaseBackupPath);

The objective is to create backup of the database with the file name I am giving. Do I have to set anyother property for this...

Cast from string to date

I created an SSIS package that pulls in legacy data from a DB2 AS400. There is an In Date field that is stored on the AS400 as OdbcType.Date or DATE. However, when I use SSIS to pull in the data, SQL wants it to be a string so I gave up and let SQL have it's way.

Now in my SELECT statement, I have this:

SELECT TLMST.TLNUMBER, TLMST.DOGNAM, BRDMST.BRDesc, TLMST.INDT
FROM TLMST INNER JOIN
BRDMST ON TLMST.BRDCOD = BRDMST.BreedNumber
WHERE (TLMST.INDT >= @.startdate) AND (TLMST.INDT <= @.enddate)
ORDER BY TLMST.INDT, TLMST.TLNUMBER

Because I see the dates stored in TLMST are yyyy-mm-dd format, this is not working. It is not pulling any records. I tried to use the CAST statement but I keep getting errors about casting from a string to a date and data overflow errors.

Should I go back and re-do the SSIS or is there a way to pull the records for this? Thanks for the information

Do a Conversion task in SSIS, with either chopping the values from the string to create a vlid date like yyyymmdd or use the format function with that.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||When you create your source in your data flow, what is the data type SSIS has set for the column. You can find this out by right clicking on the source and selecting advanced editor and then selecting the input and output properties. In there you can expand the input tree to find the data type for the date column.|||The advanced editor is showing the data type as string, however on the AS400 it is of type OdbcType.Date. But that is on the AS400 side which I am not too familiar with.|||In which case follow Jens advice to split the date passed and build a string that is of the correct format

Cast @[System::StartTime] Problem

This is a Subject Expression I put in my Mail component in my SSIS 2005 package but it's not liking the StartTime concatenation. I tried other ways but can't get it to allow this:

"Process Started by : " + @.[System::UserName] + (DT_DATE) @.[System::StartTime]

I get the error:

TITLE: Expression Builder

Expression cannot be evaluated.

ADDITIONAL INFORMATION:

The data types "DT_WSTR" and "DT_DATE" are incompatible for binary operator "+". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

Attempt to set the result type of binary operation ""Process Started by : " + @.[System::UserName] + (DT_DATE)@.[System::StartTime]" failed with error code 0xC0047080.

I tried to use CAST() but not sure if that's allowed or the syntax for it specifically in SSIS 2005

As the error message says, you cannot concatenate DT_WSTR & DT_DATE. Try:

"Process Started by : " + @.[System::UserName] + (DT_WSTR) @.[System::StartTime]

-Jamie