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 Flat File Source Dynamically

I am a relative newbie to SSIS. I have been tasked with writing packages to import data from our clients. We have about 100 clients. Each client has a few different file formats. None of the clients have the same format as each other. We load files from each client each day. Each day the file name changes. I have done all of my current development work with a constant file name in a text file connection manager.

Ultimately we will write a VB application for the computer operator to select the flat file to load and the SSIS package to load it with. I had been planning on accomplishing this thru the SSIS command line interface. Can I specify the flat file to load via a variable that is passed through the command line? Do I need to use a Script Component to take the variable and assign it to the connection manager?

Is there a better way to do this? I have seen glimpses of a VB interface to SSIS. Maybe that is a better way to kick off the packages from a VB app?

Thanks,

Chris

You can set the ConnectionString property of your flat file connection managers to be based on an expression, which is in turn based on a package variable. You can then set the value of the variable from the command line when you run DEXEC.

You can also use the Foreach Loop container to loop through all of the files in a given folder, and use the package variable as the loop enumerator, so that it will be automatically set to the name of the current file. If you know that files that are of a given format from a given client will always be in a specific folder, this might eliminate the need to have a custom UI and rely on operator input to kick off the ETL process. I don't know if this makes sense for your environment, but it sounds attractive to me.

I have not personally used the .NET API to execute packages, so I cannot comment on that approach.

|||

Matthew,

Thank you! I knew that should be possible, but, I couldn't figure out how. Based on your hint (expression) I was able to find the expressions section on my Connection's properties pane. I added an expression the ConnectionString (path) to a variable. It works!

One small oddity... I wanted to set the variable in a Script Component for testing (rather than command line). However, I couldn't set the Script Component as the first object on my Data Flow since the Flat File Source object will not take a predecessor. Instead I created another Data Flow, made my main one dependent on that, and then added a "Source" Script Component to the new Data Flow... then I populated the new variable in that Script Component.

Thanks,

Chris

|||

tofferr wrote:

One small oddity... I wanted to set the variable in a Script Component for testing (rather than command line). However, I couldn't set the Script Component as the first object on my Data Flow since the Flat File Source object will not take a predecessor. Instead I created another Data Flow, made my main one dependent on that, and then added a "Source" Script Component to the new Data Flow... then I populated the new variable in that Script Component.

Thanks,

Chris

Why not use a Script Task immediately before your data flow task to set the variable?

|||

jwelch wrote:

Why not use a Script Task immediately before your data flow task to set the variable?

That's a good idea... but, how do I access the varibales? Variables.variableName is not getting recognized in the script component editor, even after I added it to the ReadWriteVariables list.

Thanks,

Chris

|||

This article from Jamies is right on point for much of this.

http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx

Chris

|||Dts.Variables("NameofVar") should work in the Script Task.|||

jwelch wrote:

Dts.Variables("NameofVar") should work in the Script Task.

You also need to remember to specify the (case-sensitive!) variable's name in the ReadWriteVariables property of the Script task for this to work.

|||

tofferr wrote:

Matthew,

Thank you! I knew that should be possible, but, I couldn't figure out how. Based on your hint (expression) I was able to find the expressions section on my Connection's properties pane. I added an expression the ConnectionString (path) to a variable. It works!

Thanks for the feedback!

If you ask me, SSIS Expressions are the most powerful single feature in the SSIS toolset. Being able to declaratively bind arbitrary expressions to arbitrary properties of arbitrary components (with a few restrictions, of course), so that the expression is evaluated whenever the property is accessed, is incredibly powerful and cool. So many people I talk to use SSIS without having a real "light bulb moment" about expressions, but to me they are a primary tool for making SSIS packages configurable, maintainable and reusable.

I feel a blog post coming on...

sql

Change Data Capture

Again, looking for the best way to do this with SSIS.

I have a source table and I'd like to load it to a database daily, capturing what changed.

This is not a dimentional table but a fact table.

So, what I;d need to do for each record is to see if the record already exists (using business key) and if it does - compare some of the data fields and of there are changes - register it somehow and if not changes ignore.

Right now, the only two ways I see to do it with SSIS:

- Use Slowly Chaging Dimentions transformation

- Use Lookup and customize SQL, adding something like: WHERE key = ? and (field1 <> ? or field2 <> ?...)

I was wondering of there an easy way.

Dima.

Use checksums... I have something about that on my blog, you can download the component on www.sqlis.com...|||

this is not bad! not bad at all...

and it fits my case, since I have around 80 columns to check.

