?
Solved

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

Posted on 2011-04-20
22
Medium Priority
?
2,152 Views
Last Modified: 2012-05-11
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 ..!
0
Comment
Question by:chokka
  • 13
  • 3
  • 2
  • +2
20 Comments
 
LVL 22

Accepted Solution

by:
PedroCGD earned 1000 total points
ID: 35434405
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
 
LVL 9

Expert Comment

by:radcaesar
ID: 35435542
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
 

Author Comment

by:chokka
ID: 35435581
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:chokka
ID: 35435804
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
 

Author Comment

by:chokka
ID: 35436135
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
 

Author Comment

by:chokka
ID: 35436730
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
 
LVL 7

Expert Comment

by:rmm2001
ID: 35437591
You should be able to say pkg.Connections[YourConnectionManagerName].Properties[ConnectionString] to refer to the connection string directly
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35438455
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
 
LVL 22

Expert Comment

by:PedroCGD
ID: 35439355
"
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
 

Author Comment

by:chokka
ID: 35441012
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
 

Author Comment

by:chokka
ID: 35441061

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
 
LVL 7

Assisted Solution

by:rmm2001
rmm2001 earned 1000 total points
ID: 35441067
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
 

Author Comment

by:chokka
ID: 35441074
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
 
LVL 7

Expert Comment

by:rmm2001
ID: 35441082
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
 

Author Comment

by:chokka
ID: 35441143

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
 

Author Comment

by:chokka
ID: 35441185
This syntax is throwing error :

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

Author Comment

by:chokka
ID: 35441535
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
 

Author Comment

by:chokka
ID: 35447746
Thank you Moderator !!!
0
 

Author Comment

by:chokka
ID: 35448388

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
 

Author Comment

by:chokka
ID: 35448581
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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video teaches viewers about errors in exception handling.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.
Suggested Courses
Course of the Month15 days, 6 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question