Data sources and deployment of packages

I notice that when deploying a package that uses data sources behind its connection managers, the deployed package has the connection string built within the package that gets deployed.

From a previous post from Kirk - "What I'd suggest is that you create a Data source per machine and have all the connections reference it. You'll still need to create a connection manager in each package, but they will all reference the same data source. Moving to different machines is no problem because you can have a "machine resident" data source file that properly points to your "ETL Server"."

How are data sources defined for a machine? Is this simply via one of the configuaration options (i.e. an .xml file) that are loaded upon runtime by a package that is built to reference that config, which then trumps the connection string defined within the package?

ThanksDatasources are a design time feature. The connection manager references the DS and caches the connection string so that even if the datasource is no longer available, the connection will still be viable.

For cases where you need to deploy to a location where you will not be opening the package in the designer, and you need the connection string to change, you'll need to configure the connection manager in the package. The data source will only modify the connection in the designer.

The point of the earlier post was that if you're designing multiple packages and you wish to have all the connections in those packages pointing to the same server, you can create datasource and reference it by the DataSourceID in all the packages. If the server changes, you can modify one data source and it will modify all the connections for you. Next time you open the packages, they will pick up the new connection string. Moving the package to a different machine is also no problem so long as there is a data source available on the target machine with the same DataSourceID.

Configurations are your first line of defense for making packages location independent for easier deployment and your best option for packages that won't be opened in the designer. The DataSource approach is just a one off thing you can do in some cases.

K|||

>> Moving the package to a different machine is also no problem so long as there is a data source available on the target machine with the same DataSourceID.
>> Configurations are your first line of defense for making packages location independent for easier deployment and your best option for packages that won't be opened in the designer.

When I'm talking about deployment, I'm thinking test/prod etc where I'll never need to open it in Designer. That's what the dev env is for. Isn't it?

Given that, package configurations look like the "only option" and not just a "best option", and "only line of defence", not "first line of defense". Isn't that true? Basically, if I don't plan on opening my packages in Designer, then I have to go through the painful process of configuring each package separately and entering values for each package at deployment time. Is there an alternative?

Thanks,

RV.

|||

We are required to do a silent install in production at client sites, ie. no DBA or designer access. In this case, we develop in our environment and may would have to deploy each package with its connection strings. This may impact our design to minimize the number of packages.

Any thoughts?

|||

Use configurations. For each site you will need to adjust the configuration data store to hold the correct values for that site, and how you that depends on the store you use. You could write a wizard where the person running the install simply supplies the values and these are written to the correct location. Your packages will already be configured to read their information from configurations so everything should be sweet. Indirect configurations may be easier, and the values set by the install wizard.

Some links-

http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2342.aspx

http://blogs.conchango.com/jamiethomson/archive/2005/10/31/2336.aspx

http://msdn2.microsoft.com/en-us/library/ms141682.aspx

http://www.sqljunkies.com/WebLog/knight_reign/archive/2004/12/07/5445.aspx

|||

I went through all this frustration myself and eventually settled on a solution that works very well. In a nutshell, I store my connection strings for all my connection managers in the database.

1) All my connection managers are of type OLEDB

2) Every package that has a connection string has package configurations enabled with a Configuration entry of type SQL Server that points to a Configuration table in the database. This entry is the way the connection manager gets the connection string at runtime. In the Configuration entry, it is the Configuration filter setting which will differentiate the different entries in the database pertaining to different connection strings.

3) My deployment scripts insert the correct database connection info into the configuration database for each connection. The connection strings are dependent on the environment I'm deploying to. The connection managers in the solution will at runtime grab the value from the database via the package configurations on the package the connection managers live on.

4) I deploy using dtsutil and make it silent. No need to not have it silent.

Data sources and deployment of packages

I notice that when deploying a package that uses data sources behind its connection managers, the deployed package has the connection string built within the package that gets deployed.

From a previous post from Kirk - "What I'd suggest is that you create a Data source per machine and have all the connections reference it. You'll still need to create a connection manager in each package, but they will all reference the same data source. Moving to different machines is no problem because you can have a "machine resident" data source file that properly points to your "ETL Server"."

