Creating GUI With SSIS Or Passing Parameter(s) In SSIS

Apr 23, 2007

Hello All!

I have two questions to ask in this one thread. I would appreciate any feedback.

1. Is it possible to create GUI from SSIS using macro so that it can display forms or dialogs? If so how can I create a form that can be used to pass the parameters for the execution of the SSIS??

2. Is it possible to pass parameter(s) to SSIS? If yes, how can we do it...Please provide me with any example.

I wait to hear from you!

Thanks,
Niben

View 1 Replies


ADVERTISEMENT

Integration Services :: Passing More Than One Variable As Parameter Value To SSRS By SSIS

Jul 13, 2015

public Sub Main()
        Dim url, destination As String
        destination = Dts.Variables("report_destination").Value.ToString + "" + "Report_" + Format(Now, "yyyyMMdd") + ".xls"
        url = "http://localhost/ReportServer?/ssis_resport_execution/ssis_ssrs_report&rs:Command=Render&ProductID=" + Dts.Variables("ProductID").Value.ToString + "&user_id" + Dts.Variables("user_id").Value.ToString
+ "&rs:Format=EXCEL"
        SaveFile(url, destination)
        Dts.TaskResult = ScriptResults.Success
    End Sub

How to pass more than one variable values in ssis as parameter values to ssrs. With the above code its showing as empty.If i am taking single variable i am able to render the data into  excel sheet.

View 2 Replies View Related

Passing SSIS Package Variable To Stored Procedure As Parameter

Feb 25, 2008



I've created a varible timeStamp that I want to feed into a stored procedure but I'm not having any luck. I'm sure its a simple SSIS 101 problem that I can't see or I may be using the wrong syntax

in Execute SQL Task Editor I have
conn type -- ole db
connection -- some server
sql source type -- direct input
sql statement -- exec testStoredProc @timeStamp = ?

if I put a value direclty into the statement it works just fine: exec testStoredProc '02-25-2008'

This is the syntax I found to execute the procedure, I don't udnerstand few things about it.

1. why when I try to run it it changes it to exec testStoredProc @timeStamp = ? with error: EXEC construct or statement is not supported , followed by erro: no value given for one or more requreid parameters.

2. I tired using SQL commands exec testStoredProc @timeStamp and exec testStoredProc timeStamp but nothing happens. Just an error saying unable to convert varchar to datetime

3. Also from SRS I usually have to point the timeStamp to @timeStamp and I dont know how to do that here I thought it was part of the parameter mapping but I can't figure out what the parameter name and parameter size should be; size defaults to -1.

Thank you, please help.

View 2 Replies View Related

Parameter Passing In SQL Server 2005 Integration Services (SSIS) 2005 From VB.Net2005

Sep 7, 2007

Is it possible to parm in a value to a SSIS

in my SSIS i have a variable;

Name : FileName
Scope : PackageName
Type : String

Value : ""

I have tried adding the following code in my vb.net project ;

pkg.Variables("filename").Value = "C: emp estfile.001"
pkg.execute()

but come up with the following error

A first chance exception of type 'System.MissingMemberException' occurred in Microsoft.VisualBasic.dll
Public member 'Variables' on type 'IDTSPackage90' not found.

can anyone help ?

View 11 Replies View Related

Parameter Passing In SQL Server 2005 Integration Services (SSIS) 2005

Jan 16, 2007

Hi All,

Parameter passing in SSIS 2005 sometimes appears to be a cumbursome task. I have been digging into this topic for quite some time and here i note down some simple steps to demonstrate parameter passing at Package level.

(1) Create a SSIS project using Business Intelligence 2005 Or VS 2005.

(2) Create datasource (.ds) and Data Source View as required.

(3) A default SSIS Package by the name Package.dtsx is created. Double click this and you are shown tabs for Control Flow, Data Flow, Event Handlers, Package Explorer. On the Control Flow, drap and drop Execute SQL Task from Control Flow Items in the toolbar.

(4) Lets now create a variable at Package level. Right click anywhere in the control flow box (not on the Task created in Step 3 above). Click on the Variables on the context menu displayed. Variables window appears on the left of the screen. Click the Add Variable box in this window to create a variable. Name it var1 (or whatever you may like), Scope as Package, DataType as String and Value as MyValue. This is only the default value.

