Fast load option disappears when using expression for connection string
-
Hi all,
I've got a package that I want to deploy to several different servers so I thought the best solution was to create an expression for the connection string for my connection managers - the local variables are read from a configuration file and these variables are used in the expression.
All fine and dandy so far!
One of the operation is to take data from one table and copy it into another database, but I want to preserve the IDENTITY field. The OLE DB destination adapter has options for Table or view (fast load) with a 'Keep Identity' option which does what I want.......but if use the expression for the connection string the fast load option disappears and therefore my identity solution!!!
Anyone know why this is and how do I get round it?
Many thanks.
-
I use configurations on my connection managers all the time, and the fast load option never dissappears.
It sounds really strange to me, as the OLE DB Destination and the connection manager are two different objects.
Can you describe more in detail what you mean with "dissappear"? Or post a screenshot?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
I have also used them many times without any issues, what version of SSIS are we dealing with?
-
If I use a connection that doesn't have an expression I get this:
If I use an expression for the connectionstring I get these options:
One weird things is the icons used for the connections - don't know if this has any bearing:
Let me know if you need any other information.
Thanks.
-
I'm using SQL Server 2008
-
The icon is actually good. It is a notification from BIDSHelper to indicate that the connection manager is configured by an expression.
Can you post the expression here?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
"Data Source="+ @[User::VarHQServer] + ";Initial Catalog="+ @[User::VarHQDatabase] +";User ID="+ @[User::VarHQUserName] +
";password="+ @[User::VarHQPassword] + ";Persist Security Info=True; Provider=SQLNCLI10.1;Auto Translate=False;"
-
At first sight there isn't anything wrong with that expression.
The only difference that I say between those two OLE DB Destinations is the table name.
In the first it uses square brackets [], in the second one double quotes "".
Why is this?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
I don't know the answer to that one - it's what's in the dropdown list.
With the 'no-expression' everything is [] and in the expression version it's all quoted!
Very strange!
-
Are there any other expressions? Maybe on the OLE DB Destination itself?
Are you sure the second Destination is an OLE DB Destination? And you are using an OLE DB connection manager? (so not ADO.NET or something else)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
There are no other expressions being used and as you can see in the screenshot, they're both OLE DB destinations.
Both connections in the connection manager have a type of OLEDB.
-
Do both connection managers use the same username and password?
because , from memory, a fast-load can require special permissions to run such as ALTER TABLE to keep the identity
-
Yes, they're both using the 'sa' username and password.
-
If you go to the OLE DB Destination properties (F4), can you set the AccesMode property to "OpenRowset using Fast Load"?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Woo-hoo....you da man :cool:!!
Seriously though, many thanks for taking the trouble to go through my pain 🙂
I wonder why it'll work that way but not through the way you'd expect?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply