Pam B


I have a number of DTS packages I am trying to convert and am totally new to Integration Services.

I have a flat file that I reference to get a date that I use in some SQL statements in the package. It seems like the best way to do this would be to create a variable to hold the date and set it at run time from the flat file. I've been searching the documentation and the forum and can't figure out how to do this. This is so basic that I feel like an idiot putting it on the forum but I need some help. This whole Integration Services thing is a big shift from DTS (which I eventually became pretty good at) Now I'm back to ground zero.

Any help would be appreciated. If you can direct me to any basic training resources that would be cool too.





Re: Variable Value: How do I set it from value in flat file at runtime

Jamie Thomson


 Pam B wrote:

I have a number of DTS packages I am trying to convert and am totally new to Integration Services.

I have a flat file that I reference to get a date that I use in some SQL statements in the package.  It seems like the best way to do this would be to create a variable to hold the date and set it at run time from the flat file.  I've been searching the documentation and the forum and can't figure out how to do this.  This is so basic that I feel like an idiot putting it on the forum but I need some help.  This whole Integration Services thing is a big shift from DTS (which I eventually became pretty good at)  Now I'm back to ground zero.

Hi Pam,

Don't  beat yourself up over it. SSIS is a big step change from DTS, no doubt about that, and there is a steep learning curve.

It is possible to do what you want but I wouldn't call it straightforward so you definately shouldn't be embarrassed about asking.

SSIS is essentially about getting data from one place and putting it somewhere else. In your case you want to get data from a flat file but you don't want to put it answhere - you just want to get a value from it so you can use it elsewhere. Hence, this isn't a common request.

Does that flat file have only only row and one column in it That being the value that you are after I'll assume for now that the answers to these are questions are 'Yes'

Hopefully you know how to load data from the file into a SSIS data-flow. That's the easy part so I won't cover it but let me know if you want me to. The hard part is getting the  value into a variable. I have an example that explains an approach that you could take - probably the easiest approach:

Getting a value out of a file to use it in our package
(http://blogs.conchango.com/jamiethomson/archive/2005/06/15/1693.aspx)

It contains a downloadable sample to get you started.

 

 

 Pam B wrote:

Any help would be appreciated.  If you can direct me to any basic training resources that would be cool too.

There's lots of resources. A good textbook is probably the best way. I recommend these two:

Microsoft SQL Server 2005 Integration Services by Kirk Haselden
http://www.amazon.com/Microsoft-Server-2005-Integration-Services/dp/0672327813/sr=8-1/qid=1168471611/ref=pd_bbs_sr_1/102-7891523-4086513 ie=UTF8&s=books

Professional SQL Server 2005 Integration Services by Various
http://www.amazon.com/Professional-Server-Integration-Services-Programmer/dp/0764584359/ref=pd_bxgy_b_img_b/102-7891523-4086513

 

I've got some stuff you might wish to read:

But it used to work in DTS
http://blogs.conchango.com/jamiethomson/archive/2006/06/28/SSIS_3A00_-But-it-used-to-work-in-DTS-_2800_1_2900_-_2D00_-Modifing-a-Package-in-script.aspx

The mind-shift from DTS
http://blogs.conchango.com/jamiethomson/archive/2005/05/09/1396.aspx

The New ETL Paradigm
http://www.sqlservercentral.com/columnists/jthomson/thenewetlparadigm.asp

-Jamie







Re: Variable Value: How do I set it from value in flat file at runtime

Andy Leonard

Hi Pam,

I second what Jamie said: all of us here handing out advice had to learn SSIS and we know how big the transition is!

I decided to blog about how to do this - click this link to view it. Jamie's right, it isn't straight-forward.

Hope this helps,

Andy






Re: Variable Value: How do I set it from value in flat file at runtime

Pam B

Jamie,

The information you provided is great. The problem is that unfortunately because of the formatting of the text file I can't get the Flat File Source to load without errors. What I want is the first 8 characters in the text file (on the line that you would really exclude if you wanted to get the tab delimited table, which begins on the second line ).

With your assistance (and the assistance I got from a more experienced co-worker) I finally got it to work as follows:

1) I created a variable named gvLoadDate to hold a date.

2) I dragged a Script Task directly on the Control Flow Tab and

set the readwrite variables for the Script Task to "gvLoadDate"

wrote the following in the Script Task:

Dim myFilePath As String
Dim myStreamReader As System.IO.StreamReader
Dim myLine As String

Public Sub Main()
'
' Add your code here
myFilePath = "\\ServerName\SharedDirName\FileName.txt"
myStreamReader = File.OpenText(myFilePath)
myLine = myStreamReader.ReadLine()

Dts.Variables.Item("gvLoadDate").Value = CDate(myLine.Substring(0, 8))

MsgBox("Load Date is " & Dts.Variables.Item("gvLoadDate").Value.ToString())

Dts.TaskResult = Dts.Results.Success
End Sub

It worked!!! Now on to the next SSIS hurdle! Thanks a lot!!!

Pam






Re: Variable Value: How do I set it from value in flat file at runtime

Pam B

Andy,

I just wanted to thank you for your help. I went to your blog and printed the info you provided. It is great and it will help me on some of my next steps in this project so I will be using it! For the current one I had to just grab the first 8 characters so I did it with a lot of stumbling around in a Script Task directly on the Control Flow tab. You can view my Post to see how I got it to work. Sorry I wasn't more specific in my explanation of my problem. You guys were great.

Thanks,

Pam






Re: Variable Value: How do I set it from value in flat file at runtime

Andy Leonard

Hi Pam,

Thanks so much for your response!

Actually, I just read your solution above. The code has been copied and stored - I'm sure I'll be needing that soon! I like it because it allows me to open (probably) any file and grab some number of characters from it. This should be useful indeed!

Thanks!

Andy