SSIS Variables

Question

SSIS Variables

Whitmoj

SSC Enthusiast

Points: 139
More actions
November 4, 2014 at 4:54 am

#312681

Hi All
I am creating an SSIS with variables for file name and path. The file is an excel, I have done this with csv and works a treat. I am trying to set the path and extension by using the variables that the foreach loop creates and I just keep getting error's can any one help.
Kind Regards
James

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

What are the errors?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Whitmoj SSC Enthusiast Points: 139 More actions · Answer 2

No real errors it just wont connect and when I run's it it comes back with VS_ISBROKEN which I know is incorrect because the package works if I point it to the excel. All I am wanting to do is make the path use the variable.

Kind Regards

James

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

How did you configure the expressions on the Excel connection manager?

Did you set DelayValidation to true?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Whitmoj SSC Enthusiast Points: 139 More actions · Answer 4

No I hadn't but there is still an issue as it can not connect. I have set the Retain Same Connection to true. It's can't find the excel.

Kind Regards

James

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 5

How did you configure the expressions on the Excel connection manager?

Can you post all messages from the output window?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Whitmoj SSC Enthusiast Points: 139 More actions · Answer 6

This is the error I'm getting now.

[Excel Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "\\myhsfp02b\data$\RTT_Validation_Team\DQ Exception Reports\Exceptions\Automated Exception Reports\Active Waiters Unable to Link To Periods PW.xls" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

I have a similar job that does exactly the same thing it's just that the input is a CSV and this works wonders I can have multiple csv's with the same name and it loops through. But I can not replicate this to work with an excel.

Kind Regards

James

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 7

There are no other errors?

There may be error messages posted before this with more information on why the AcquireConnection method call failed

How did you configure the expressions on the Excel connection manager?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Whitmoj SSC Enthusiast Points: 139 More actions · Answer 8

There are no other errors.

That's the bit I'm falling down on.

I have set my expressions as follows

ExcelFilePath = @[User::ReportPath]

Name = @[User::ActiveWaiter]

Hope that is what you mean

Kind Regards

James

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 9

What are the values of those variables?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Whitmoj SSC Enthusiast Points: 139 More actions · Answer 10

The report path is the full path to the drive and folder the excel sits in.

and the ActiveWaiters is AWLS which the foreach loop applies

Kind Regards

James

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 11

AWLS = advanced wilderness life support?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Chris Hurlbut SSCarpal Tunnel Points: 4162 More actions · Answer 12

Don't know if this is the cause but when you are working with excel go to the Project Properties--->Debugging and set Run64BitRunTime to False

MMartin1 One Orange Chip Points: 27799 More actions · Answer 13

Chris Hurlbut (11/5/2014)
Don't know if this is the cause but when you are working with excel go to the Project Properties--->Debugging and set Run64BitRunTime to False

True...

What version of Excel are you using? Have you tried Chris's suggestion?

----------------------------------------------------

crmitchell SSCarpal Tunnel Points: 4557 More actions · Answer 14

Does the account being used by the SSIS package have access to this network share and folder under it? Note also that as this is a UNC share I believe it will need to be a domain rather than a local account.