I wish they extened data destination column mapping to do this. If they added an Option column on the mapping screen with things like: "Key", "Track Changes", "Overwrite" - this would be so powerfull tool.

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...

Catching an exception from a Web service task

Hi!

I am quite new using SSIS and I have a problem with catching an (SOAP) exception from a Web service task. Some times my web service task can fail and when the web service is failing, it is throwing an exception. When the task succeeds the result is being put into a variable, That part is not a problem.

But catching an exception is. I have tried to use a script task and tried to get exception from the dts object model. I have not yet succeeded on that. But it might be a possible way to go. A different approach might be creating an OnError event on my web service task which I can create a task when triggered. But I have not found any solution yet and I hope some people out there have done this before or have a solution on this.

Regards

Geir F

I forget to tell that I want the error description text from the exception that is thrown from the web service task because I want to log the errors. So what I hope is to have a script task that executes when web service fails and to write some code to catch the error description.

Current status on this is that the script task executes when the WS fails (red arrow). Nice. The web service returns an object, say TestOutput variable. When the WS succeeds, I can convert the object into a integer datatype and catch the value. But when it fails, I don't know to catch the error description. When executing the package, I can see the error description on th progress tab, so there must be a way for me to get the error too :-)

Regards

GF

Casting DT_WSTR to integers in SSIS

Hello, all.

Why is it that, despite what is said in the sketchy SQL Help content, it appears to be impossible to cast a string to an integer in the Expression Builder to generate a value for a variable? More specifically, why does the following expression cause an error?

(DT_UI4) (SUBSTRING(@.[User::FullDataPath], LEN(@.[User:ataPath]) + 1, 2))

I'm iterating over files and using the name of a given file as an ID for an operation. I simply want to grab a file name using the Foreach Loop Container and process that file, while at the same time use the name in another operation. The file name will be something like "2.txt" (full path something like "c:\something\something\2.txt"). I can use string functions to return the file name, which is a number as a string, and it should be no problem to cast that number as a string to a number (an Int32). SQL Server 2005 help has a chart that indicates such a cast is legal.

Maybe it's a crazy thing to be doing. Maybe I have to go about this a completely different way, but casting from "2" to 2 should be possible in the Expression Builder.

Thanks for any help.

I wonder if it doesn't like a period in the data when it tries to cast to an integer....

substring("c:\something\something\2.txt",LEN("c:\something\something") + 1, 2) = "2."|||I see that's not the case, as what I wrote.

Workaround: add a second derived column and in that component, do your cast to DT_UI4. In the first, just perform the substring operation.

I'm not sure that it will allow it in one step because it can't parse the data to determine if it's numeric or not based on the substring operation. That is, when trying to convert the output of substring, you have no idea if it's numeric or not. (YOU do, but SSIS doesn't) When you add a second derived column component, casting a column to DT_UI4 should be straightforward because SSIS is going to imply that it's numeric and can be cast.

I don't like it, but that's my guess. You are always welcome to vent this issue over at http://connect.microsoft.com/sqlserver/feedback.

Phil|||

What's the error message?

-Jamie

|||

Hey, Phil.

Thanks for the response. It turns out that my problem was relying on ALL of my variables being populated at runtime. I didn't bother to specify default values for the variables I had added to my package, because I was populating these variables are runtime. This was working, but once I started using those variables for cast operations, the Expression Builder evaluation mechanism was choking.

What I was doing was this:

1. I was getting a variable folder path from a database and storing that in a variable.

2. I used that folder path variable as an expression for the Foreach Loop Container, wherefrom I derived a full file path (spec) in order to process multiple files.

3. But then I got the brilliant idea of using the folder path and the full file spec to derive a numerical value (based on the file name) and I used that value as an input into an Execute SQL Task to do an auxiliary task.

The bottom line is that once I specified bogus default values at design time (which values were replaced anyway at runtime) for the variables upon which my cast expression depended, the Expression Builder evaluation mechanism was able to validate my cast expressions.

This little idiocy of mine has cost me a few hours of work. But, I should say that I saw nothing about runtime evaluations of design time values DURING DESIGN TIME in the SQL Help.

|||

phanoteus wrote:

This little idiocy of mine has cost me a few hours of work. But, I should say that I saw nothing about runtime evaluations of design time values DURING DESIGN TIME in the SQL Help.

Indeed. So I think you're beig a bit harsh on yourself criticising your actions as idiocy. It doesn't strike me as sensible or intuitive that variables have to be initialised to a value that will never get used. But that's just me.

-Jamie

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