(5) Now let us edit the SQL Task created in Step 3. Double on it, on the General tab you can change its Name, Description. Set ResultSet as None. We shall proceed to execute a stored procedure by name MySPName and pass it a parameter. Set ConnectionType as OLE DB. Select the connection you creates in step 2. Set SQLSourceType as Direct input. SQLStatement as MySPName ? . Note the ? mark after the name of the stored procedure. This is important to accept the variable value (var1) created in Step 4.

(6) Select Parameter Mapping tab now. Click on Add button. Select the Variable Name as User::var1. This is the user created variable of Step 4. Select Direction as Input, DataType as Varchar and Parameter Name as @var1. Click on OK now.

(7) This sets up the basic of parameter passing. Compile the project to verify everything works. Right Click on the SQL Task and select Execute Task. This will execute the package taking default value of the variable. This can be used along with dtexec command with /set option to pass the parameter at command prompt.





View 5 Replies View Related

Passing Parameters To An Ssis Package?

May 31, 2007

 When someone executes my ssis package, I want the user to be prompted to fill in a few parameters. How do i do that?

View 4 Replies View Related

Passing Data From Db To Ssis Variable

Jul 11, 2007

Is there anyway to pass data from a sql database to a variable in a ssis package. I'm trying to get a few fields from a sql database into some variables in my package and send an email using these variables with the send mail task?



Thanks,

View 17 Replies View Related

Passing Param - SSIS Package ??

Sep 21, 2007

Hi,


I'm exporting some data from a table to fixed-width file using SSIS.
The query inside the package is:


SELECT col1, col3, col6 FROM Table1
WHERE code = 5 AND status = 'S'


The 'code' value will be passed from the front-end.

How can I make the query dynamically take the 'code' value and export to a file?

If a user selects 'code' as 7 , then the query should change to


SELECT col1, col3, col6 FROM Table1
WHERE code = 7 AND status = 'S'


and with this it has to export to a file.


Is this possible? If so, any suggestions on how to do it? !!!


Thanks,
Siva.

View 9 Replies View Related

Not Executing SSIS SQL Task By Passing Variable

Jun 11, 2008

Hi,

I am creating SSIS package which using Execute SQL Task to which I am supplying one .sql file code is

delete from Test where ID = @ID

I defined @ID variable to SSIS and also path where .sql file placed but i am not able to execute this package i am getting error like can not find C:@Path file......

As i got information that passing such variable to .sql you need ADO.net connection so I changed SQLSERVER Database connection string to ADO.net .... after that when i set hard core value for these variable i.e for @ID and @Path then it runs sccessfully but by setting parameter i am getting above error


Any suggestion that will be gr8 help for me

T.I.A

View 2 Replies View Related

Scheduling A SSIS Package Using SQL Agent (passing Credentials)

Aug 30, 2007



Hi:

I created a fairly simple SSIS package to move data from a remote server which uses SQL Server Authentication to a local server that uses Windows Authentication. Everything works fine when executing the package within BI studio and if I execute the package by connecting to SSIS. If I schedule it as a SQL Server Agent Job, however, the non-integrated security fails unless I manually edit the connection to include the password. Obviously this is insecure because it is in plain text. What can I do?

Thanks.

View 4 Replies View Related

Passing Parameters To SSIS Execute Process Task

Feb 20, 2008

Hi

Can anyone help me in for the following.

i want to execute a exe file with two variable parameters

Executable : C: empMyExe.exe
Parameter1 : User::Category type is string (below example A)
Parameter2 : User::Amount type is string (below example 1)

in dos it looks like this
c: empMyExe A 1

This will executes fine.

Thanks,
Madhu

View 1 Replies View Related

SSIS Parent/Child Package Variable Passing

Feb 7, 2006

Hi

I have having trouble getting my hands around how to retrieve variables from a parent package. I read about the Environment variables and Configuration File at the parent package level and the Parent Package variable at the child level.

Here are my questions:

1. Can you only store/retrieve 1 variable in a config file at a time?

2. Does the child package have to define the variables and if so, do they have to be the same names as the parent package?

This seems so more more complex then the DTS2000 way of passing variables to and from packages.

Any help would be appreciated.

