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


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 ?
chokkaStudentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Reza RadConsultant, TrainerCommented:
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
chokkaStudentAuthor Commented:


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
Reza RadConsultant, TrainerCommented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

chokkaStudentAuthor Commented:
Okay, Thanks ..!

Now

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

0
chokkaStudentAuthor Commented:


For Source file - Flat File :-

From the Application - We have a Browse Window.

File Path can differ by selecting the Folder Option.
0
Reza RadConsultant, TrainerCommented:
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
chokkaStudentAuthor Commented:
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
Reza RadConsultant, TrainerCommented:
look at my previous post, you just need to ADDRESS the dtsx file in the code provided
does it make sense to you?
0
chokkaStudentAuthor Commented:

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
Reza RadConsultant, TrainerCommented:
also set Packagelocation with the address of dtsx file
0
chokkaStudentAuthor Commented:


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
chokkaStudentAuthor Commented:
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
chokkaStudentAuthor Commented:

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
chokkaStudentAuthor Commented:
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
Reza RadConsultant, TrainerCommented:
add this reference:
Microsoft.SQLServer.ManagedDTS

0
Reza RadConsultant, TrainerCommented:


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
Reza RadConsultant, TrainerCommented:
add this using:
using Microsoft.SqlServer.Dts.Runtime;
0
chokkaStudentAuthor Commented:
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
chokkaStudentAuthor Commented:
This is the error, i am receiving ..!
Error2.JPG
0
Reza RadConsultant, TrainerCommented:
could you upload your package here?, I think there should be something wrong there.
0
chokkaStudentAuthor Commented:
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
chokkaStudentAuthor Commented:
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
Reza RadConsultant, TrainerCommented:
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
chokkaStudentAuthor Commented:
Package which i attached .. i have not defined any Variable.

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

0
Reza RadConsultant, TrainerCommented:
>>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
chokkaStudentAuthor Commented:

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
Reza RadConsultant, TrainerCommented:
OK, I changed your package,
try this one,
you need a password to open this package, type 123 as password


340Price.dtsx.txt
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chokkaStudentAuthor Commented:
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
Reza RadConsultant, TrainerCommented:
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
chokkaStudentAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.