I have a SourceConnectionExcel in my SSIS package. This path to this is configured in the .dtsConfig file. My question is, how can I get the path from within a Script Task?
I ws thinking something like this might work, but no dice.
Dts.Connections("SourceConnectionExcel").Properties("ExcelFilePath")
So in a script task for one of my packages I have a connection manager to an dtsConfig OLE DB.
This is the code
Dim ConnectionString As String = Dts.Connections("db_stage").ConnectionString
Dim sqlConnection As SqlConnection = New SqlConnection(ConnectionString )
I get a login failed for
user...But if I hardcode the connectionString, including the password this works.
1) Why is it that the ConnectionString from the connection manager omits this password?
2) Since this is an OLE DB, is there anyway to set the Data Source Designer to omit the "Provider=ABCDED.1" section?
Thanks!
Tony
Hi all
In a SSIS Package I have defined a ConnectionManager to
a SQL Server 2005.
Now I am trying to query data in a Visual Studio for Application Script.
I tried the following:
Dim local_SQLConnectionManager As Microsoft.SqlServer.Dts.Runtime.ConnectionManager
Dim local_SQLConnection As New System.Data.SqlClient.SqlConnection
Dim local_SQLDataReader As System.Data.SqlClient.SqlDataReader
Dim local_SQLCommand As New System.Data.SqlClient.SqlCommand
local_SQLConnectionManager = Dts.Connections("Name of ConnectionManager")
local_SQLConnection = CType(local_SQLConnectionManager.AcquireConnection(null), System.Data.SqlClient.SqlConnection)
local_SQLConnection.Open()
local_SQLCommand.Connection = local_SQLConnection
local_SQLCommand.CommandText = "SELECT something"
local_SQLDataReader = local_SQLCommand.ExecuteReader()
If local_SQLDataReader.Read Then
Dts.Variables("Just a variable").Value = local_SQLDataReader.GetString(0)
End If
local_SQLDataReader.Close()
It always stops on the line with the AcquireConnection:
Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.SqlClient.SqlConnection'. and so on ....
Thanks for any help
Frank Uray
In fact, we could use AMO in the Sript Task, just by include the AMO.dll, many guys have talked about it on the forum.
But now, O my god, I met a big problem.
I declare a AS connectionManager in the SSIS package. In the Script Task I can't use it.
If this is a OEL DB ConnectionManager and connect to SQL Sever, I know I could write this inside the Sript Task:
Public myKPIConnection As SqlClient.SqlConnection
myKPIConnection = _
DirectCast(Dts.Connections("CYF.KPIOperation").AcquireConnection(Dts.Transaction), _
SqlClient.SqlConnection)
Then I could use myKPIConnection inside the Task.
But, How to do the similar thing to a AS ConnectionManager? I need to DirectCast the AS connectionManager to What?
By the way, the only thing I want to do is to Start an AS transaction inside the Sript Task, and let a Process Task outside to be enlisted in the trransaction. So I need to use the same AS connectionmanager.
Thanks.
The CreatePackage sample provided with SQL Server programmatically creates a package that has a source type of OLEDB to a flat file destination. I am building exactly the opposite, source=flatfile, destination=SQL Server. I expect that will be a more common scenario is using SSIS.
The problem I have is populating the source columns in the FlatFileSource connection manager programmatically. I know it can be done because it happens when you build a package in Visual Studio. What I'd like to know is how to do it programmatically in the object model. How can I interrogate the datasource through the connection manager to find out what columns it has? If I know, I can add the columns to the connection manager. My sample below does this, but it doesn't know the number of columns in the source so that value is hardcoded. I'm guessing there is a better way to do this than what I've got below.
How can I find the number of columns in my source so I can add the columns to the connection manager?
Thanks.
Private Sub AddColumnsToFlatFileConnectionManager()
Dim ff As wrap.IDTSConnectionManagerFlatFile90 = Nothing
For Each cm As ConnectionManager In _Package.Connections
If cm.Name.Equals(_ExternalConnectionID) Then
ff = TryCast(cm.InnerObject, wrap.IDTSConnectionManagerFlatFile90)
DtsConvert.ToConnectionManager90(cm)
End If
Next
If Not ff Is Nothing Then
Dim col As wrap.IDTSConnectionManagerFlatFileColumn90
Dim name As wrap.IDTSName90
Dim Min As Int32 = 0
Dim Max As Int32 = Min + 3 ' *** HARDCODED LIMIT ***
For cols As Integer = Min To Max
col = ff.Columns.Add()
If cols = Max Then
col.ColumnDelimiter = vbCrLf
Else
col.ColumnDelimiter = ","
End If
Dim width As Int32 = 50
Dim DataType As wrap.DataType = wrap.DataType.DT_STR
col.ColumnType = "Delimited"
col.DataType = DataType
col.MaximumWidth = width
col.DataPrecision = 0
col.DataScale = 0
col.ColumnWidth = width
name = TryCast(col, wrap.IDTSName90)
name.Name = "Column " & cols.ToString
Next
End If
End Sub
I'm developing a custom manager in SSIS that has several properties defined. One property is a password string that is visible in clear text in the properties pane. I'm trying to figure out how to create a masked field in the properties pane that will mask the text and not present this in clear text. Can someone send me instructions or code samples in C# for doing this? BOL doesn't provide any information on doing this.
View 3 Replies View RelatedHello,
I just create FLATFILE connection manager from UI, and now I would like save this connection manager for future use. I am not able to do this and now I am really stuck in the middle of coding.
ConnectionManager class has SaveToXML and LoadFromXML methods from IDTSPersist, these methods are not for use, but I am able to save CM to XML, however not load. Every try for loading ends with error 0xC0011008. The XmlNode.Name == "DTS:ConnectionManager"
CM class is not serializable, thus standard .NET feature is not working, also is sealed.
IDTSConnectionManagerFlatFile90 of cm.InnerObject is not serializable either, because it is interface and cm.InnerObject cannot be cast to ConnectionManagerFlatFileClass because is the COM.
Is there any way, how to save and load ConnectionManager besides writing of tons of code for each of connection manager type?
I am able to save and load package with connection manager, but it is not exactly what I want. Also is there possibility to work with XML in packages, but this is not "right".
Thanks
Erik
I am using the "Transfer SQL Server Objects Task" to copy some tables from database A to database B including data.
The tables, primary key constraints, Foreign key, data and all transfers nicely except for "DEFAULT CONSTRAINTS" on the tables.
I have failed to find any option in the "Transfer SQL Server Objects Task" task to explicitly say "copy default constraints". So I guess logically it should happen automatically but it doesn't. I hope it is not a bug :-)
Any option anyone knows will help.
Thanks.
In short, does the €œTransfer SQL Server Objects Task€? support distributed transactions?
In trying to use a €œTransfer SQL Server Objects Task€? in a container using a transaction on the container. The task is set to support the transaction. It is setup to copy table data from several tables from a non-domain server (sql server 2000) to a domain-based server (sql server 2005). I get an error stating, €œThis task can not participate in a transaction€?.
I am wondering if it means exactly what it says €“ this task in SSIS can€™t participate at all. Or does it mean that it won€™t in this scenario for some reason. I attempted a simple copy of data from mssql 2005 to mssql 2005 (same server) and the task still failed). MSDTC appears to be running properly on my machine and such (I can do a simple distributed transaction across linked server to the 2000 server in Query Analyzer (QA)). Also, MSDTC appears to be working on both servers with distributed transaction query tests in QA.
Here€™s the error info€¦
SSIS package "Development BusinessContacts and Products Migration.dtsx" starting.
Information: 0x4001100A at Copy BusinessContacts Data: Starting distributed transaction for this container.
Error: 0xC002F319 at Copy BusinessContacts database table data 1, Transfer SQL Server Objects Task: This task can not participate in a transaction.
Task failed: Copy BusinessContacts database table data 1
Information: 0x4001100C at Copy BusinessContacts database table data 1: Aborting the current distributed transaction.
Information: 0x4001100C at Copy BusinessContacts Data: Aborting the current distributed transaction.
SSIS package "Development BusinessContacts and Products Migration.dtsx" finished: Failure.
The program '[4700] Development BusinessContacts and Products Migration.dtsx: DTS' has exited with code 0 (0x0).
Hopefully a simple question about parent-child package relationship. For this example, let's say I have a simple setup - one parent package: parent.dtsx, and one child package: child.dtsx. The parent package calls the child package via the ExecutePackage Task.
If I add an OleDB ConnectionManager to the parent package called MySqlConnectionManager, should I be able to reference this connection via a script task (or custom component) from my child package? I realize that I will have a problem doing this at design time, but I thought I could get around it with the script task or custom component. That said, when I look in the Connections collection at run-time from within my child package, I do not see the parent package's MySqlConnectionManager. Am I missing something, or is this the way it was intended to work?
Thanks,
David
A common issue that I run across with clients is they want only want to process a file if it's finished transmitting to the server. This SQL Server 2005 task reads the properties of a file and writes the values to a series of variables. For example, you can use this task to determine if the file is in use (still be uploaded or written to) and then conditionally run the Data Flow task to load the file if it's not being used. You can also use it to determine when the file was created in order to determine if it must be archived.
http://www.pragmaticworks.com/filepropertiestask.htm
I've created my own posting for this. The original post was here, I apologize: http://forums.microsoft.com/forums/ShowPost.aspx?PostID=2906512&SiteID=1
According to the poster it's not possible. But there has to be some way to do it? Reflection (don't know how)?
I need to get a reference to the task host in an SSIS Task component.
Basically the scenario is this:
I have a custom task I have created. However I would like to validate that the ExecValueVariable is infact a string variable during the validate event of the task. I know how to verify its a string variable. But I can't figure out how to read what the user selected (such as User::Myvariable). The only way I've been able to figure out how to do it, but it only works if you open my custom task UI.
What I did is this:
I've implemented IDtsTaskUI and during the initialize section I wrote:
Sub Initialize(ByVal taskHost As TaskHost, ByVal serviceProvider As IServiceProvider) Implements IDtsTaskUI.Initialize
' Store the TaskHost of the task.
Me.taskHostValue = taskHost
Dim myTask As CustomTask= CType(taskHost.InnerObject, CustomTask)
myTask.myTaskHost = taskHost
End Sub
My Task is named: CustomTask. I have a public variable in my task as follows:
Public NotInheritable Class CustomTask
Inherits Task
Implements IDTSComponentPersist
Public myTaskHost As TaskHost = Nothing
Therefore I pass back the taskhost value to the CustomTask class, and voila I have it.
Problem is, this only works if the custom task calls the initialize method, and this only happens when you open the custom editor.
I then do the validation in my CustomTask class and it works fine, but myTaskHost is null/nothing until you actually open the custom task UI
How do I solve this?
Sorry guys, I'm really new to this. Can somebody tell me the easiest way to use the FTP task with a dynamic local path. I just want the FTP task to send whatever file is in the directory I specify.
I'm creating a package that extracts data from a table to a pipe-delimited file. I then use an external process (since using Expressions for it was a nightmare) to rename the file so that it'll reflect the previous month (e.g. April2008_report.txt). Then, I have to FTP that file. That's where my problem is. It seems I need to specify the exact filename on the local path of the FTP task, and since the filename will change each month... well, you see my problem.
Is there a way to tell the FTP task to just send *.txt, or something to that effect?
Thanks in advance for you help!
Thanks,
Mark
Hi ,
I have a master database. Based on certain criteria's I will be creating one new database. Once I have finished the creation, I have to check the value of a particular column in both database tables.
This is actually a status check. if both are not equal i have to update detail database table column value with master column value.
I thought i can use lookup transform for this. The problem is master database table's status datatype and detail table's status datatype is diff.
Can anyone suggest me any other way for this problem?
thanks in advance.
In my package i am loading data from a flat file into multiple tables like table1, table2, table3 all tables have a column called cust_num.
But data for this column cust_num is only coming in table1 and i need to load the data from this column to other tables how can i do this.
I have written the Dynamic TSQL S-Proc. Below is what i wanted to implement in SSIS using foreach loop container as a cursor. But i am little doubtful whether I can achieve the dynamics to this level. I know everything is possible but is it advisable to go for this simple Sproc or SSIS tasks.
I have some 15 tables being populated using this SPROC.
Here is some helpful description
ENTITYNAME gives me the table i need to work
FIELDNAME gives me the field i have to work on
CHANGEDVALUE gives me the value changed in that field
( This three i get from source table which is about 9000 rows and containing 15 possible ENTITY to be work on and 100's of their respective FIELD )
while in Cursors i need to get using these above variables other variables like
FLAG
KeyName
Thrugh SQL1 I get the KeyValue
then using this KeyValue check if the data exist update else insert new data.
QUESTION: IS THIS ADVISABLE to go for SSIS task or just carry with SPROC?
/*******************************************************************************************************/
DECLARE Table_Cursor CURSOR
FOR SELECT ENTITYNAME,FIELDNAME, KEYID, CHANGEDVALUE, UPDATEUSER, UPDATEDATE
FROM dbo.ChangedDimensionStage
OPEN Table_cursor
FETCH NEXT FROM Table_cursor INTO @ENTITY, @FIELD, @KEYID, @VALUE, @USER, @DATE
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @FLAG NVARCHAR(50);
SET @FLAG = (SELECT LEFT(@ENTITY, (SELECT CHARINDEX( 'DIM', @ENTITY) -1)) )+ 'LastUpdateFlag';
DECLARE @KeyName NVARCHAR(50);
SET @KeyName = (SELECT LEFT(@ENTITY, (SELECT CHARINDEX( 'DIM', @ENTITY) -1)) )+ 'Key'
DECLARE @KeyValue NVARCHAR(50)
DECLARE @SQL1 NVARCHAR (1000)
SET @SQL1 = N'Select @KeyValueOUT = '+ @KeyName + ' FROM DW_Integration.dbo.MangFact WHERE ClaKey = ' + @KEYID + ' GROUP BY ' + @KeyName + ' HAVING SUM(TotalClaCount) > 0 OR SUM(IncidentOnlyClaCount) > 0 '
EXECUTE sp_executesql @SQL1, N'@KeyValueOUT INT OUTPUT', @KeyValue OUTPUT;
DECLARE @WC_TABLE NVARCHAR(100)
SET @WC_TABLE = 'WorkingCopy' + @ENTITY
DECLARE @SQL2 nvarchar (1000);
SET @SQL2 = 'IF EXISTS (SELECT '+ @KeyName +' FROM ' + @WC_TABLE + ' WHERE ' + @KeyName + ' = ' + @KeyValue + ' )' +
' BEGIN UPDATE ' + @WC_TABLE + ' SET '+ @FIELD + ' = '''+ @VALUE + ''' WHERE ' + @KeyName + ' = ' + @KeyValue +'; END' +
' ELSE BEGIN
INSERT INTO '+ @WC_TABLE + ' SELECT * FROM DW_Integration.dbo.' + @ENTITY + ' WHERE ' + @Flag + ' = ' + '''Y''' + ' AND '+ @KeyName + ' = ' + @KeyValue + ';' +
'UPDATE ' + @WC_TABLE + ' SET '+ @FIELD + ' = '''+ @VALUE + ''' WHERE ' + @KeyName + ' = ' + @KeyValue +'; END'
EXECUTE sp_executesql @SQL2
FETCH NEXT FROM Table_cursor INTO @ENTITY, @FIELD, @KEYID, @VALUE, @USER, @DATE
END
CLOSE Table_cursor
DEALLOCATE Table_cursor
Hello,
I m having an FTP Task in SSIS project, from where I have downloaded .csv file.
And also having a Processed Folder in which the processed .csv files are kept.
Main task is to download file from FTP and check , if the date of downloaded file is newer than file in processed folder, then only process that file. Else skip the processing
Can anyone help me out how to process about.
plz reply as early as possible.
Thanks & Kind regards ....
Aparna
I have written a very basic Script Task using VS 2005, it just looks through a sqlDataReader and display a message.
I want this script to be triggered by an SQL Agent. I am not sure what to do from this point on. How do I publish or whatever it is called to get it to the SQL server or agent?
This is my very first attempt, no I have no clue what I am doing, will need lots of help and tutorials, thanks for reading.
i am trying to generate a dynamic connection string using a variable 'servername'
i know where and how to do it but i am not sure abt the details of the paramenters that are to be passed in the script
and anyone give me a sample script
this is what iam trying to do
Public Sub Main()
'
Dts.Connections("SQL????").ConnectionString = "Data Source????" + Dts.Variables("servername").Value.ToString() + ???windows authentication and what else ????
'
Dts.TaskResult = Dts.Results.Success
End Sub
Thanks all
Hi there,
I'm still pretty new to SSIS, what I am really battling with is getting a custom task to work.
I have developed a custom task, digitally signed it added it to the Global Assembly Cache, written the UI for it - Pretty much as it is done here: http://technet.microsoft.com/en-us/library/ms136080.aspx
but my problem is that when I drag it from the tool box into the control flow pane, I get this message: "The task user interface specified by type name 'XXXXXXXXXXXXXXXXXXX' could not be loaded.
(Microsoft.DatatransformationServices.Design).
Does anybody have an idea of what I am doing wrong here?
Duane.
Dim RootFolder As String
RootFolder = DTSGlobalVariables("gRootFolder")
I have an error that RootFolder is not being declared with this ?
but it is , why is .NET such a heap of sh*t ?
How can i find out the moniker for my own ssis task?
View 3 Replies View RelatedPlease help me i am new to ssis ....I am trying to send an email using mail task and I am sure that ISP works fine with smtp. I am entering all the correct information in the task but it takes a long time to run the task and then comes back with a failure error. can someone help me find an easy way to send an email to group of emails...I even tried to send it to 1 email and it is still not working.
View 10 Replies View RelatedHi
I am new to SSIS. I wanted to do a daily ETL load from sourceA to targetB database
Please let me know the best method to that i should follow to do the below task
1. Get the Yesterday's date or last months's from-date & to-date dynamically.
2. Pass the from-date and to-date to my source SQL [e.g select emp_name, emp_dept from emp where hiredate between @param1 and @param2]. Acually my source sql has subquery and the parameters has to be passed to the subquery's where clause.
3. Insert the resultset into a staging table in the targetB database.
4. Join this targetB table with another table in sourceC db
5. finally load the result set in to the targetT database table
6. How to configure/schedule this package so that it runs daily, automatically pass the yesterday's date to the package.
I have done this using Oracle PL/SQL but my requirement is to do this in SSIS. I am not sure which control flow or transformation items to be used to do this in an effecient way.
Please guide me.
Hi Everyone,
When I first installed BI Studio everything was working fine, but I went into it today and I noticed that the FTP task seems to be missing, I can't see it in the toolbox, everything else appears to be their but not that.
Any idea how I can get this back, I really don't wont to uninstall and reinstall at the moment, and I'm not even sure if that would work.
Thanks
We are unable to retrieve a file from an FTP server using SSIS FTP Task. With the settings indicated below:
IsRemotePathvariable: False
RemotePath: /home/ftpu/test.csv
IsLocalPathVariable: False
LocalPath: working_directory (with Connection Manager entry setup for this)
Operation: Receive files
The following error is produced:
Error: 0xC0029183 at FTP data, FTP Task: File represented by "/home/ftpu/ns_test.csv" does not exist.
Performing a manual ftp works fine, hence suggesting a fault with the SSIS TFP Task. Has anyone else successfully used an FTP task, and if so, any ideas on what's going wrong?
Thanks
Neil
Has anyone used web service task in SSIS????
I am using a simple web service task, which posts a SOAP request To a web service and is supposed to get a response back...
I have been able to configure the HTTP Connection (which points to the URL of a WSDL file), and also been able to download the WSDL file onto the desktop....
Iam also able to define the Input - Service and Method.
I understand that when I assign the Service and method values I should see a grid asking for the input parameters, which I never get...
Is there a reason why this is happening, as , as per a tutorial that I have read, there is supposed to be a prompt of the parameters.
This is , of course the error I get when i run the package:
SSIS package "Webservice.dtsx" starting.
Error: 0xC002F304 at Web Service Task, Web Service Task: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: Could not execute the Web method. The error is: Exception has been thrown by the target of an invocation..
at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection, VariableDispenser taskVariableDispenser)
at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()".
Task failed: Web Service Task
Any help highly appreciated!
Thanks
I have just been using the Import Data wizard in SSMS and when going through the wizard I checked the "Optimize for many tables" checkbox.
The resultant package contains a task called "Transfer Task". I've never heard of this before. Its not listed in the toolbox and its not documented in BOL.
It does some rather strange things as well.
From what I can determine it uses an XML manifest file (stored in c:documents and settings<user>Local settings emp) to construct a SSIS package on the fly which is then executed by an Execute Package Task.
Its SourceDB property is always "smo_Pubs". Its DestinationDB property is always "smo_Pubs_xfred"
There's no UI for it
All very peculiar.
What is it?
Where has it come from?
Did it arrive with SP1 or have I just never noticed it before?
Why is it preferable to a package with lots of data-flows?
Why is it not documented?
When should I use it/not use it?
Questions questions questions...
Thanks
-Jamie
I want to use script task in order to dynamically filled Table A, B, C from the data Table 1. What script should i used and how can i implement this in SSIS.
Ex:
Table 1
Col1 Col2 Col3
A F1 123
A F2 213
A F5 456
B G1 345
C H2 897
C H5 768
Now i wanna write a script which can dynamically do this:
1) read the each row of Table1
2) Get from Col1 = the name of the table on which it needs to update
3) Get from Col2 the name of the colunm of the table from Col1 on which update has to be made
4) Update that particular colunm with the value in Col3
Results shud be
Table A
F1 F2 F3 F4 F5
123 - - - -
- 213 - - -
- - - - 456
Table B
G1 G2 G3
345 - -
Table C
H1 H2 H3 H4 H5
- - - - 768
Is it possible to CREATE a database using SSIS and name it using a variable / parameter mapping.
if not, how can I use SSIS to take a username from a database table and create a database with that name?
thanks is advance....b
I have several packages, all start with a script task component. In this component I use a dll to load some information to the package
All works fine, we made over 80 packages without any problems...
Now we built a master package that call all the other packages (some in a parallel way)
and we're getting a very rare problem, sometime, and in diferente part of the package we get the following error message:
The script threw an exception: Object reference not set to an instance of an object.
When i run the packages alone (all of them) there is no problem
Any Clue?
Cheers
A stored function is created in MySQL Server running on Linux with following info:
mysql > delimiter $$
mysql > CREATE FUNCTION GetMaxKeyForSampleTable()
-> RETURNS DOUBLE READS SQL DATA
-> BEGIN
-> RETURN (SELECT MAX(MJD) FROM tblSampleTable);
-> END;
Query OK, 0 rows affected (0.02 sec)
mysql > delimiter ;
***The above function is successfully stored in the database.
"Execute SQL Task" add-in module has following info:
Name: Execute SQL Task
Description: Execute Task
TimeOut: 0
CodePage: 1252
ResultSet: Single row
Connection Type: ADO.NET
Connection: .MySQLServerLinux
SQLSourceType: Direct Input
SQL Statement: SELECT GetMaxKeyForSampleTable() AS MaxKey
IsQueryStoredProcedure: True
ByPassPrepare: False
Result Set:
Result Name Variable Name
MaxKey User:_dMaxKey
The following steps are taken:
1. Clicked "Parse Query" push button which results a pop-up dialog box -
"The query parsed correctly".
2. Clicked "OK" button.
3. Right clicked "Execute SQL Task" add-in module.
4. Selected "Execute Task".
"Execute SQL Task" changed its color from white to yellow to red
which means excution failed.
Please help me to figure out what should be done to make the excution successful.
Thank you very much in advance.
Paul Cho
Need help regarding ssis dataflow task
I need to create a ssis package. I want to import the data from a flat file to a table.
Lets say, the table has 5 columns -- col1, col2, col3, col4 , col5.(Assume that all columns can be NULLABLE) The datafile contains the data related to only three columns say col1, col2, col3. So when I use dataflow task to import the data from the file to the table, I will only get three columns, col1, col2, col3. Columns col4, col5 will be NULL.
However, I want to populate columns col4, col5 with some values which are stored in the variable.
IS there any way to do this??
Any help would be appreciated.
Thanks