Help with converting code: VB code in SQL Server 2000->Visual Studio BI 2005

Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here:

Function Main()

on error resume next

dim cn, i, rs, sSQL

Set cn = CreateObject("ADODB.Connection")

cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"

set rs = CreateObject("ADODB.Recordset")

set rs = DTSGlobalVariables("SQLstring").value

for i = 1 to rs.RecordCount

sSQL = rs.Fields(0).value

cn.Execute sSQL, , 128'adExecuteNoRecords option for faster execution

rs.MoveNext

Next

Main = DTSTaskExecResult_Success

End Function

This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)

Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:

public Sub Main()

...

Dts.TaskResult = Dts.Results.Success

End Class

I get the following error when I attempt to compile this:

Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.

I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script:
- The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.

- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).

Given this statement:

dim cn, i, rs, sSQL

I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:

Set cn = CreateObject("ADODB.Connection")

cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"

set rs = CreateObject("ADODB.Recordset")

This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!

Option Strict controls what we call "late binding." If option strict is ON, then what happens is that the compiler will generate code that takes full advantage of the type system in the CLR. This means that each variable has a compile-time type (hence you need to specify the "As" clause), and because each variable has a compile-time type, the IL that the compiler generates is fast (for example, method calls are translated to a "call" or a "callvirt" IL instruction).

If you turn option strict OFF, this is what is known as the "late binding" mode. There is no equivalent in C#. What the late binding mode allows you to do is to leave variables specified as "object" (and if you omit the "As" clause, the type is assumed to be "object"). The compiler, because it does not know what the type is, needs to generate code that calls the Visual Basic Runtime helpers to execute method calls.

Because of this, the compiler cannot provide checks for you during compile time. For example, you can do something like this:

dim o = CreateObject("ADODB.Connection")
o.foo()

The compiler will not check whether the "foo" method exists, because it doesn't know the type of o. And if "foo" indeed does not exist, you will get a runtime exception. Runtime exceptions are much harder to debug and diagnose; this is probably why people advise against turning option strict off.

However, in certain scenarios, such as yours, I believe that turning option strict off will make life easier. The compiler may not be as helpful, but it will make your code easier to understand.

The CreateObject calls are indeed creating COM objects. Your analysis of what the code is doing is correct. The .NET equivalent of the ADODB.Connection and ADODB.Recordset classes live in the "System.Data" namespace. Take a look here for some information: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemData.asp

I hope this helps - feel free to post any more questions or issues that you run into here :)|||

Hi Timothy--Thanks for the analysis, and the suggestion of turning this option off. How do I do that for just this package? I read somewhere (don't remember the URL at the moment) that I had to turn this option off through a configuration parameter in a file ending in .vbproj. However, the ultimate goal is store this package in the SQL Server database and so give it the ability to schedule it through the SQL Server Agent. That doesn't seem feasible if I have to store connection information externally, which is a security risk...and I can't find any files with that name anyway.

I tried to add the following into the function:

Option Strict Off

That caused another compilation error, unless I'm missing something else. Any ideas on how to turn the Option Strict off for just this task?

|||

Better to leave Option Strict On.

Dim i as Integer

Dim cn as ADODB.Connection

Dim rs as ADODB.Recordset

These declarations will require a reference in the Script task project to the ADODB primary interop assembly.

-Doug

|||What was the error you get with Option Strict Off?

The finest level of granularity is to turn option strict off at the file level - put this at the top of the file:
Option Strict Off

If you pursue any solution where you need to turn on/off option strict, it's best to at the very least put the code you want option strict off for in another file.

I agree too that Option Strict should typically be left On, but there are situations where turning it Off can make it easier to code, at the expenses that I mentioned above.|||

Thanks all--These prior posts helped me work around the problem. It's appreciated--problem solved!

- Jonathan

Help with converting code: VB code in SQL Server 2000->Visual Studio BI 2005

Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here:

Function Main()

on error resume next

dim cn, i, rs, sSQL

Set cn = CreateObject("ADODB.Connection")

cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"

set rs = CreateObject("ADODB.Recordset")

set rs = DTSGlobalVariables("SQLstring").value

for i = 1 to rs.RecordCount

sSQL = rs.Fields(0).value

cn.Execute sSQL, , 128'adExecuteNoRecords option for faster execution

rs.MoveNext

Next

Main = DTSTaskExecResult_Success

End Function

This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)

Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:

public Sub Main()

...

Dts.TaskResult = Dts.Results.Success

End Class

I get the following error when I attempt to compile this:

Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.

I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script:
- The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.

- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).

Given this statement:

dim cn, i, rs, sSQL

I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:

Set cn = CreateObject("ADODB.Connection")

cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"

set rs = CreateObject("ADODB.Recordset")

This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!

Option Strict controls what we call "late binding." If option strict is ON, then what happens is that the compiler will generate code that takes full advantage of the type system in the CLR. This means that each variable has a compile-time type (hence you need to specify the "As" clause), and because each variable has a compile-time type, the IL that the compiler generates is fast (for example, method calls are translated to a "call" or a "callvirt" IL instruction).

If you turn option strict OFF, this is what is known as the "late binding" mode. There is no equivalent in C#. What the late binding mode allows you to do is to leave variables specified as "object" (and if you omit the "As" clause, the type is assumed to be "object"). The compiler, because it does not know what the type is, needs to generate code that calls the Visual Basic Runtime helpers to execute method calls.

Because of this, the compiler cannot provide checks for you during compile time. For example, you can do something like this:

dim o = CreateObject("ADODB.Connection")
o.foo()

The compiler will not check whether the "foo" method exists, because it doesn't know the type of o. And if "foo" indeed does not exist, you will get a runtime exception. Runtime exceptions are much harder to debug and diagnose; this is probably why people advise against turning option strict off.

However, in certain scenarios, such as yours, I believe that turning option strict off will make life easier. The compiler may not be as helpful, but it will make your code easier to understand.

The CreateObject calls are indeed creating COM objects. Your analysis of what the code is doing is correct. The .NET equivalent of the ADODB.Connection and ADODB.Recordset classes live in the "System.Data" namespace. Take a look here for some information: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemData.asp

I hope this helps - feel free to post any more questions or issues that you run into here :)|||

Hi Timothy--Thanks for the analysis, and the suggestion of turning this option off. How do I do that for just this package? I read somewhere (don't remember the URL at the moment) that I had to turn this option off through a configuration parameter in a file ending in .vbproj. However, the ultimate goal is store this package in the SQL Server database and so give it the ability to schedule it through the SQL Server Agent. That doesn't seem feasible if I have to store connection information externally, which is a security risk...and I can't find any files with that name anyway.

I tried to add the following into the function:

Option Strict Off

That caused another compilation error, unless I'm missing something else. Any ideas on how to turn the Option Strict off for just this task?

|||

Better to leave Option Strict On.

Dim i as Integer

Dim cn as ADODB.Connection

Dim rs as ADODB.Recordset

These declarations will require a reference in the Script task project to the ADODB primary interop assembly.

-Doug

|||What was the error you get with Option Strict Off?

The finest level of granularity is to turn option strict off at the file level - put this at the top of the file:
Option Strict Off

If you pursue any solution where you need to turn on/off option strict, it's best to at the very least put the code you want option strict off for in another file.

I agree too that Option Strict should typically be left On, but there are situations where turning it Off can make it easier to code, at the expenses that I mentioned above.|||

Thanks all--These prior posts helped me work around the problem. It's appreciated--problem solved!

- Jonathan

Help with code

I have this code in a DTS package which is:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

drop proc VerifyRequest
go
/*
* VerifyRequestTransfer - run a command that looks for @.filename in the output
*

*
* This proc looks for a file matching 'tbl_%' in the output of an ftp command.
* The output message reports success/failure of transfer.
* A return code of 1 indicates success
* Return code = 0 indicates failure.
*
* How it Works:
* ftp is executed using @.ftpcommandfile as input to the -s parameter.
* The output of ftp is written to a table
* The table is cleared of garbage records
* The count of records matching @.filename is checked
* if the count = 1 then there success!
*/
CREATE proc VerifyRequest
@.filename varchar(200),
@.ftpcommandfile varchar(1000)
as

declare @.rc int
declare @.rows int, @.errcode int, @.rows2 int
set @.rc = 0
set @.rows = -9998

set nocount on
-- build a table containing list of files in Request directory
if exists (select * from tempdb.dbo.sysobjects where name='RequestFiles' and type = 'U')
drop table tempdb.dbo.RequestFiles
create table tempdb.dbo.RequestFiles (
line_no int identity(1,1) Primary key clustered,
Filename varchar(200) NULL
)
declare @.cmd varchar(2000)

--Get list of remote files
set @.cmd = 'ftp -i -s:' + @.ftpcommandfile
Insert into tempdb.dbo.RequestFiles (Filename)
Exec master.dbo.xp_cmdshell @.cmd
select @.rows = @.@.rowcount, @.errcode = @.@.error
if @.rows = 0 OR @.errcode != 0
begin
set @.rc = -1
goto done
end

-- remove non-files and already processed files ( there might have been old files on remotesystem )
Delete
From tempdb.dbo.RequestFiles
Where coalesce(Filename, '') not like '%tbl_%'
-- check count
select @.rows = (select count(*) from tempdb.dbo.RequestFiles
Where tempdb.dbo.RequestFiles.Filename like '%'+@.filename+'%' )

if @.rows = 1
set @.rc = 1
done:
return @.rc
go

Now the message I am getting is:

The task reported failure on execution. Procedure 'VerifyRequest' expects Parameter '@.filename', which was not supplied.

I don't know where to set this parameter.

I hope someone can help.

Thanks

LystraYou will need to supply both the @.filename and @.ftpcommandfile parameters when you call the procedure from your code:

VerifyRequest 'C:\Yourfile.nam', 'C:\YourCommandFile.nam'|||If that was THAT easy the error would have referenced @.ftpcommandfile parameter, not @.filename.|||THe vb scripts that is first started which is:

Function Main()
DTSGlobalVariables("PostDate") = month(now()) & "/" & day(now()) & "/" & year(now())
dim tmp
dim filename

tmp = right("0" & datepart("m", DTSGlobalVariables("PostDate")), 2)
tmp = tmp & right("0" & datepart("d", DTSGlobalVariables("PostDate")), 2)
tmp = tmp & datepart("yyyy", DTSGlobalVariables("PostDate"))
DTSGlobalVariables("Datestamp") = tmp
filename = DTSGlobalVariables("TransferFileNameRoot") & DTSGlobalVariables("Datestamp") & DTSGlobalVariables("FileExtension")
DTSGlobalVariables("TransferFilename") = DTSGlobalVariables("TransferFileDir") & "\" & DTSGlobalVariables("TransferFileNameRoot") & DTSGlobalVariables("Datestamp") & DTSGlobalVariables("FileExtension")

' use the output file name to generate an FTP command file
set oFSO = CreateObject("Scripting.FileSystemObject")
set oFile = oFSO.OpenTextFile( DTSGlobalVariables ("TransferFTPCommands").Value , 2, 1)
oFile.writeline ("open " & DTSGlobalVariables("TransferFTPServer"))
oFile.writeline DTSGlobalVariables("TransferFTPLogin")
oFile.writeline DTSGlobalVariables("TransferFTPPassword")
oFile.writeline "cd /fs11/infiles"
oFile.writeline "mput " & DTSGlobalVariables("TransferFilename").Value
oFile.writeline "quit"
oFile.Close
set oFile = nothing

' Generate an FTP command file to verify that transfer worked.
set oFile = oFSO.OpenTextFile( DTSGlobalVariables ("TransferFTPVerifyReq").Value , 2, 1)
oFile.writeline ("open " & DTSGlobalVariables("TransferFTPServer"))
oFile.writeline DTSGlobalVariables("TransferFTPLogin")
oFile.writeline DTSGlobalVariables("TransferFTPPassword")
oFile.writeline "cd /fs11/infiles" & vbCRLF & "ls -l " & vbCRLF & "quit"
oFile.Close
set oFile = nothing
set oFSO = nothing

' save the output filename into the transfer verification query
tmp = "Select count(*) from tempdb.dbo.RequestFiles " & vbCRLF & _
"Where tempdb.dbo.RequestFiles.Filename like '%"+ filename + "%'"
'Create a new query to look for files with the output filename
' find the task that counts the number of transferred files
set oTasks = DTSGlobalVariables.Parent.Tasks
for each task in oTasks
if task.Properties("Description") = "Evaluate File Count" then
' set the Query in DynamicProperties Task so that it checks for today's file
For Each oAssignment In task.CustomTask.Assignments
if instr( oAssignment.DestinationPropertyID , "'TransferredFileCount'" ) then
oAssignment.SourceQuerySQL = tmp
end if
next
end if
next
Main = DTSTaskExecResult_Success
End Function

Since I have created a ftp transfer file that lists the file names and should put the files in a temp table. I am having trouble with the ftp command to list the file in my file.

Thanking you in advance.

Lystra

Help with agent job to import from ODBC?

SQL Server 2005 Standard Edition.
Using wizard, no problem to use SQL to import from ODBC data source. Saved
the DTS job to an SSIS package, unable to use with agent scheduler.
Error is DTS_E_PRODUCTLEVELTOLOW.
Please help why is this happening?Frank
Do you want to move jobs to SQL Server 2005?
"Frank Ricciardi" <FrankRicciardi@.discussions.microsoft.com> wrote in
message news:0EE47C4D-589D-4B95-81EB-7F1DE5988A2E@.microsoft.com...
> SQL Server 2005 Standard Edition.
> Using wizard, no problem to use SQL to import from ODBC data source. Saved
> the DTS job to an SSIS package, unable to use with agent scheduler.
> Error is DTS_E_PRODUCTLEVELTOLOW.
> Please help why is this happening?|||I just want them to run as scheduled without operator intervention. I don't
really care how it do it.
Does that make sense?
thanks in advance..
"Uri Dimant" wrote:
> Frank
> Do you want to move jobs to SQL Server 2005?
>
> "Frank Ricciardi" <FrankRicciardi@.discussions.microsoft.com> wrote in
> message news:0EE47C4D-589D-4B95-81EB-7F1DE5988A2E@.microsoft.com...
> > SQL Server 2005 Standard Edition.
> >
> > Using wizard, no problem to use SQL to import from ODBC data source. Saved
> > the DTS job to an SSIS package, unable to use with agent scheduler.
> >
> > Error is DTS_E_PRODUCTLEVELTOLOW.
> >
> > Please help why is this happening?
>
>

Help with agent job to import from ODBC?

SQL Server 2005 Standard Edition.
Using wizard, no problem to use SQL to import from ODBC data source. Saved
the DTS job to an SSIS package, unable to use with agent scheduler.
Error is DTS_E_PRODUCTLEVELTOLOW.
Please help why is this happening?
Frank
Do you want to move jobs to SQL Server 2005?
"Frank Ricciardi" <FrankRicciardi@.discussions.microsoft.com> wrote in
message news:0EE47C4D-589D-4B95-81EB-7F1DE5988A2E@.microsoft.com...
> SQL Server 2005 Standard Edition.
> Using wizard, no problem to use SQL to import from ODBC data source. Saved
> the DTS job to an SSIS package, unable to use with agent scheduler.
> Error is DTS_E_PRODUCTLEVELTOLOW.
> Please help why is this happening?
|||I just want them to run as scheduled without operator intervention. I don't
really care how it do it.
Does that make sense?
thanks in advance..
"Uri Dimant" wrote:

> Frank
> Do you want to move jobs to SQL Server 2005?
>
> "Frank Ricciardi" <FrankRicciardi@.discussions.microsoft.com> wrote in
> message news:0EE47C4D-589D-4B95-81EB-7F1DE5988A2E@.microsoft.com...
>
>

Help with a stored procedure or DTS package

Can anyone offer me a solution to this , i have a table that hold
QI AN Quantity Price Order Refer
Area
28 96229392 15 83.98 1 A1
Level 1
28 960004877 55 192.68 2 B
Level 1
28 96011194 56 102.66 3 B1
Level 1
28 96011194 112 10.66 3 C
Level 2
and i want to transform it to
QI AN Quantity Price Refer
Area
28 grupanfa 0
Level 1
28 96229392 15 83.98 A1
28 960004877 55 192.68 B
28 96011194 56 102.66 B1
28 grupenda 0
28 grupanfa 0
Level 2
28 96011194 112 10.66 C
28 grupenda 0
can anyone please advise
Regards
JohnAre both these tables in sql server? what are the data types?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"John" <topguy75@.hotmail.com> wrote in message
news:446afe1a$0$692$fa0fcedb@.news.zen.co.uk...
> Can anyone offer me a solution to this , i have a table that hold
> QI AN Quantity Price Order Refer
> Area
> 28 96229392 15 83.98 1 A1
> Level 1
> 28 960004877 55 192.68 2 B
> Level 1
> 28 96011194 56 102.66 3 B1
> Level 1
> 28 96011194 112 10.66 3 C
> Level 2
> and i want to transform it to
> QI AN Quantity Price Refer
> Area
> 28 grupanfa 0
> Level 1
> 28 96229392 15 83.98 A1
> 28 960004877 55 192.68 B
> 28 96011194 56 102.66 B1
> 28 grupenda 0
> 28 grupanfa 0
> Level 2
> 28 96011194 112 10.66 C
> 28 grupenda 0
> can anyone please advise
> Regards
> John
>|||The first talbe is in SQL, the second need creating as a temporary table,
can assume all columns are varchar(50)
Regards
john
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:8tOdnTrKJqZY1fbZRVnyuw@.bt.com...
> Are both these tables in sql server? what are the data types?
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "John" <topguy75@.hotmail.com> wrote in message
> news:446afe1a$0$692$fa0fcedb@.news.zen.co.uk...
>|||Where are groupenda and groupanfa coming from? Are they indicating the
start and end of groups, based on level? Why do these have a value of 0,
and is the 0 supposed to represent anything?
Are you trying to create a flat file for an export to another system?
This looks like something that you should be doing in an application or
report, rather than in SQL.
"John" <topguy75@.hotmail.com> wrote in message
news:446afe1a$0$692$fa0fcedb@.news.zen.co.uk...
> Can anyone offer me a solution to this , i have a table that hold
> QI AN Quantity Price Order Refer
> Area
> 28 96229392 15 83.98 1 A1
> Level 1
> 28 960004877 55 192.68 2 B
> Level 1
> 28 96011194 56 102.66 3 B1
> Level 1
> 28 96011194 112 10.66 3 C
> Level 2
> and i want to transform it to
> QI AN Quantity Price Refer
> Area
> 28 grupanfa 0
> Level 1
> 28 96229392 15 83.98 A1
> 28 960004877 55 192.68 B
> 28 96011194 56 102.66 B1
> 28 grupenda 0
> 28 grupanfa 0
> Level 2
> 28 96011194 112 10.66 C
> 28 grupenda 0
> can anyone please advise
> Regards
> John
>sql

Help with "start job" error

good morning everyone,
I have created some scheduled jobs which basically are transfer data from Oracle DB to SQL server through DTS package. I created DTS pacakages and right click to schedule the job for daily run.
I was able to execute DTS package to transfer data w/o problems at all. However it seemed scheduled job could not run automatically nor I kick off the job manually.
I have attached the error message below. It seemed I need some components be installed. Would someone take a look and let me know what is it, name of the components, probably cost information? Thank you very much for the help in advance.When you manually execute a DTS job, it runs under your login and with the resources of your local machine. When the scheduled job is run by the SQLServer Agent, it runs under the permissions of the login property that was specified in the Administrative Services tab with the physical resources (disk drives, folder mappings, etc.) of the SQL Server that the agent resides on.

The attached error looks like the Oracle components are missing from the SQL Server. Once you have those installed, you might want to also verify that the login used by SQL Server Agent has permisisons on the Oracle server.|||check to make sure your Sqlagent startup acct has proper permission. If the job is invoked by non-admin, agent proxy acct will be used. Take a look at the following to set the proxy acct.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp

Help w/ data errors

HI,

Periodically, I receive these errors in my package:

The "component "Set date to NULL or format" (111)" failed because error code 0xC0049063 occurred, and the error row disposition on "output column "SettlementDate - derived" (544)" specifies failure on error. An error occurred on the specified object of the specified component.

It has to do with the fact that I have a derived column that uses an expression to format a date field. The expression is as follows:

ISNULL(TRIM(SettlementDate)) || TRIM(SettlementDate) == "" || LEN(TRIM(SettlementDate)) < 8 || TRIM(SettlementDate) == "89999999" ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(SUBSTRING(SettlementDate,5,2) + "/" + SUBSTRING(SettlementDate,7,2) + "/" + SUBSTRING(SettlementDate,1,4))

It looks complicated, but it isn't. Basically, I'm just checking to see if the date value is NULL, a blank, less than 8 chars, or if it equals 899999999. If it is, set the date to null.

Anyways, the problem is, that the date field value changes sometimes. That is, I thought I covered the problem by checking for 89999999, but apparently other values are showing up as well, and when this happens it breaks the package.

How can I account for varying values, without having to know in advance what they are? Apparently, this problem is going to continue.

Thanks much

How about

"... || TRIM(SettlementDate) > "21000000" ? NULL(DT_DBTIMESTAMP) : ......."|||

Hmm, don't know. I went into my package and ran the step manually, and viola it worked.

I honestly don't know why it broke at 3:30 am this morning, but ran okay five hours later... I loaded the exact same data file.

This makes no sense to me.

|||Are you sure the package that runs via a schedule is the same as the one you opened up manually to execute the step?|||

yeah, that's probably it. the one that ran at 3:30 ran from a job, using the msdb package store.

the one i ran manually was from BIDS

i probably forgot to update it in msdb

i notice the "create date" column in sysdtspackages90 does not reflect the date the package is loaded into msdb, but the original creation date of the package, so there's no way to keep track of when the package was loaded into msdb

unless this data is somewhere else?

Help Using VBScript in an ActiveX task in DTS Package

Hi,

Thanks for reading.

I am creating a DTS package to import a .txt file into sql. I have everything in place, but the text file needs to have the last record deleted before the import. I need help with this part

I would like to delete the last record from a fixed width text file before I import it into sql. The number of rows will vary from file to file.

Can any one offer suggestions on the best way to do this.

I understand that I have to use the FSO to open and read the file, but I am not sure the best way to proceed after that.

Thanks in advance,
SteveThere are a couple easy ways to do this that I can think of:

1) Open the file up before import and delete the last record, then import to SQL Server.
2) Import to a temporary table that has a IDENTITY field in it, then delete the row with the highest value, then import to normal table.
3) If you want to delete the last line because it's an abnormal line (not a suitable record to go into the db), then just allow a certain number of errors. This way it'll basically error out without inserting the line.