Thanks in advance,

Gordon Radley



View 7 Replies View Related

Passing Values For Variables To An SSIS Package From C#/ ASP.NET Page.

May 30, 2007

I've got an SSIS package that works fine. It does extracts from a foreign ODBC source and moves it to SQL Server. It has 3 variables, a customer ID, a fromDate and a toDate. Those variables are used to complete a SQL statement expression. So far so good.



I now want to provide a web interface in a web page that will enable the user to provide values for those variables. How can I execute my package passing the user's input?



Thanks for any advice.



Lou

View 3 Replies View Related

SQL Server 2008 :: SSIS - Passing Column Delimiter From A Variable?

Mar 13, 2015

I am building a generic SSIS where it takes a text source file and converts to a destination source file . However in the process I want to set the row delimiter and the column delimiter dynamically through the package variable. So that I can use it for any file transfer.

I saw the option for row delimiter in the file connection string property but did not see any column delimiter option.

View 3 Replies View Related

Dyanamically Passing Input Parameters To Stored Procedure By Using SSIS

May 14, 2008

Hi,

I have 2 source tables emp_ass,aprvl_status these tables are not having common column to join. and 1 target table Time_Card, i have a stored procedure with 4 input parameters, emp_ass_id,status_id,start date,end date,i am inserting data into timecard based on emp_ass_id, my week start date is sunday and end date is saterday if emp start date is sunday i am just incremnting the start date by 7 days as end date is saterday and inserting that row, if employe statrt date is other than Sunday. i am just insering start date with to reach end date saterday, this work fine when i give the input parameters, now my reqirement is i need to automate this process as i need to get new emp_ass_id which is not in target table and insert his records based on his start date and end date,
ex:
if emp_ass_id is 1001, start date 1/1/2008 and end date is 2/1/2008 then i need to insert

Uniq_Id, emp_ass_id, start_date end_date status_id





1099

1001

1/1/2008 12:00:00 AM
1/5/2008 12:00:00 AM 1








1100

1001

1/6/2008 12:00:00 AM
1/12/2008 12:00:00 AM 1








1101

1001

1/13/2008 12:00:00 AM
1/19/2008 12:00:00 AM 1








1102

1001

1/20/2008 12:00:00 AM
1/26/2008 12:00:00 AM 1








1103

1001

1/27/2008 12:00:00 AM
2/2/2008 12:00:00 AM 1






the stored procedure will insert these records if i give the input parameters, now i need to automate this process by using SSIS. please help me,i need to get emp_ass_id,start_date,end_date dynamically from source table if emp_ass_id is not in target table.

Thanks in advance.

View 9 Replies View Related

Integration Services :: Passing Parameterized Query Through Variable In SSIS

May 22, 2015

I have defined a variable Var_Query_SQL and passed the below query using expression but it is showing error. where am i going wrong.

"SELECT
       sample_id ,
       sample_time ,
       trans_date ,
       product = mh.[identity] ,
comments = s.m_smp_comment

[URL] ...

View 4 Replies View Related

Reg: Executing And Passing Arguements To Exe Using SSIS Execute Process Task

Apr 22, 2008

Hi,

Can any one give me a solution how to call exe. Also, i need to pass 1 input to that exe.
I tried using Execute process task but it is not working.

My task is:

I have DFT , it generate a output file then after the output file generated i need to upload it SharePoint server.
So i developed one .exe using .net, C#. ( it is running fine when cmd line)

But i want to embbed in SSIS package.

Like :

DFT ( DataFlowTask) --------------------> OutPutFile.txt

EPT ( ExecuteProcessTask) ----------> Upload it to SharePointPortal.


Kindly give the solution ASAP.

Thanks
Senthil

View 4 Replies View Related

Passing A Table Programmatically To Datareader Source Component In Ssis

Feb 26, 2007

Hi There,

I am loading/executing packages from c# and I need to populate a temp table from user input and pass this table as a variable to the datareader source components sql command. I am using expression to build this query, but I am getting design time error when I have this command..

"select id, (SysDate + 28) as ExpiresDate from Table1 where id in (Select Id from" +@[User::Table2]+")"..

I have declared Table2 as a variable of type Object and I am creating Table2 in C# and I am assigning that Table to the user Table. But in the design mode, I am getting an error...expression cannot be evaluated.

Can anybody please tell me when I cannot do this?

Thanks,



View 3 Replies View Related

Help On Creating SSIS

Dec 16, 2006



Hello ,

I am getting the given error message while creting SSIS. Please help me to solve the error....



Error loading 'Package.dtsx' : QI for IEnumVARIANT failed on the unmanaged server..





regards,

Malhar , THanks

View 8 Replies View Related

Integration Services :: Passing Connection String Dynamically To SSIS Package

Jul 12, 2012

I have create packages which loads the data from flat file to sql server table, now I want to make my destination table connection dynamic what is format of connection string. I also need to pass user name and password for sql server dynamically in this case, what is the format for the connection string.

Also  in package i used ADO.net  as source for  *.mdb files how i can set the commection to .mdb files dynamically which is used as source in my package.

View 8 Replies View Related

SQL 2012 :: Passing Record Counts From Execute Task In SSIS To Package Variables

Mar 4, 2014

I've got a package in SSIS 2012 that has an Execute SQL task in the control flow level.

The SQL in question does an Upsert via the SQL merge statement. What I want to do, is return the count of records inserted and records updated (No deletes going on here to worry about). I'm using the output option to output the changed recs to a table variable.

I've tried returning the values as:

Select Count(*) as UpdateCount from @mergeOutput where Action = 'Update'
and
Select Count(*) as InsertCount from @mergeOutput where Action = 'Insert'

I've tried setting the resultset to both Single rowset and Full rowset, but i'm not seeing anything returned to the package variables I've set for them (intInsertcount and intUpdatecount).

View 2 Replies View Related

SQL 2012 :: SSIS Passing Parameters To Stored Procedure That Changes Based On The Data Being Passed?

Jun 23, 2015

Using the following:

SQL Server: SQL Server 2012
Visual Studio 2012

I have created an SSIS package where I have added an Execute SQL Task to run an existing stored procedure in my SQL database.

General Tab:

Result Set: None
Connection Type: OLE DB
SourceType: Direct Input
IsQueryStoredProcedure: False (this is greyed out and cannot be changed)
Bypass Prepare: True
SQL Statement: EXEC FL_CUSTOM_sp_ml_location_load ?, ?;

Parameter Mapping:

Variable Name Direction Data Type Prmtr Name Prmtr Size
User: system_cd Input NVARCHAR 0 10
User: location_type_cd Input NVARCHAR 1 10

Variables:

location_type_cd - Data type - string; Value - Store (this is static)
system_cd - Data type - string - ??????
The system code changes based on the system field for each record in the load table

Sample Data:

SysStr # Str_Nm
3 7421Store1
3 7454Store2
1815061Store3
1815063Store4
1615064Store5
1615065Store6
1615066Store7
7725155Store8

STORED PROCEDURE: The stored procedure takes data from a load table and inserts it into another table:

Stored procedure variables:
ALTER PROCEDURE [dbo].[sp_ml_location_load]
(@system_cd nvarchar(10), @location_type_cd nvarchar(10))
AS
BEGIN .....................

This is an example of what I want to accomplish: I need to be able to group all system 3 records, then pass 3 as the parameter for system_cd, run the stored procedure for those records, then group all system 18 records, then pass 18 as the parameter for system_cd, run the stored procedure for those records and keep doing this for each different system in the table until all records are processed.

I am not sure how or if it can be done to pass the system parameter to the stored procedure based on the system # in the sys field of the data.

View 6 Replies View Related

Creating Jobs In SSIS

Nov 5, 2007

what is the equiv to dtsrunui to create the operating system (CmdExec)....
anyone have a good site suggestions for ssis outside of bol

View 2 Replies View Related

Creating A Script In SSIS?

Nov 22, 2007

Is it possible to create a Create Table or similar script in SSIS for an object?

Thanks in advance,
Richard.

View 3 Replies View Related

Need Help After Creating SSIS Project

Dec 20, 2006

Hi,



I have created SSIS project that imports .csv file into sql server 2005.

SSIS package is saved successfully but now i want the table created in sql server 2005.

So, how to get the imported data in sql server 2005.



Can any1 help me out?

Thanx n Regards,

Ruja :)

View 6 Replies View Related

Creating And Scheduling Job For SSIS

Feb 8, 2007



Hi All

I want to create and schedule job for my SSIS packages. But I have no idea how to do that.

View 12 Replies View Related

Creating A SSIS Package

Mar 5, 2007

HI All,



can any body give steps to import data from one sqldb to another through ssis package, i was comfotable with dts but ssis is a lil bit confusing.....



thnx

regards

View 1 Replies View Related

Creating SSIS Packages In C#

Nov 15, 2006

How can I create a SSIS package using C#? Can anybody have source code for the same.

thanx in advance,

Vinod

View 1 Replies View Related

Creating An SSIS Package

Feb 18, 2008

Can anyone give a newbie a general script for creating an SSIS page to import data nightly from an Access database to sql 2005. They are not on the same server. The Access database us 366,968 kb and grows everyday from input from web content. It has 5 tables and 100's of queries, which is how it is updated.

Please give me a starting point, do not understand the info in books on line.

Thank you
Dee

View 6 Replies View Related

Get Parameter And Use In SSIS From SSRS Parameter

Mar 17, 2006

Hi,

I am not sure this is the correct place to post this
question, but here it is. I am trying to pass some parameters to SSIS
from a report using the report parameter, then SSIS will create the
datareaderdest and return to the report to use.

Anyone have any idea, guidance or leads please share it out. Thanks in advance.



Daren

View 9 Replies View Related

Integration Services :: Execute Process Task / Passing In Variables From SSIS To Powershell Script

May 15, 2015

I am using SSIS 2012 to dynamically backup stored procedures on a list of Servers and Databases.Here are the steps in my package,

1. Execute SQL Task: Captures a result set (configured to save the data set in an Object variable) with all the Servers and Databases on which stored procedures exist.

2. For each loop that is configured to get each each row(server name @[User::Server_Name] and databases name @[User::DataBase_Name]) from the object variable (@[User::Connection_Strings])and pass it to a connection manager that has an expression for servername
and database name.

2a)  Within the for each loop, i have an execute process task that is configured as

 i) Executable:  C:WindowsSystem32WindowsPowerShellv1.0powershell.exe
 ii) Arguments:  Configured this to fetch value from an expression. The expression i am using is,'C:batch - CopyPowerShell Scripts to Backup Stored ProceduresScriptOutSPs.ps1' -$Server_Name "+ @[User::Server_Name]+ " -$Database_Name "+ @[User::DataBase_Name]
             
Note:  @[User::Server_Name] is the Servername from object variable and so is @[User::DataBase_Name] for database name . The execute task is to run a command line that triggers a powershell script with parameters. Here is the powershell script that i am using,

param([String]$Server_Name,[String]$Database_Name)
$Server = $Server_Name
$Database = $Database_Name
$savePath = "SalesDepartmentsData ScienceUsersSANDEEP PStoredProcedures_Backup"

[code]...

When i execute the script, by passing parameters from arguments, it executes successfully but nothing happens. Passing wrong arguments in the expression?

View 3 Replies View Related

Integration Services :: SSIS Foreach Loop Container Passing Variables To Store Procedure

Sep 8, 2015

I am having a challenge to pass the filename variable from SSIS Foreach Loop Container to SQL store procedure.  I have an "Execute SQL Task" inside "Foreach Loop Container" which will receive the input filename parameter passed by the Foreach Loop Container.  The store procedure command line is defined as "EXEC sp_mySQLStoreProcedure  ?" within the "Execute SQL Task".  The Foreach loop container will gather the filenames from File System then put it in the filename variable one by one.  I would like to pass the filename variable as the input parameter to the sp_mySQLStoreProcedure in the "Execute SQL Task".  How can I connect the variable and the store procedure so that it will process all the input files gathered by the Foreach Loop Container?

View 4 Replies View Related

Error Creating New SSIS Project

Sep 15, 2006

When I start a new project the BI environment cannot open the DTSX package and I get only one tab with the message:

"microsoft visual studio is unable to load this document. Object reference not set to an instance of an object".

I tried uninstall and install of BI Development Studio as well as a repair install of Visual Studio 2005. None of which helped. Please help me to get this fixed.

Kind regards,

Neeva

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved