Passing connection string from C# to SSIS Package ..!

From C# Code, I am executing the SSIS Package ..! I have to pass source file (.csv) and destination connection ( sql database ) from C# to SSIS as Input Parameters ..!

In SSIS, i have a simple Data Flow Task in which it has Flat File Source connected to OLEDB Destination File.

How should i pass Dynamic Connection string to SSIS Package ..!
chokkaStudentAsked:
Who is Participating?
 
PedroCGDConnect With a Mentor Commented:
Build the connection in connection managers as expression and set DelayValidation=TRUE, and in the expression use variables like server, database name and other parameters you want!
Helped?
0
 
radcaesarCommented:
Dont you have config file for that SSIS package?

Create a config file for that package and store the connection string in that file. Why did you pass that from C# code?

In future, If needed, you can just Edit that config file.

http://vyaskn.tripod.com/sql_server_2005_making_ssis_packages_portable.htm
0
 
chokkaStudentAuthor Commented:
radcaesar :-

1) C# Application gives Source File which is .csv file. This File path is often dynamic. It can be from C:\ or D:\

2) Destination File - SQL Server is mentioned in the Config file. This config file is located along with C# Application.

Due to our requirement, i am skeptical about config file in SSIS Package ..!
string strSourceConn = @"c:\Documents and Settings\C\Desktop\File to Place Order\FEB  2011 ORDER 1.csv";
            string strDestConn = System.Configuration.ConfigurationManager.AppSettings["SQLDB"];

            string pkgLocation = @"C:\Package1.dtsx";
            

            Package pkg;
            Microsoft.SqlServer.Dts.Runtime.Application app;
            DTSExecResult pkgResults;

           app = new Microsoft.SqlServer.Dts.Runtime.Application();
            pkg = app.LoadPackage(pkgLocation, null);
//pkg.Variables["strSourceFile"].Value = strSourceConn;  
 //pkg.Variables["dConn"].Value = strDestConn; 
                    
            pkgResults = pkg.Execute();
            Response.Write(pkg.ExecutionResult);

Open in new window

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
chokkaStudentAuthor Commented:
PedroGCD :

I did your first step : Build the connection in connection managers as expression and set DelayValidation=TRUE

In the second step :- I have declared the variables for ServerName,Database,UserName,Password.

Destination Conn : "Data Source=Server;Initial Catalog=SampleDB;User ID=sa;Password=sa123;"
Source Conn : "C:\File.csv"

What would be the SQL SSIS Connection String Variable Expression ??
0
 
chokkaStudentAuthor Commented:
From this website : http://sqlrs.blogspot.com/2006/03/using-expression-variables-in-ssis.html

"Provider=SQLNCLI;Server=" + @[User::SS_DST_SERVER] +";Database=" + @[User::SS_DST_DATABASE] + ";UID=sa;PWD=asdasd;"


I want  to set this Configuration Setting  ..! Where i have to mention this syntax ???
0
 
chokkaStudentAuthor Commented:
From Comment ID: 35434405

I am able to achieve partially.

This link helps me  http://sqlrs.blogspot.com/2006/03/using-expression-variables-in-ssis.html

To declare Database Name, Server, User Name. ..!

     pkg.Variables["dDatabaseName"].Value = "SQLDB";
            pkg.Variables["SS_DST_Server"].Value = "Server";
            pkg.Variables["dUserName"].Value = "sa";

But in SQL Connection String, How to mention the Password for Connection String ?

Second Question is about Source File which is Flat File - .csv ..!

How to set variable for Source File and pass file path .. ?
0
 
rmm2001Commented:
You should be able to say pkg.Connections[YourConnectionManagerName].Properties[ConnectionString] to refer to the connection string directly
0
 
Alpesh PatelAssistant ConsultantCommented:
There is 3 way you can pass value from system to SSIS

1. Environment variable
2. SQL Server (in Table)
3. Config file

In your case Config file is the best way to get result

Create config file and put parameters in it (Node and its values)
Write code to Update config file in C#.

In SSIS configure package from Package & Configuration menu. Use Config file and map config file node values to SSIS local variable.

Create Connection string dynamiclly using expression.


After doing this, when you call the SSIS package from C# it will create connection dynamically from config file.
0
 
PedroCGDCommented:
"
Second Question is about Source File which is Flat File - .csv ..!

How to set variable for Source File and pass file path .. ?"

Pass as expression in the conenction manager!
Let me know more feedback
0
 
chokkaStudentAuthor Commented:
rmm2001 / Comment Id : 35437591

I am using this syntax :-

pkg.Connections["sConnectionString"].ConnectionString = @"C:\File.csv";