The 1st solution needs the VBScript you're looking for. The problem I think with that is that the TextStream Object that you're looking for is a forward only object. This means that you'd have to open it, read each line at a time keeping track of which line you were on with some sort of local variable, then identify when you've reached the end of the file. Then you'd have to close the file, open it again, then read the file till you got to the last line (which you'd now know was the last line because of your local variable(s) that you initialized last time. Then you could delete that line. Here's the link for documentationhttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/jsobjtextstream.asp

The 2nd solution doesn't involve any VBScript, and would probably be simpler to explain and troubleshoot.

The 3rd solution was just a possible guess at what you're trying to do.

David

Help --SSIS Dataflow Task

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
Vishal wrote:
> 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
Suppose I have one variable which contains some value. @.[User::exp1]
Now create one more variable which contains source query.
set evaluateasexpression as true for this variable.
set expression like this
"select col1,col2,col3,'"+@.[User::exp1] + "' as col4 from a1"
In Data flow task in source select sql command from variable and select
this variable.
Regards
Amish Shah
http://shahamishm.tripod.com

Help --SSIS Dataflow Task

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.
ThanksVishal wrote:
> 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
Suppose I have one variable which contains some value. @.[User::exp1]
Now create one more variable which contains source query.
set evaluateasexpression as true for this variable.
set expression like this
"select col1,col2,col3,'"+@.[User::exp1] + "' as col4 from a1"
In Data flow task in source select sql command from variable and select
this variable.
Regards
Amish Shah
http://shahamishm.tripod.com

Help --SSIS Dataflow Task

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.
ThanksVishal wrote:
> 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
Suppose I have one variable which contains some value. @.[User::exp1]
Now create one more variable which contains source query.
set evaluateasexpression as true for this variable.
set expression like this
"select col1,col2,col3,'"+@.[User::exp1] + "' as col4 from a1"
In Data flow task in source select sql command from variable and select
this variable.
Regards
Amish Shah
http://shahamishm.tripod.com

Help regarding passing parameters in SQL and Lookup

Hi,

1) I am using exceute SQL tasks in my control flow. 3 variables have been defined at the package level.They are mapped to 3 parameters respectively in the Execute SQL task.

When I try using these parameters in SQL error is thrown.Query is not getting parsed.My connection is OLEDB. Target and source are in SQL Server.

Can anyone suggest a workaround?

2) Before loading my target I need to define a Lookup . My requirement is if say consumer key matches in fact table then update it else insert.

2 kinds of lookup are available in SSIS dataflow tools. Simple Lookup for exact matching and Fuzzy Lookup for matching based on probability.

Neither of it supports my requirement? Can i put a select and insert query directly in Lookup or will need to call it from a file as a stored procedure?

Please suggest a solution for this too.

Thanks in advance.

Regards,

Aman Anand

aman anand wrote:

Hi,

1) I am using exceute SQL tasks in my control flow. 3 variables have been defined at the package level.They are mapped to 3 parameters respectively in the Execute SQL task.

When I try using these parameters in SQL error is thrown.Query is not getting parsed.My connection is OLEDB. Target and source are in SQL Server.

Can anyone suggest a workaround?

It'd be alot easier to work out what's going on if you told us:

The full error message|||

Jamie,

Thanks for your reply. My query is mentioned below:

INSERT INTO AUD_PROCESS_CONTROL
(LOAD_ID, MASTER_SEQ_ID, PROCESS_START_TIME, PROCESS_END_TIME, SOURCE_RECORD_COUNT, LOAD_RECORD_COUNT,
UPDATE_RECORD_COUNT, FAIL_RECORD_COUNT, EXTRACT_FROM_DATE_TIME, EXTRACT_TILL_DATE_TIME, LOAD_CONTROL_STATUS)
VALUES (1, ?, GETDATE(), GETDATE(), ?, ?, 0, 0, GETDATE(), GETDATE(), 'LR')

Audit table is being maintained to keep track of ETL parameters.

MASTER_SEQ_ID

SOURCE_RECORD_COUNT and

LOAD_RECORD_COUNT

are the 3 variables defined as int32 and the scope is at package level.

They are mapped to 3 parameters which are also defined with datatype as int.

error is something like it says unable to parse the parameters in the query!

will look at the links mentioned in your reply.

thanks again.

Regards,

Aman

|||

aman anand wrote:

error is something like it says unable to parse the parameters in the query!

I meant to copy and paste the error message!

But regardless, you should be able to achieve this using an expression. The links I provided earlier will help.

-Jamie

|||

Jamie,

Sorry for not copy pasting the error before here goes the entire stuff.

INSERT INTO AUD_PROCESS_CONTROL
(LOAD_ID, MASTER_SEQ_ID, PROCESS_START_TIME, PROCESS_END_TIME, SOURCE_RECORD_COUNT, LOAD_RECORD_COUNT,
UPDATE_RECORD_COUNT, FAIL_RECORD_COUNT, EXTRACT_FROM_DATE_TIME, EXTRACT_TILL_DATE_TIME, LOAD_CONTROL_STATUS)
VALUES (1, @.P_MASTER_SEQ_ID, GETDATE(), GETDATE(), @.P_SOURCE_RECORD_COUNT, @.P_LOAD_RECORD_COUNT, 0, 0, GETDATE(), GETDATE(), 'LR')

Error:

TITLE: SQL Task

The query failed to parse. Must declare the scalar variable "@.P_MASTER_SEQ_ID".

Variables have been defined as int64 and while mapping them to parameters datatype has been given as Long.

Regards,

Aman

|||

Again, try using an expression.

-Jamie

|||

Sometimes it works even though the parser says opposite

Another solution is to build the complete query in a varaible

Turn Evaluate as an expression to True and build it like

"SELECT Dato, Kurs from [" + @.[User::Kurstype] + "$] WHERE (NOT (Kurs IS NULL))"

|||

Hi,

Thanks for your invaluable replies jamie and cgpl.

I tried another workaround for my problem. Used ADO.NET connection manager for the query which was posing problems and passed the parameters as @.P_......,

Now this issue has been sorted.

Thanks again.

Regards,

Aman

Help reg Watch Window

hi frnds,

i want to view the runtime variable value in the watch window while the package is running,

how i will get that watch window. i searched all the menus but i didnt get it.

im using Microsoft Visual Studio 2005 for SSIS package.

To do this you, first need to be stopped on a breakpoint. The Watch window is only valid during a break point, so whilst it may be visible whilst running, the values are not updated in real-time.

So set a breakpoint. When stopped, got to the Variables window, click to select and then drag the variable to the watch window. The variables will be shown in the watch window with the current value. The variables will stay in the watch window for the duration of your working session, so you don't have to keep dragging them in everytime youi hit a breakpoint.