Solved

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

Posted on 2010-11-22
30
1,783 Views
Last Modified: 2012-05-10

VS 2008 / SQL 2008 SSIS

How to create Source / Destination File Connection String dynamically by reading from config file ?

How to create connection manager dynamically ?
0
Comment
Question by:chokka
  • 17
  • 13
30 Comments
 
LVL 30

Expert Comment

by:Reza Rad
ID: 34191856
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.
0
 

Author Comment

by:chokka
ID: 34191997


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

Expert Comment

by:Reza Rad
ID: 34192037
you should just set variable as expression, like:
@[User::SourceFile]

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

Author Comment

by:chokka
ID: 34192138
Okay, Thanks ..!

Now

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

0
 

Author Comment

by:chokka
ID: 34192147


For Source file - Flat File :-

From the Application - We have a Browse Window.

File Path can differ by selecting the Folder Option.
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 34192254
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
0
 

Author Comment

by:chokka
ID: 34200878
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
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 34200973
look at my previous post, you just need to ADDRESS the dtsx file in the code provided
does it make sense to you?
0
 

Author Comment

by:chokka
ID: 34201011

Okay.



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

Expert Comment

by:Reza Rad
ID: 34201096
also set Packagelocation with the address of dtsx file
0
 

Author Comment

by:chokka
ID: 34201350


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();

0
 

Author Comment

by:chokka
ID: 34201360
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();



    }

Open in new window

0
 

Author Comment

by:chokka
ID: 34201395

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();



    }

Open in new window

0
 

Author Comment

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

0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 34201486
add this reference:
Microsoft.SQLServer.ManagedDTS

0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 30

Expert Comment

by:Reza Rad
ID: 34201495


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.
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 34201499
add this using:
using Microsoft.SqlServer.Dts.Runtime;
0
 

Author Comment

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

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

Open in new window

0
 

Author Comment

by:chokka
ID: 34201710
This is the error, i am receiving ..!
Error2.JPG
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 34202724
could you upload your package here?, I think there should be something wrong there.
0
 

Author Comment

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

Author Comment

by:chokka
ID: 34202950
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 ..!
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 34202960
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
0
 

Author Comment

by:chokka
ID: 34202963
Package which i attached .. i have not defined any Variable.

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

0
 
LVL 30

Expert Comment

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

Author Comment

by:chokka
ID: 34213342

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

Accepted Solution

by:
Reza Rad earned 500 total points
ID: 34220720
OK, I changed your package,
try this one,
you need a password to open this package, type 123 as password


340Price.dtsx.txt
0
 

Author Comment

by:chokka
ID: 34394522
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
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 34398603
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.
0
 

Author Comment

by:chokka
ID: 34400832
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;
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now