How are data sources defined for a machine? Is this simply via one of the configuaration options (i.e. an .xml file) that are loaded upon runtime by a package that is built to reference that config, which then trumps the connection string defined within the package?

ThanksDatasources are a design time feature. The connection manager references the DS and caches the connection string so that even if the datasource is no longer available, the connection will still be viable.

For cases where you need to deploy to a location where you will not be opening the package in the designer, and you need the connection string to change, you'll need to configure the connection manager in the package. The data source will only modify the connection in the designer.

The point of the earlier post was that if you're designing multiple packages and you wish to have all the connections in those packages pointing to the same server, you can create datasource and reference it by the DataSourceID in all the packages. If the server changes, you can modify one data source and it will modify all the connections for you. Next time you open the packages, they will pick up the new connection string. Moving the package to a different machine is also no problem so long as there is a data source available on the target machine with the same DataSourceID.

Configurations are your first line of defense for making packages location independent for easier deployment and your best option for packages that won't be opened in the designer. The DataSource approach is just a one off thing you can do in some cases.

K|||

>> Moving the package to a different machine is also no problem so long as there is a data source available on the target machine with the same DataSourceID.
>> Configurations are your first line of defense for making packages location independent for easier deployment and your best option for packages that won't be opened in the designer.

When I'm talking about deployment, I'm thinking test/prod etc where I'll never need to open it in Designer. That's what the dev env is for. Isn't it?

Given that, package configurations look like the "only option" and not just a "best option", and "only line of defence", not "first line of defense". Isn't that true? Basically, if I don't plan on opening my packages in Designer, then I have to go through the painful process of configuring each package separately and entering values for each package at deployment time. Is there an alternative?

Thanks,

RV.

|||

We are required to do a silent install in production at client sites, ie. no DBA or designer access. In this case, we develop in our environment and may would have to deploy each package with its connection strings. This may impact our design to minimize the number of packages.

Any thoughts?

|||

Use configurations. For each site you will need to adjust the configuration data store to hold the correct values for that site, and how you that depends on the store you use. You could write a wizard where the person running the install simply supplies the values and these are written to the correct location. Your packages will already be configured to read their information from configurations so everything should be sweet. Indirect configurations may be easier, and the values set by the install wizard.

Some links-

http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2342.aspx

http://blogs.conchango.com/jamiethomson/archive/2005/10/31/2336.aspx

http://msdn2.microsoft.com/en-us/library/ms141682.aspx

http://www.sqljunkies.com/WebLog/knight_reign/archive/2004/12/07/5445.aspx

|||

I went through all this frustration myself and eventually settled on a solution that works very well. In a nutshell, I store my connection strings for all my connection managers in the database.

1) All my connection managers are of type OLEDB

2) Every package that has a connection string has package configurations enabled with a Configuration entry of type SQL Server that points to a Configuration table in the database. This entry is the way the connection manager gets the connection string at runtime. In the Configuration entry, it is the Configuration filter setting which will differentiate the different entries in the database pertaining to different connection strings.

3) My deployment scripts insert the correct database connection info into the configuration database for each connection. The connection strings are dependent on the environment I'm deploying to. The connection managers in the solution will at runtime grab the value from the database via the package configurations on the package the connection managers live on.

4) I deploy using dtsutil and make it silent. No need to not have it silent.

data reader source in data flow problem

hi all,

i have a package in ssis that needs to deliver data from outside servers with odbc connection. i have desined the package with dataflow object that includes inside a datareader source. the data reader source connect via ado.net odbc connection to the ouside servers and makes a query like: select * from x where y=? and then i pass the data to my sql server. my question is like the following:

how do i config the datasource reader or the dataflow so it will recognize an input value to my above query? i.e for example:

select * from x where y=5 (5 is a global variable that i have inside the package). i did not see anywhere where can i do it.

please help,

tomer

You can set an expression on the SQLCommand property. Similar to as explained here: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