User::sConnectionString is defined as Variable for ConnectionString

The syntax throws exception ..! Unable to find the variable sConnectionString
0
 
chokkaStudentAuthor Commented:

ID: 35439355 / PetroGCD

In the Destination File (i.e SQL ) I mentioned that i am able to succeed ..

     pkg.Variables["dDatabaseName"].Value = "SQLDB";
            pkg.Variables["SS_DST_Server"].Value = "Server";
            pkg.Variables["dUserName"].Value = "sa";

When i am changing the server, i am changing the SQL Password also ..! So, How should i sent the password along with these syntax ..!
0
 
rmm2001Connect With a Mentor Commented:
Try it like this: pkg.Connections[YourConnectionManagerName].Properties[ConnectionString] to refer to the connection string directly

You just pass in the name of the connection manager and it applies the connection string property to it. You don't need to specify a variable for the connection string in the package. So go ahead and remove that expression from the connection manager and just pass in the connection string.

This is all assuming that you have the actual connection string and not the file. SSIS won't know to open up the file to look for the conn string or not.

Another approach you could take:

Assumptions: You have the file with the connection string in it.

1) Keep your cConnectionString variable
2) Create a variable to hold the file path of where the conn string is stored
e) Script task to read the file contents and assign that to a variable. The file path is passed in as the #2 parameter

Now your conn string will be assigned to the variable

3) Assign that variable to the connection manager (like you're doing now).

So you're just adding a new step to read the connection string in to a variable and read it there.

Give that a shot and see what happens!
0
 
chokkaStudentAuthor Commented:
ID: 35439355 / PetroGCD

For the Source File :- .csv

What would be the Connection Expression ..

pkg.Connections["sConnectionString"].ConnectionString = @"C:\File.csv";

Like, It can be C Drive or D Drive ..! I dont have sample ConnectionExpression for Flat File

0
 
rmm2001Commented:
RE: 35441061

You don't need to pass in each part of the connection string separately - you can just pass in the entire thing and SSIS will know what to do with it
0
 
chokkaStudentAuthor Commented:

Please clarify your Comment Id : 35441067

Req :

SourceFile :- c:\File.csv
Destination :-  "Data Source=Server;Initial Catalog=DB;User ID=sa;Password=chokka;"

I kept the syntax

pkg.Connections[sConnectionString].Properties[ConnectionString]  = @"C:\File.csv"

Variable defined in the ConnectionManager of SourceFile is "sConnectionString"
Variable defined in the ConnectionManager of DestinationFile is "dConnectionString"
0
 
chokkaStudentAuthor Commented:
This syntax is throwing error :

pkg.Connections[sConnectionString].Properties[ConnectionString]  = @"C:\File.csv"
0
 
chokkaStudentAuthor Commented:
RMM2001 :-

sFile - is the name of Source file - Connection Manager ..!

pkg.Connections["sFile"].Properties[ConnectionString]  = @"c:\File.csv"

In C#, it throws an exception .. " The name 'ConnectionString' does not exist in the current context "




0
 
chokkaStudentAuthor Commented:
Thank you Moderator !!!
0
 
chokkaStudentAuthor Commented:

I have attached C# Source Code which calls the Package.
I am getting the PackageExecution Result = Success

But when i check the SQL Table, I am not able to see the Exported Values !!!
DTSExecResult pkgResults = new DTSExecResult();
        try
        {
            string strSourceConn = @"C:\File to Place Order\FEB  2011 ORDER 2.csv";
            string destDir = "Provider=SQLNCLI10;Server=TestServer;Database=TestDB;Uid=sa;Pwd=Chokka";

            string pkgLocation = @"C:\SSISPOC\Package.dtsx";
            string sTableName = @"[dbo].[PlacedOrderDetails]";

            Package pkg;
            Microsoft.SqlServer.Dts.Runtime.Application app;

            app = new Microsoft.SqlServer.Dts.Runtime.Application();
            pkg = app.LoadPackage(pkgLocation, null);

            pkg.Variables["sSorceFileName"].Value = strSourceConn;
            pkg.Variables["sDestConnStr"].Value = destDir;
            pkg.Variables["sTableName"].Value = sTableName;
            
            pkgResults = pkg.Execute();
            Response.Write(pkg.ExecutionResult);
        }
        catch (Exception ex)
        {
            Response.Write(ex.InnerException.Message);
        }

Open in new window

0
 
chokkaStudentAuthor Commented:
My Package is Perfectly fine. My Source Code is perfectly fine.

Still, Source Values are not imported into SQL Table. What would be the reason ????
When i run the SQL Profiler - I dont see the Transaction Execution ..!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.