SQL SSIS - Connection Managers : How to create connection string dynamically or passing from config file ?

first create a string variable, in package scope, name it as FileName
then right click on flat file connection manager, select properties,
then in properties window, select expressions
then click on ellipsis button, and "property expression editor"
select "connection string" from property section,
and set User::FileName in expression section

NOTE THAT YOU SHOULD SET A DEFAULT VALID PATH FOR VARIABLE VALUE.

then you can use dtexec or sql server job step to set variable value dynamically from outside of package.

Thanks,

Under Expression Builder for the Variable ( User Defined )

It is expecting Expression

And when i gave the file path as

c:\Folder\File.txt
(Or)
\\ServerName\File.txt

it is not accepting.

And also the same with Destination File.

When i tried give the connection string as follows ..

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
SourceFile.JPG

you should just set variable as expression, like:
@[User::SourceFile]

this will be enough, now set default value, in variable definition in variable window

Okay, Thanks ..!

Now

How programmatically, we can pass the connection string ? Where should i mention the actual connection string ..?

For Source file - Flat File :-

From the Application - We have a Browse Window.

File Path can differ by selecting the Folder Option.

you can use this syntax for set variables value from .NET CODE:

string pkgLocation;
Package pkg;
Microsoft.SqlServer.Dts.Runtime.Application app;
DTSExecResult pkgResults;
pkgLocation =
@"C:\SSISProgrammatically\Package.dtsx";
app = new Microsoft.SqlServer.Dts.Runtime.Application();
pkg = app.LoadPackage(pkgLocation, null);

pkg.Variables["yourName"].Value = "Dennis MacAlistair Ritchie";
pkg.Variables["yourAge"].Value = 66;

pkgResults = pkg.Execute();

reference:
http://www.dotnetking.com/TechnicalComments.aspx?LogID=321

reza_rad :- Thanks

Here i am creating the SSIS Package from VS 2008.

How should i call the package ..! It is located as Seperate Project.

Please see the Screen Shot
SSIS-Project.JPG

look at my previous post, you just need to ADDRESS the dtsx file in the code provided
does it make sense to you?

Okay.



pkg.Variables["SourceConn"].Value = "c:\Folder\Test1.txt";    
pkg.Variables["DestinationConn"].Value = "Data Source=Server;Initial Catalog=TempDB;User ID=sa;Password=chokka;";

also set Packagelocation with the address of dtsx file

I am facing some assembly reference - missing dll's

On Package , DTSExecResult and DTS.

Package pkg;
        Microsoft.SqlServer.Dts.Runtime.Application app;
        DTSExecResult pkgResults;
        pkgLocation = @"C:\Import340B\Import340B\340Price.dtsx";
        app = new Microsoft.SqlServer.Dts.Runtime.Application();

Actual Code

protected void Button3_Click(object sender, EventArgs e)
    {
        string pkgLocation;
        Package pkg;
        Microsoft.SqlServer.Dts.Runtime.Application app;
        DTSExecResult pkgResults;
        pkgLocation = @"C:\Import340B\Import340B\340Price.dtsx";
        app = new Microsoft.SqlServer.Dts.Runtime.Application();
        pkg = app.LoadPackage(pkgLocation, null);

        pkg.Variables["SourceConn"].Value = "Dennis MacAlistair Ritchie";
        pkg.Variables["DestinationConn"].Value = 66;

        pkgResults = pkg.Execute();

    }

Select allOpen in new window

Error 1

Error      1      The type or namespace name 'Package' could not be found (are you missing a using directive or an assembly reference?)      C:\Test\Default.aspx.cs      80      9      C:\Test\

Error      2      The type or namespace name 'Dts' does not exist in the namespace 'Microsoft.SqlServer' (are you missing an assembly reference?)      C:\Test\Default.aspx.cs      81      29      C:\Test\


Error      3      The type or namespace name 'DTSExecResult' could not be found (are you missing a using directive or an assembly reference?)      C:\Test\Default.aspx.cs      82      9      C:\Test\


Error      4      The type or namespace name 'Dts' does not exist in the namespace 'Microsoft.SqlServer' (are you missing an assembly reference?)      C:\Test\Default.aspx.cs      84      39      C:\Test\

protected void Button3_Click(object sender, EventArgs e)
    {
        string pkgLocation;
        Package pkg;
        Microsoft.SqlServer.Dts.Runtime.Application app;
        DTSExecResult pkgResults;
        pkgLocation = @"C:\Import340B\Import340B\340Price.dtsx";
        app = new Microsoft.SqlServer.Dts.Runtime.Application();
        pkg = app.LoadPackage(pkgLocation, null);

        string sSourceFile = System.Configuration.ConfigurationManager.AppSettings["340BPrice"];
        string sDestFile = System.Configuration.ConfigurationManager.AppSettings["LocalDB"];

        pkg.Variables["SourceConn"].Value = sSourceFile;
        pkg.Variables["DestinationConn"].Value = sDestFile;

        pkgResults = pkg.Execute();

    }