The difference with the datareader source is that you cannot use a variable. Instead, set the expression on the SQLCommand property via the expressions of the parent data-flow.

-Jamie

|||

hi jamie,

thank you for your advice but i still did not understand completely.

the example u sent me is with ole db data source. lets say i have created the variable

and put there the select. now how do i config the data flow to use it?

can you give me another example?

thx,

Tomer

|||

Like I said, the Datareader Source does not allow you to use a variable as input. So, instead of putting the expression that builds your SQL statement into a variable, put it in an expression that sets the SQLCommand property of your Datareader Source. The interface to setting this expression is in the properties pane of the Data Flow task in which the Datareader Source resides.

-Jamie

|||

hi again Jamie,

i have put there:

"select * from x where y=" + @.[user::max_date]

and i get an error.

can u advice?

|||

Is max_date a Datetime variable? If so you need to cast it as a string in order to be able to concatenate it.

-Jamie

|||

Jamie,

thank you very much for your help - you realy helped me here and saved me lots of time.

tomer

|||

Hi Jamie,

yesterday everyyhing worked fine and suddenly i get in the package VS_BROKEN?

why can u advice me on that please?

tomer

|||

You have made a change in one of your components that means it won't work anymore. It should tell you which component.

-Jamie

|||

it says its failed executing the SQL command at the data reader source and i get validation error VS_BROKEN/ my sql is like yesterday:

"select * from x where y = " + [user::x]

when x is a string type. i must have changed somthing small without noticing...?

|||Are you missing the @. before the variable name (should be "@.[user::x]" ) in your sql command, or is that just a typo in your post?

data not flowing out of flat file source

my package has a flat file source that should be extracting data from a text file passing the data to the next component in the data flow. the package validates fine, but the data isn't flowing. however, i see the data in the source component. i added a data viewer between the source and the next component to see if any data flowed and saw no data. can someone suggest how i should go about trying to debug this? thanks.Look at the output window. Anything of interest there?|||

Could you give more details about the flat file format and how you configured the flat file connection manager?

Thanks.

|||

DarrenSQLIS wrote:

Look at the output window. Anything of interest there?

below are some output lines that interest me:

Information: 0x40043007 at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Data Flow Lockbox Validate File and Header Info, Flat File Lockbox [1]: The processing of file "c:\casestudy\lockbox\samplelockbox.txt" has started.
Information: 0x400490F4 at Data Flow Lockbox Validate File and Header Info, Lookup BankBatchID [373]: component "Lookup BankBatchID" (373) has cached 0 rows.

"Flat File Lockbox [1]" is the flat file source component. the flat file connection manager is connected to file "c:\casestudy\lockbox\samplelockbox.txt"|||

Bob Bojanic wrote:

Could you give more details about the flat file format and how you configured the flat file connection manager?

Thanks.

below is the entire contents of the text file:

H080105 B1239-99Z-99 0058730760
I4001010003 181INTERNAT
C4001010004 01844400
I4002020005 151METROSPOOO1
C4002020006 02331800
I4003030009 MAGIC CYCLES
C4003030010 02697000
I4004040013 LINDELL
C4004040014 02131800
I4005040017 151GMASKI0001
C4005040019 01938800

the general tab of the flat file connection manager is configured as follows:

file name: c:\casestudy\lockbox\samplelockbox.txt
locale: english (united states)
unicode: unchecked
code page: 1252 (ANSI - Latin I)
format: ragged right
text qualifier: <none>
header row delimiter: {CR}{LF}
header rows to skip: 0
column names in first data row: unchecked|||

Can you try this?

In a copy of your package, delete everything after the flat file source.

