Can't I dynamically read from any file? - Connection Manager does not exist

I have a Parent package and a child package.

I have hard coded the value for the User::FilePath variable in the parent package.

I am mapping this variable to the value of the same variable in the Child Package.

I created a directory and sql file: "C:\temp\sqlb.sql". I have verified that the path variable value is passed to the child package by using a Script task with a messgbox call.

How do I define an execute sql task to execute sql file: @.[User::FilePath] & "sqlb.sql". I'm using this expression for the SqlStatementSource property. I have entered the OLEDB server information and specified the SQLSourceType = fileconnection.

However I get the error:

[Execute SQL Task] Error: Connection manager "D:\rlewisdev\ZZ\sqlb.sql" does not exist.

What am I doing wrong?

SQLStatementSource in this case points to a connection manager, not a file. You'll have to build a connection manager, call it "SQLFile" or something and use that as the SQLStatementSource. Then you'll have to change the ConnectionString of the connection manager named "SQLFile" (or whatever you decide to call it.)|||

Hi boston..,

You have to create a connection manager based on file path,

you can do the following steps:

1-create connection manager myfilepath

2-right click on this connection and click properties

3-in properties click on the button ... behind expression property

4-In property expression editor ,select the connection string property, and click button ... in the right column

5-In expression builder window expand the variable tree and select ur path variable and drag it to the expression container.

6-click on evaluate expression

NB: you can save your configuration setting in xml file or regsitry or sql server from "package configuration wizard".

Thanks,

Tarek Ghazali

SQL Server MVP

http://www.sqlmvp.com