Select allOpen in new window

I reduced two errors by adding Dll's

Microsoft.SqlServer.ManagedDTS

 Microsoft.SqlServer.Dts.Runtime

But still getting other two errors ..

Error      1      The type or namespace name 'Package' could not be found (are you missing a using directive or an assembly reference?)      C:\Test\Default.aspx.cs      82      9      C:\Test\


Error      2      The type or namespace name 'DTSExecResult' could not be found (are you missing a using directive or an assembly reference?)      C:\Test\Default.aspx.cs      84      9      C:\Test\

add this reference:
Microsoft.SQLServer.ManagedDTS

On the Project menu, click Add Reference and add a reference to Microsoft.SqlServer.ManagedDTS.dll. Click OK.


Use the Visual Basic Imports statement or the C# using statement to import the Microsoft.SqlServer.Dts.Runtime namespace.

add this using:
using Microsoft.SqlServer.Dts.Runtime;

All Dll's are added.

Application is working Perfectly - If i am not adding any Variables.

But on adding variables and assigning the value to the Variable, I am facing this error.


This is what i am doing ..!

I have already defined Source Connection and Destination Connection String.

To which when i am assigning a variable.

TITLE: Package Validation Error
------------------------------

Package Validation Error

------------------------------
ADDITIONAL INFORMATION:

Error at Data Flow Task [SQL Server Destination [9]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "FORMER.MedExpress.sa" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Error at Data Flow Task [SSIS.Pipeline]: component "SQL Server Destination" (9) failed validation and returned error code 0xC020801C.

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

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

Error at 340Price [Connection manager "FORMER.MedExpress.sa"]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft OLE DB Provider for ODBC Drivers"  Hresult: 0x80004005  Description: "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".

 (Microsoft.DataTransformationServices.VsIntegration)

------------------------------
BUTTONS:

OK
------------------------------

Select allOpen in new window

This is the error, i am receiving ..!
Error2.JPG

could you upload your package here?, I think there should be something wrong there.

reza_rad

Yes you are right , there is something wrong.

1) My Package is working fine - If i dont keep variable.

If i keep the variable, then Package throws exception.

Reason is that, I am not sure where i need to keep the variable either in Data Flow or Connection Manager.

****

Eventually i am expecting variables as connection string.

Source Connection Variable  should be like this " C:\Folder Name\FlatFile.txt"

Destination Connection Variable  should be like this "Data Source=ServerName;Initial Catalog=DBName;User ID=sa;Password=chokka;""

*************************

Please change the extension of the Package - Remove .txt

BulkImportRx30.dtsx.txt

reza_rad : I think without Source File and Destination File - You will not be able to Test it.

If possible by you ,please join with me by remotely ..!

you didn't defined any variable.

if you want to use variables, you should create them,
go to control flow tab, right click on a blank area, select variables
in the variables window, create new variables as you want.

in your case you should create two variables:
one for flat file source,
another for destination,

after creating variable,
right click on each connection manager , select properties, then click on ellipsis button front of EXPRESSION property,
in the expression builder window, set ConnectionString property with appropriate variable.
look at attachments, which is for Destination Connection for example

1.jpg
2.JPG

Package which i attached .. i have not defined any Variable.

I did so, because i was facing Error which i posted.

>>If possible by you ,please join with me by remotely ..!<<
mail your information to me : a dot raad dot g at gmail dot com

Source File :- Flat Text File.

Destination :- You can create a SampleDB and TestTable - Where you can import the Flat File data.

1) In this DTSX Package you can view the Source Connection and Destination Connection.

 I kept the actual connection in the package.

Please keep SourceVariable and DestinationVariable and help us to write a Program to call the package from Code.
340Price.dtsx.txt
340bprice.txt

I am sorry for late response. I was away from my work for a long.

From  my Comment ID: 34213342

We have Flat File ( Source File) which has to be imported to SQL Database.

From Comment ID: 34220720

I didnt see any changes.

I think, the best way would be recreate a Package which does import from Flat file to SQL Database by keeping variables for Connection String.

Probably you might have attached same old file in the Comment ID: 34220720

long time passed from last comment, and I don't remember what changes I made in the package,
But I remember that your package didn't worked, and after my changes it works successfully on my side.
maybe problem was in variable definitions.

I am sorry for long delay. I had flu infection and have to be away from work.

From ID: 34398603

My Package will not work on your computer, as because Source File Path and Destination File ( Connection String ) are Hard Coded. Configuration is made as per my Computer ( File Folder / File Path ) and SQL Connection String.

I am trying to get connection string in dynamic variable format. So that once i received the package, i can sent the connection string from C#.

pkg.Variables["SourceConn"].Value = sSourceFile;
pkg.Variables["DestinationConn"].Value = sDestFile;