Add a row count component after the Flat File Source (see http://msdn2.microsoft.com/en-us/library/ms141136(SQL.90).aspx for the row count component)

Now you can run the data flow with nothing but the flat file - if data flows to the row count, then your source is ok.

The reason I ask is becuase I see that your lookup component cached 0 rows - I wonder if that is actually the issue. Lookup row caching occurs on pre-execute so if there is a problem there, your flat file source will never even get started.

Donald Farmer

|||donald,

i took your suggestion and copied my package. then, i deleted everything from the data flow task except a flat file source and a rowcount. i then ran this package and the data still wasn't flowing out of the flat file source. next, i deleted my data flow task and created a new data flow task. then, i added a flat file source and a rowcount to this new data flow. low and behold, that resolved the issue. it seems that my original data flow somehow became corrupted and that this prevented the data from flowing out of the flat file source. to me, this seems to be a bug. is there a way to repair my original data flow so that it won't be necessary for me to duplicate all of my previous work?|||

Well that's odd, for sure. You can select, copy and paste components from data flow to data flow, so you could try copying and pasting the rest of your original data flow into your new one and hooking it up. There will be messages about metadata needing fixed up, but it all is effectively identical it should be relatively easy to do so.

(Copy and back up that new data flow first of course.)

I don't really have any suggestions about what could have gone wrong. I do wonder if the new data flow is identical in all ways to the old one, but difficult to tell without examining them in detail.

Donald

|||

Donald Farmer wrote:

Well that's odd, for sure. You can select, copy and paste components from data flow to data flow, so you could try copying and pasting the rest of your original data flow into your new one and hooking it up. There will be messages about metadata needing fixed up, but it all is effectively identical it should be relatively easy to do so.

(Copy and back up that new data flow first of course.)

I don't really have any suggestions about what could have gone wrong. I do wonder if the new data flow is identical in all ways to the old one, but difficult to tell without examining them in detail.

Donald

donald,

you were correct about there being messages about metadata needing fixed up. below are the messages:

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Data Flow Task [DTS.Pipeline]: input column "line" (158) has lineage ID 28 that was not previously used in the Data Flow task.

Error at Data Flow Task [DTS.Pipeline]: "component "Derived Column Checks 1" (156)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

you previously stated that fixing this should be relatively easy to do so. so, how should i go about fixing this?|||

There should be a warning triangle in the components that need to be fixed up. Double click on those components to open the UI and the metadata may be fixed automatically, or you will be prompted with a mapping dialog to fix up the changes.

Donald

|||

Donald Farmer wrote:

There should be a warning triangle in the components that need to be fixed up. Double click on those components to open the UI and the metadata may be fixed automatically, or you will be prompted with a mapping dialog to fix up the changes.

Donald

ok, that worked. thanks for your assistance.

Data Mart rollbacks

How have folks been managing rollbacks on failures inside SSIS when populating data marts?
For example - we have a seperate package for each dimension table, then a master Fact table update. If one of the dimension table updates fails - how have you rolled back the previous changes in the tables updated prior to the failure - or if the Fact tabel package fails - how do you manage rollback in all the dimension tables?
My first thought was using the Audit table information to determine which tables needed rolled back.
Hello Joe,
What about putting the Tasks (Execute Package tasks) in a transaction?
http://msdn2.microsoft.com/en-us/library/ms137690(SQL.90).aspx

Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com

> How have folks been managing rollbacks on failures inside SSIS when
> populating data marts?
> For example - we have a seperate package for each dimension table,
> then a master Fact table update. If one of the dimension table
> updates fails - how have you rolled back the previous changes in the
> tables updated prior to the failure - or if the Fact tabel package
> fails - how do you manage rollback in all the dimension tables?
> My first thought was using the Audit table information to determine
> which tables needed rolled back.
>
|||Is this what your team would implement?
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:885683c261f8c97ff6e74166f0@.news.microsoft.com ...
> Hello Joe,
> What about putting the Tasks (Execute Package tasks) in a transaction?
> http://msdn2.microsoft.com/en-us/library/ms137690(SQL.90).aspx
>
> --
> Allan Mitchell
> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
> http://www.konesans.com
>
>
|||Hello Joe,
Yes. I would be looking to put things inside of transactions. I may logically
split things up but yes transactions would be the way for me
Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com
[vbcol=seagreen]
> Is this what your team would implement?
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:885683c261f8c97ff6e74166f0@.news.microsoft.com ...
|||the easy way:
backup the DB, execute the ETLs, restore the DB in case of a problem...
in fact, its a big recommendation to always backup first, so there is no overhead here.
"Joe" <hortoristic@.gmail.dot.com> wrote in message news:D6985AF3-799E-4B96-8A87-7A823C9C1FC2@.microsoft.com...
How have folks been managing rollbacks on failures inside SSIS when populating data marts?
For example - we have a seperate package for each dimension table, then a master Fact table update. If one of the dimension table updates fails - how have you rolled back the previous changes in the tables updated prior to the failure - or if the Fact tabel package fails - how do you manage rollback in all the dimension tables?
My first thought was using the Audit table information to determine which tables needed rolled back.

Data Mart rollbacks

How have folks been managing rollbacks on failures inside SSIS when populati
ng data marts?
For example - we have a seperate package for each dimension table, then a ma
ster Fact table update. If one of the dimension table updates fails - how h
ave you rolled back the previous changes in the tables updated prior to the
failure - or if the Fact tabel package fails - how do you manage rollback in
all the dimension tables?
My first thought was using the Audit table information to determine which ta
bles needed rolled back.Hello Joe,
What about putting the Tasks (Execute Package tasks) in a transaction?
http://msdn2.microsoft.com/en-us/library/ms137690(SQL.90).aspx
Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com

> How have folks been managing rollbacks on failures inside SSIS when
> populating data marts?
> For example - we have a seperate package for each dimension table,
> then a master Fact table update. If one of the dimension table
> updates fails - how have you rolled back the previous changes in the
> tables updated prior to the failure - or if the Fact tabel package
> fails - how do you manage rollback in all the dimension tables?
> My first thought was using the Audit table information to determine
> which tables needed rolled back.
>|||Is this what your team would implement?
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:885683c261f8c97ff6e74166f0@.news.microsoft.com...
> Hello Joe,
> What about putting the Tasks (Execute Package tasks) in a transaction?
> http://msdn2.microsoft.com/en-us/library/ms137690(SQL.90).aspx
>
> --
> Allan Mitchell
> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
> http://www.konesans.com
>
>|||Hello Joe,
Yes. I would be looking to put things inside of transactions. I may logica
lly
split things up but yes transactions would be the way for me
--
Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com
[vbcol=seagreen]
> Is this what your team would implement?
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:885683c261f8c97ff6e74166f0@.news.microsoft.com...
>|||the easy way:
backup the DB, execute the ETLs, restore the DB in case of a problem...
in fact, its a big recommendation to always backup first, so there is no ove
rhead here.
"Joe" <hortoristic@.gmail.dot.com> wrote in message news:D6985AF3-799E-4B96-8
A87-7A823C9C1FC2@.microsoft.com...
How have folks been managing rollbacks on failures inside SSIS when populati
ng data marts?
For example - we have a seperate package for each dimension table, then a ma
ster Fact table update. If one of the dimension table updates fails - how h
ave you rolled back the previous changes in the tables updated prior to the
failure - or if the Fact tabel package fails - how do you manage rollback in
all the dimension tables?
My first thought was using the Audit table information to determine which ta
bles needed rolled back.

Data flow task to delete records and then insert records in transaction

HI,

I have been trying to solve the locking problem from past couple of days. Please help mee!!

Scenario:
--
I have a SSIS package in which 2 data flow tasks. 1st data flow task deletes records from a 5 tables and the 2nd data flow task should insert records into 1 of the five tables after the success of 1st data flow task. This scenario runs in Transacation.

The above scenrio in the 2nd data flow task hangs in runtime. It does not complete. with sp_who2 command i could see that there is an intent share lock(LK_M_IS) on the table and the status is SUSPENDED.

I dont know how to come out of this locking. Please help.

Thanks ,
SunilTry setting RetainSameConnection to TRUE on the connection manager.

|||It was already set to TRUE. Please help meeee..

|||Based on some other threads about similar issues, this may not be SSIS, but related to DTC and the core relational engine. You might try checking some of the other forums to see if they have any suggestions.

|||

Have you tried using a SQL Task (set based) for the deletes followed by your Insert Data Flow Task?

Larry

|||

Hey Larry,

Yes. I did use it.but still the blocking happens. Please suggest

Thanks,

Sunil

Data Flow Task SQL strings

Hi,

I just wanna ask:

I'm creating an SSIS package, a Data Flow Task. I have used OLEDB Source connected to a SQL Server Destination. Now in my OLEDB Source, I have this SQL statement

SELECT FirstName, LastName, Age FROM Employees WHERE (Age > 10) AND (Age < 95)

But what I want is to have the last name and first name concatenated and in proper case(capitalize first letter of the firstname and surname). I also want to TRIM or remove the blank spaces of the field in my SQL statement. How I be able to do this?

I tried using proper(), trim() and ucase() like in MSAccess but no success.

Please help. Thanks in advance.

The OLE DB Source has to use the same syntax as used by the underlying DB engine - in this case SQL Server.

Have a look in BOL for RTRIM(), LTRIM(), SUBSTRING(), UPPER() LEFT(), REPLACE() to get you going.

Alternatively you could carry out this work in the SSIS data-flow using a Derived Column component.

-Jamie

|||Thanks for your reply. I was able to do it using Derived Column. Thanks again and more power...

Data flow task running very slow

Hello,

I developed an SSIS package doing a nightly load into a data warehouse. We have an 8 hour loading window - currently the package takes 16 hours to complete.

I isolated the problem to a Data Flow task where +-35% of the time is spent. This task is pretty straight forward:

- OLE DB source, reading +- 800,000 rows from a SQL server database

- 13 Lookups in sequence, to get surrogate keys from dimension tables. Lookups are all on GUIDS.

- An aggregation

- OLEDB target, fact table in a SQL server database.

It seems unreasonable for the this task to take over 5 hours. It spends the majority of time on the lookups - not so much at target, source and aggregation.

Any comments and advice will be greatly appreciated.

Thanks.

(PS some machine details:

OS Name Microsoft(R) Windows(R) Server 2003, Standard Edition
Version 5.2.3790 Service Pack 1 Build 3790
Other OS Description Not Available
OS Manufacturer Microsoft Corporation
System Name ARK-SQL
System Manufacturer HP
System Model ProLiant DL380 G5
System Type X86-based PC
Processor x86 Family 6 Model 15 Stepping 6 GenuineIntel ~1866 Mhz
Processor x86 Family 6 Model 15 Stepping 6 GenuineIntel ~1866 Mhz
BIOS Version/Date HP P56, 9/18/2006
SMBIOS Version 2.3
Windows Directory C:\WINDOWS
System Directory C:\WINDOWS\system32
Boot Device \Device\HarddiskVolume1
Locale United States
Hardware Abstraction Layer Version = "5.2.3790.1830 (srv03_sp1_rtm.050324-1447)"
User Name Not Available
Time Zone South Africa Standard Time
Total Physical Memory 3,327.30 MB
Available Physical Memory 938.20 MB
Total Virtual Memory 1.10 GB
Available Virtual Memory 2.78 GB
Page File Space 2.00 GB
Page File C:\pagefile.sys)

How many rows are the lookups caching? Are you selecting the whole table (All columns) or are you using a SQL query to specify only the columns you need.

Are you sure it is not your dest that is slow receiving the rows?
If it is, SSIS will slow down the rows retrieved from the source making it appear as if it is something else slowing it down.

Check the lookups. Are as few columns as possible being selected.
Push your rows into nothing such as a Konesans Trash Destination. Does it appear faster?

Also, if your lookup is caching alot of rows and the key you are caching is a GUID, that's a chunk of work to do.|||

You need to find where the bottleneck is. You could start measuring how fast the dataflow 'reads from source'; then how fast it does the transformations (since you have a fair amount of transformations, I would measure a several points); and finally measure the how fast it writes into the destination.

few tips:

The lookups could slow down performance if you are using partial cache. So, limit the number of columns and rows (by providing a select statement with a where clause if possible) and use full cache mode. This approach could generate another problem if memory in server is limited.

See if you can move the aggregation upstream and or limit the number of columns to be used as 'group by'. In general aggregations will perform better if the number of columns with fewer columns in the 'group by'.

If you use an OLE DB destination, try to stick with 'fast load' and set the interval commit to an acceptable range

Use DB profiler tools to measure the performance of each SQL statement use in the data flow (OLE DB source, lookups, etc)

This white paper has some other information

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx|||

I would also highly recommend taking a read of this:

Donald Farmer's Technet webcast

(http://blogs.conchango.com/jamiethomson/archive/2006/06/14/SSIS_3A00_-Donald-Farmer_2700_s-Technet-webcast.aspx)

-Jamie

|||

Hi Crispin,

Thanks for your response.

I solved the problem last night. The key was in the caching.

I modify the SQL statement in most of my lookups to handle inferred fact entries. I.e. a sale against an unknown customer gets linked to the 'UNKNOWN CUTOMER' dimension table entry. It seems that SSIS sets the caching to partial when you modify the lookup SQL. I assume the lookup had to go back to disk quite often and therefore high cost for big dimension tables (...of which I have quite a few).

I am achieving a +-500% performance increase by keeping the Lookup transform as it is and by setting the cahcing to full. I treat the inferred fact entries at a later stage in stead of at the point of lookup.

|||Good to hear it has approved, however...

The lookup does not change to partial cache when you specify a statement. Not sure why you say it has done this. Was the checkbox checked?

On the partial cache thing, you are correct. The lookup will check it's cache, if the key is found, use it, else run the query against SQL. This can become dog slow. Only time it has been a good thing, for me at least, is when you know your facts are going to be a very small portion of a really large dimension. That way, the first few queries are slow but you don't have to cache 13 million rows only to use 1000.

Data flow task multiple destinations

Hi,

The further i get with doing my current SSIS package the more i am starting to wonder about best practices and performance.

My current package loops through CSV files in a specified location and extracts events from these files. Each file contains multiple events which are a mixture of different types. Depending on the event there are a different number of comma seperated values. In the package i firstly set each event to one column seperated by a comma delimeter. I then create an array for the event which is split by the delimeter. In a script i weed out all elements of the array that are common to all events and set the remaining events to another array. After some processing i come to my conditional split transformation which splits the processing of each event based on the EventID. This is where i'm having doubts on whether i have approched the package correctly. There are approximately 60 different events so each one of these has a seperate pipeline to process the remaining parameters in the array and output them to the destination table. The destination table is differnet for each ID. Is it viable to have this amount conditions and paths when creating the pacakge and is this likely to have any detrimental effect on performance. Is there possibly another way that i could approach this problem?

Many thanks, i hope that made sense.

Grant

Grant Swan wrote:

Hi,

The further i get with doing my current SSIS package the more i am starting to wonder about best practices and performance.

My current package loops through CSV files in a specified location and extracts events from these files. Each file contains multiple events which are a mixture of different types. Depending on the event there are a different number of comma seperated values. In the package i firstly set each event to one column seperated by a comma delimeter. I then create an array for the event which is split by the delimeter. In a script i weed out all elements of the array that are common to all events and set the remaining events to another array. After some processing i come to my conditional split transformation which splits the processing of each event based on the EventID. This is where i'm having doubts on whether i have approched the package correctly. There are approximately 60 different events so each one of these has a seperate pipeline to process the remaining parameters in the array and output them to the destination table. The destination table is differnet for each ID. Is it viable to have this amount conditions and paths when creating the pacakge and is this likely to have any detrimental effect on performance. Is there possibly another way that i could approach this problem?

Many thanks, i hope that made sense.

Grant

Grant,

I have to be honest, it sounds like a very good approach. Importing as a single column and then splitting within the pipeline is definately a good idea.

Granted, 60 data-paths is alot but that does not mean the SSIS pipeline cannot handle them so yes, I do think it is viable. In terms of performance, well yes, of course as the number of paths increases then SSIS has more work to do and this will be detrimental to the time taken to execute - but that's not the same as being detrimental to perrformance. You're not going to get away from an increase in execution time whatever you solution may be, regardless of whether you use SSIS or not. Whether the performance is acceptable or not is entirely down to your discretion and the determining factors are:

Amount of data|||Hi Jamie,

Many thanks for this. It has put things in perspective. I still believe this solution at its current state to be quicker than the existing stored procedure which consists of a hell of a lot of nested if statements. The goal is to speed up this process as we are constantly getting these CSV files on a daily basis (approx 2000 a day) and the current methods are showing limitations.

I'll hopefully get some throughput and performance stats once i have the package completed and would be happy to let you cast your eye over them.

On a lightly differnet note. I am aware that when i process the remaining parameters there may be times where the event row will cause errors. Instead of using a row redirect and setting up the same destination multiple times would i be correct in saying that the redirects should point to a Union all transformation which then can input them all to one error destination table? Thinking about this would it be possible to have multiple inputs into a script which formalises all the events so that they can be directed to and error table?

Thanks,

Grant|||

Grant Swan wrote:

Hi Jamie,

Many thanks for this. It has put things in perspective. I still believe this solution at its current state to be quicker than the existing stored procedure which consists of a hell of a lot of nested if statements. The goal is to speed up this process as we are constantly getting these CSV files on a daily basis (approx 2000 a day) and the current methods are showing limitations.

I'll hopefully get some throughput and performance stats once i have the package completed and would be happy to let you cast your eye over them.

If resources start to get squeezed on performance then perhaps your conditional split could push EventIDs 1-30 into a raw file and EventIDs 31-60 into a second raw file. You can then process each "group" in isolation in seperate data-flows - thereby splitting out the processing. Variations on this these (i.e. more groups) are of course an option :)

As always the way to find the optimum solution is test and measure test and measure test and measure!!!

Grant Swan wrote:

On a lightly differnet note. I am aware that when i process the remaining parameters there may be times where the event row will cause errors. Instead of using a row redirect and setting up the same destination multiple times would i be correct in saying that the redirects should point to a Union all transformation which then can input them all to one error destination table? Thinking about this would it be possible to have multiple inputs into a script which formalises all the events so that they can be directed to and error table?

Yeah I think they should point to a UNION ALL. Maybe you could push that data into a raw file (which is stupendously quick compared to a flat file or DB table) and then push the contents of the raw file to wherever you want it in a seperate data-flow. Again, it would be an interesting test to find out. I dread to think what your data-flow will look like with 120 data paths in it though

-Jamie

|||Jamie,

Yeah I

think they should point to a UNION ALL. Maybe you could push that data

into a raw file (which is stupendously quick compared to a flat file or

DB table) and then push the contents of the raw file to wherever you

want it in a seperate data-flow. Again, it would be an interesting test

to find out. I dread to think what your data-flow will look like with

120 data paths in it though

It looks bad enough with nearly 60 :)
Need to look at the layout and set it out a bit better i think.

Thanks again,

Grant|||Jamie,

I've been thinking and believe i have come up with possibly a better approach to this problem. I've found that error re-direction from a script component isn't just a simple matter of dragging the red path to a seperate output although if you could help on how to do (as a seperate question) this i'd be interested in finding out.

Anyway, i believe i can get rid of all script tasks following the conditioanl split and just having one before the split. In this i would then split up my comma delimited list into generic parameter variables. Each event in the conditional split would then use only specific parameters from the array. This will work as long as i use a fixed length array with empty strings where no parameters exist. The problem i think i will encounter with this method is that i still need to convert the parameter data from strings into the destination table data type. I assume for this i should be able to use the data conversion task in place of the script? will the data conversion task allow me to do complex conversions that might involve changing a hex value in the source data to a integer value for the destination.

Any help or opinions on this would be appreciated.

Thanks,

Grant|||

Complicated conversions like that are more readily achieved in a Derived Column component or a script component.

-Jamie

|||Thanks Jamie,

Thats what i thought. At least with the Derived columns and the data conversion transformations i can use the error redirect as soon as a problem arises. As i mentioned the script task seems to be sadly missing this functionality for what ever reason.

Grant